Problem with Microsoft Access VBA

John Clingeleffer
John Clingeleffer used Ask the Experts™
on
I am trying to have some Access VBA run on both 32 bit and 64 bit Microsoft Access. I followed the Microsoft instructions re PtrSafe and LongPtr.

The code follows:

Option Compare Database

Public Type SECURITY_ATTRIBUTES
        nLength As LongPtr
        lpSecurityDescriptor As LongPtr
        bInheritHandle As LongPtr
End Type

Public Declare PtrSafe Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal Reserved As LongPtr, ByVal lpClass As String, ByVal dwOptions As LongPtr, ByVal samDesired As LongPtr, lpSecurityAttributes As SECURITY_ATTRIBUTES, phkResult As LongPtr, lpdwDisposition As LongPtr) As LongPtr
Public Declare PtrSafe Function RegSaveKey Lib "advapi32.dll" Alias "RegSaveKeyA" (ByVal hKey As LongPtr, ByVal lpFile As String, lpSecurityAttributes As SECURITY_ATTRIBUTES) As LongPtr
Public Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal lpReserved As LongPtr, lpType As LongPtr, lpData As String, lpcbData As LongPtr) As LongPtr
Public Const HKEY_LOCAL_MACHINE = &H80000002

Public Const REG_SZ = 1
Public Const REG_OPTION_NON_VOLATILE = 0
Public Const STANDARD_RIGHTS_ALL = &H1F0000
Public Const KEY_QUERY_VALUE = &H1
Public Const KEY_SET_VALUE = &H2
Public Const KEY_CREATE_SUB_KEY = &H4
Public Const KEY_ENUMERATE_SUB_KEYS = &H8
Public Const KEY_NOTIFY = &H10
Public Const KEY_CREATE_LINK = &H20
Public Const SYNCHRONIZE = &H100000
Public Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_ALL Or KEY_QUERY_VALUE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Or KEY_CREATE_LINK) And (Not SYNCHRONIZE))
Public Const ERROR_SUCCESS = 0&

Public Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal ulOptions As LongPtr, ByVal samDesired As LongPtr, phkResult As LongPtr) As LongPtr
Public Declare PtrSafe Function RegQueryValue Lib "advapi32.dll" Alias "RegQueryValueA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal lpValue As String, lpcbValue As LongPtr) As LongPtr
Public Declare PtrSafe Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal Reserved As LongPtr, ByVal dwType As LongPtr, ByVal lpData As String, ByVal cbData As LongPtr) As LongPtr
Public Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As LongPtr) As LongPtr

'   Reference: MS Q145679
Declare PtrSafe Function RegQueryValueExNULL Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal lpReserved As LongPtr, lpType As LongPtr, ByVal lpData As LongPtr, lpcbData As LongPtr) As LongPtr
Declare PtrSafe Function RegQueryValueExString Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal lpReserved As LongPtr, lpType As LongPtr, ByVal lpData As String, lpcbData As LongPtr) As LongPtr
   

Public Function MIESaveSetting(ByVal pKey, ByVal pString As String, pValue As String)
    '   pKey "SOFTWARE\ODBC\ODBC.INI\TestLink"
    Dim l&, hKey&, ldisp&
    Dim sa As SECURITY_ATTRIBUTES

    sa.nLength = LenB(sa)
    l& = RegCreateKeyEx( _
        HKEY_LOCAL_MACHINE, _
        pKey, _
        0&, _
        vbNullString, _
        REG_OPTION_NON_VOLATILE, _
        KEY_ALL_ACCESS, _
        sa, _
     hKey, _
        ldisp)

    If l& = ERROR_SUCCESS Then
        l& = RegSetValueEx(hKey, pString, 0, REG_SZ, pValue, Len(pValue) + 1)
    End If

End Function

This code compiles and runs on 32bit Office with no problems but fails in 64 bit Office at the hKey variable with the error message "ByRef argument type mismatch".

Any ideas.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger

Commented:
Hi there! :)

Just to confirm before I start working on it... What is this Access VBA used for, any example files? It will be much faster if I have materials to reference the debugging and editing.

Author

Commented:
Full function is to retrieve and set registry values. It works in conjunction with the main software package - Summit Event Manager - Pro.

Full code for the module follows:

Option Compare Database

Public Type SECURITY_ATTRIBUTES
        nLength As LongPtr
        lpSecurityDescriptor As LongPtr
        bInheritHandle As LongPtr
End Type

Public Declare PtrSafe Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal Reserved As LongPtr, ByVal lpClass As String, ByVal dwOptions As LongPtr, ByVal samDesired As LongPtr, lpSecurityAttributes As SECURITY_ATTRIBUTES, phkResult As LongPtr, lpdwDisposition As LongPtr) As LongPtr
Public Declare PtrSafe Function RegSaveKey Lib "advapi32.dll" Alias "RegSaveKeyA" (ByVal hKey As LongPtr, ByVal lpFile As String, lpSecurityAttributes As SECURITY_ATTRIBUTES) As LongPtr
Public Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal lpReserved As LongPtr, lpType As LongPtr, lpData As String, lpcbData As LongPtr) As LongPtr
Public Const HKEY_LOCAL_MACHINE = &H80000002

Public Const REG_SZ = 1
Public Const REG_OPTION_NON_VOLATILE = 0
Public Const STANDARD_RIGHTS_ALL = &H1F0000
Public Const KEY_QUERY_VALUE = &H1
Public Const KEY_SET_VALUE = &H2
Public Const KEY_CREATE_SUB_KEY = &H4
Public Const KEY_ENUMERATE_SUB_KEYS = &H8
Public Const KEY_NOTIFY = &H10
Public Const KEY_CREATE_LINK = &H20
Public Const SYNCHRONIZE = &H100000
Public Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_ALL Or KEY_QUERY_VALUE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY Or KEY_CREATE_LINK) And (Not SYNCHRONIZE))
Public Const ERROR_SUCCESS = 0&

Public Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal ulOptions As LongPtr, ByVal samDesired As LongPtr, phkResult As LongPtr) As LongPtr
Public Declare PtrSafe Function RegQueryValue Lib "advapi32.dll" Alias "RegQueryValueA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal lpValue As String, lpcbValue As LongPtr) As LongPtr
Public Declare PtrSafe Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal Reserved As LongPtr, ByVal dwType As LongPtr, ByVal lpData As String, ByVal cbData As LongPtr) As LongPtr
Public Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As LongPtr) As LongPtr

'   Reference: MS Q145679
Declare PtrSafe Function RegQueryValueExNULL Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal lpReserved As LongPtr, lpType As LongPtr, ByVal lpData As LongPtr, lpcbData As LongPtr) As LongPtr
Declare PtrSafe Function RegQueryValueExString Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As LongPtr, ByVal lpValueName As String, ByVal lpReserved As LongPtr, lpType As LongPtr, ByVal lpData As String, lpcbData As LongPtr) As LongPtr
   


Public Function MIEGetSetting(ByVal pKey, ByVal pString) As String
    ' Dim myKeyValue As String * 2048
    Dim vValue As Variant, sValue As String
    Dim lType As LongPtr, cch As LongPtr
    Dim l As LongPtr, hKey As LongPtr, ldisp As LongPtr
    Dim sa As SECURITY_ATTRIBUTES
    lType = REG_SZ
    'Debug.Assert False
    ' l& = RegQueryValueEx(hKey, vbNullString,0&, lType, 0&, lBuffer)
    ' lBuffer = 256
    Dim hClassSubKey As LongPtr

    l& = RegOpenKeyEx(HKEY_LOCAL_MACHINE, pKey, 0&, KEY_QUERY_VALUE, hClassSubKey)
    If l& <> ERROR_SUCCESS Then
        MIEGetSetting = ""
        Exit Function
    End If

    l& = RegQueryValueExNULL(hClassSubKey, pString, 0&, lType, 0&, cch)
    sValue = String(cch + 1, Chr(0))
    l& = RegQueryValueExString(hClassSubKey, pString, 0&, lType, sValue, cch)

    ' l& = RegQueryValueEx(hClassSubKey, pString, 0&, lType, 0&, lBuffer)
    ' l& = RegQueryValueEx(hClassSubKey, pString, 0&, lType, myKeyValue, ByVal lBuffer)
    ' l& = RegQueryValueEx(hClassSubKey, pString, 0&, lType, 0&, lBuffer)

    If l& = ERROR_SUCCESS Then
        vValue = Left$(sValue, cch - 1)
    Else
        vValue = Empty
    End If
    MIEGetSetting = vValue

'    l& = RegCreateKeyEx( _
        HKEY_LOCAL_MACHINE, _
        "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", _
        0&, _
        vbNullString, _
        REG_OPTION_NON_VOLATILE, _
        KEY_ALL_ACCESS, _
        sa, _
        hKey, _
        ldisp)
'    If l& = ERROR_SUCCESS Then
'        l& = RegSetValueEx(hKey, "TestLink", 0, REG_SZ, "SQL Server", 11)
'    End If

End Function    '   MIEGetSetting





Public Function MIESaveSetting(ByVal pKey, ByVal pString As String, pValue As String)
    '   pKey "SOFTWARE\ODBC\ODBC.INI\TestLink"
    Dim l&, hKey&, ldisp&
    Dim sa As SECURITY_ATTRIBUTES

    sa.nLength = LenB(sa)
    l& = RegCreateKeyEx( _
        HKEY_LOCAL_MACHINE, _
        pKey, _
        0&, _
        vbNullString, _
        REG_OPTION_NON_VOLATILE, _
        KEY_ALL_ACCESS, _
        sa, _
        hKey, _
        ldisp)

    If l& = ERROR_SUCCESS Then
        l& = RegSetValueEx(hKey, pString, 0, REG_SZ, pValue, Len(pValue) + 1)
    End If

End Function
John TsioumprisSoftware & Systems Engineer

Commented:
Try hkey as LongLong
Dim hkey as LongLong

Open in new window

President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
@JohnC

 You are making the same mistake that a lot of other people have made, which is that not everything gets a 'LongPtr' or 'LongLong" automatically just because it is a long.  Sometimes it really needs to be a long.  Other times it does need to be changed.

 You have to look at what's being used by a call and then decide if it needs to change.

 For example, your RegOpenKeyEx is this:

Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal ulOptions As LongPtr, ByVal samDesired As LongPtr, phkResult As LongPtr) As LongPtr

When in fact it should be this:

Declare PtrSafe Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As LongPtr, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As LongPtr) As Long

 So some things do change for 64 bit and some do not.  Even worse, sometimes the actual call changes with different arguments for 64 bit vs 32 bit. That's true for structures as well.  That's all explained here:

Compatibility Between the 32-bit and 64-bit Versions of Office 2010
http://msdn.microsoft.com/en-us/library/ee691831(office.14).aspx

make sure you read the section "Introducing the VBA 7 Code Base"

This page has a link for a download that covers most of the common API calls and the changes required for 64 bit and will help the most with porting a 32 bit app:
https://docs.microsoft.com/en-us/office/troubleshoot/office/win32api_ptrsafe-with-64-bit-support

and here's a list of all the calls that were modified for 64 bit:
http://msdn.microsoft.com/en-us/library/aa383663(VS.85).aspx

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial