User Tools

Site Tools


software:microsoft:access

This is an old revision of the document!


PtrSafe Access adjustment for 64 bit

2020-02-26

Compile error 64-bit

Change this

  Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
  Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

to this and it will compile on both 32-bit and 64-bit

#If Win64 Then
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#Else
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#End If

From <https://stackoverflow.com/questions/6255750/how-do-i-convert-this-program-to-work-on-a-64-bit-machine>

2020-02-20

Compile error

Private Declare Function WaitForSingleObject Lib “kernel32” (ByVal _

  hHandle As Long, ByVal dwMilliseconds As Long) As Long
  

Can t find project of library

      dtFMSProcessingDate = Date
      

How to display Access query results without having to create temporary query?

From: http://stackoverflow.com/questions/17328092/how-to-display-access-query-results-without-having-to-create-temporary-query

Const cstrQueryName As String = "TemporaryQuery"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sqlStr As String

'*Logic to create SQL query... variable to hold query is called sqlStr*
' Apparently you have that piece worked out.  I'll use a simple query ...
sqlStr = "SELECT * FROM Dual;"

Set db = CurrentDb
If Not QueryExists(cstrQueryName) Then
    Set qdf = db.CreateQueryDef(cstrQueryName)
Else
    Set qdf = db.QueryDefs(cstrQueryName)
End If
qdf.sql = sqlStr
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery cstrQueryName
If you still want to later discard the saved query, do this ...

If QueryExists(cstrQueryName) Then
    DoCmd.DeleteObject acQuery, cstrQueryName
End If
This is the helper function for the above code ...

Public Function QueryExists(ByVal pName As String) As Boolean
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim blnReturn As Boolean
    Dim strMsg As String

On Error GoTo ErrorHandler

    blnReturn = False ' make it explicit
    Set db = CurrentDb
    Set qdf = db.QueryDefs(pName)
    blnReturn = True

ExitHere:
    Set qdf = Nothing
    Set db = Nothing
    QueryExists = blnReturn
    Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 3265 ' Item not found in this collection.
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure QueryExists"
        MsgBox strMsg
    End Select
    GoTo ExitHere

End Function

SaveAsText() in Access to export all code

From: http://stackoverflow.com/questions/187506/how-do-you-use-version-control-with-access-development

script in VBScript, that uses the undocumented Application.SaveAsText() in Access to export all code, form, macro and report modules.

Updated to use OpenAccessProject() if it sees a .adp extension, else use OpenCurrentDatabase()

----------------------------------------------------------------------------------------------
' Usage:
'  CScript decompose.vbs <input file> <path>

' Converts all modules, classes, forms and macros from an Access Project file (.adp) <input file> to
' text and saves the results in separate files to <path>.  Requires Microsoft Access.
'

Option Explicit

const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3

' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

dim sADPFilename
If (WScript.Arguments.Count = 0) then
    MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
    Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))

Dim sExportpath
If (WScript.Arguments.Count = 1) then
    sExportpath = ""
else
    sExportpath = WScript.Arguments(1)
End If


exportModulesTxt sADPFilename, sExportpath

If (Err <> 0) and (Err.Description <> NULL) Then
    MsgBox Err.Description, vbExclamation, "Error"
    Err.Clear
End If

Function exportModulesTxt(sADPFilename, sExportpath)
    Dim myComponent
    Dim sModuleType
    Dim sTempname
    Dim sOutstring

    dim myType, myName, myPath, sStubADPFilename
    myType = fso.GetExtensionName(sADPFilename)
    myName = fso.GetBaseName(sADPFilename)
    myPath = fso.GetParentFolderName(sADPFilename)

    If (sExportpath = "") then
        sExportpath = myPath & "\Source\"
    End If
    sStubADPFilename = sExportpath & myName & "_stub." & myType

    WScript.Echo "copy stub to " & sStubADPFilename & "..."
    On Error Resume Next
        fso.CreateFolder(sExportpath)
    On Error Goto 0
    fso.CopyFile sADPFilename, sStubADPFilename

    WScript.Echo "starting Access..."
    Dim oApplication
    Set oApplication = CreateObject("Access.Application")
    WScript.Echo "opening " & sStubADPFilename & " ..."
    If (Right(sStubADPFilename,4) = ".adp") Then
        oApplication.OpenAccessProject sStubADPFilename
    Else
        oApplication.OpenCurrentDatabase sStubADPFilename
    End If

    oApplication.Visible = false

    dim dctDelete
    Set dctDelete = CreateObject("Scripting.Dictionary")
    WScript.Echo "exporting..."
    Dim myObj
    For Each myObj In oApplication.CurrentProject.AllForms
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acForm, myObj.fullname, sExportpath & "\" & myObj.fullname & ".form"
        oApplication.DoCmd.Close acForm, myObj.fullname
        dctDelete.Add "FO" & myObj.fullname, acForm
    Next
    For Each myObj In oApplication.CurrentProject.AllModules
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acModule, myObj.fullname, sExportpath & "\" & myObj.fullname & ".bas"
        dctDelete.Add "MO" & myObj.fullname, acModule
    Next
    For Each myObj In oApplication.CurrentProject.AllMacros
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acMacro, myObj.fullname, sExportpath & "\" & myObj.fullname & ".mac"
        dctDelete.Add "MA" & myObj.fullname, acMacro
    Next
    For Each myObj In oApplication.CurrentProject.AllReports
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "\" & myObj.fullname & ".report"
        dctDelete.Add "RE" & myObj.fullname, acReport
    Next

    WScript.Echo "deleting..."
    dim sObjectname
    For Each sObjectname In dctDelete
        WScript.Echo "  " & Mid(sObjectname, 3)
        oApplication.DoCmd.DeleteObject dctDelete(sObjectname), Mid(sObjectname, 3)
    Next

    oApplication.CloseCurrentDatabase
    oApplication.CompactRepair sStubADPFilename, sStubADPFilename & "_"
    oApplication.Quit

    fso.CopyFile sStubADPFilename & "_", sStubADPFilename
    fso.DeleteFile sStubADPFilename & "_"


End Function

Public Function getErr()
    Dim strError
    strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
               "From " & Err.source & ":" & vbCrLf & _
               "    Description: " & Err.Description & vbCrLf & _
               "    Code: " & Err.Number & vbCrLf
    getErr = strError
End Function

Replacing the Calendar Control in Access 2010 Applications

Calendar control

From: http://msdn.microsoft.com/en-us/library/office/gg251104%28v=office.14%29.aspx

  Microsoft Access has included a Calendar control since it first added support for ActiveX 
  controls, in Access 2.0. The calendar control, MSCAL.OCX, is not included with Access 2010.
  Consider the following pros and cons of each option:
  Built-in date picker
      Simple to use (requires only setting the ShowDatePicker property).
      No customization available.
      Works only with TextBox controls bound to a Date field in a table.
  DatePicker Control
      Reasonably easy to use.
      Allows more customization than the built-in date picker.
      Can also be used to select times.
      Can be bound to a data source.
      Depends on the Microsoft Custom Controls DLL, which introduces serious versioning issues. Your
      end-users may not have the same version of the DLL installed as you do, and this requires 
      updating their DLL to the latest version.
      Only appears as a drop-down, and cannot appear as an open calendar.
  Calendar Form
      Appears only as an open calendar (both a pro and a con).
      Completely customizable, but requires some work in the designer and in code.
      Easy to use─merely drag onto a form and write the code.
      Hooking up events requires additional code.
      Binding is not available without writing code.

Alternatives to SendKey

From: http://www.pcreview.co.uk/forums/alternative-sendkeys-keystrokes-f9-wait-yes-t3162974.html

  DoCmd.GoToControl "Balance"
  

From: http://www.access-programmers.co.uk/forums/showthread.php?t=47212

  By the way, in that code snippet, you might be wondering why moving the focus to the txtField field
  involves two steps:
  1- Me.subForm.SetFocus
     and then,
  2- Me.subForm.Form.Controls("txtField").SetFocus

num lock keeps shutting off

How to solve missing MSCAL.OCX reference in Microsoft Access 2010

From: http://community.spiceworks.com/how_to/show/5517-how-to-solve-missing-mscal-ocx-reference-in-microsoft-access-2010

From: https://social.msdn.microsoft.com/Forums/office/en-US/fd1d449e-d39c-4c0b-99ea-ed561d8906ce/mscalocx-microsoft-calendar-control-conversion-to-mscomct2ocx?forum=exceldev

  However note, that the solution specified is a work-around for the above problem. Microsoft has stated 
  that the Calendar control feature is deprecated in Access 2010 and has suggested a number of options 
  for replacing it. See the link referenced at the bottom of this page for more details
  
  Centerport
  

Forms: Date Picker without ActiveX

From: http://access.mvps.org/access/forms/frm0057.htm

Forms: Date Picker without ActiveX; I have a form with a date field or a block of code that needs to input a date and I want to the user to have a calendar view to select that date from. I don't want to use any ActiveX components.

(A) Use the Access Date Picker.

wildcards for SQL

From: http://msdn.microsoft.com/en-us/library/office/aa140104(v=office.10).aspx

The pattern-matching characters we looked at last month were provided through DAO. Rather than using the asterisk (*) and question mark (?) symbols as wildcards, ADO requires that you use the percent sign (%) to match multiple characters and the underscore (_) to match a single character. There's no single-digit wildcard available through ADO that equates to DAO's pound sign wildcard (#); however, character lists and ranges behave the same.

split for openargs

visual-basic-combo-box-tutorial

http://www.vb6.us/tutorials/visual-basic-combo-box-tutorial

http://www.mvps.org/access/api/api0002.htm

For an action query you might do something like:

Dim strSQL As String strSQL = “Delete * From MyTable Where ID =” & Me.txtID CurrentDB.Execute strSQL

You can also use DoCmd.RunSQL: DoCmd SetWarning False DoCmd.RunSQL strSQL DoCmd SetWarning True

Or you might build a recordset:

Dim rst As DAO.Recordset Dim db As DAO.Database Dim strSQL As String

strSQL = “Select * From MyTable” Set db = CurrentDB Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

http://www.databasejournal.com/features/msaccess/article.php/3505836/Executing-SQL-Statements-in-VBA-Code.htm

software/microsoft/access.1633616314.txt.gz · Last modified: 2021/10/07 14:18 by superwizard