It's all about the data.

Sunday, March 28, 2010

A work in Progress and 64 Bit VBA

Recently, I revisited an Access demo I've been puttering around with for a while. In the process, I also had a great chance to take a stroll through the emerging information relating to 64 bit OS's and Access. The event that triggered this was moving this demo into Access 2010 (32 bit flavor) running on a 64 bit OS (Win 7). It didn't work, which called for some changes in API calls to handle the new 64 bit environment. I conducted a quick consultation with my friend Banana, and we came up with the changes needed to convert these APIs to run on the 64 bit WIn 7 OS.

In the process, I worked through two new datatypes and the requirement to use LenB(), instead of Len() to evaluate the size of a structure.

This stuff matters to anyone moving their Access apps from a 32 bit OS to a 64 bit OS. We're not talking about Access itself here. The default installation for Access 2010 will be the 32 bit version and all of my testing here is in the 32 bit version of Access on a 64 bit OS.

I can't explain this any better than MSDN does, so here's their explanation:

"In previous versions of VBA, there was no specific pointer data type so the Long data type was used. And because the Long data type is always 32-bits, this breaks when used on a system with 64-bit memory because the upper 32-bits may be truncated or may overwrite other memory addresses. Either of these situations can result in unpredictable behavior or system crashes.


(32 bit version)
Declare Function RegOpenKeyA Lib "advapi32.dll" (ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long [editor's note: Return value remains long]

To resolve this, VBA now contains a true pointer data type: LongPtr. This new data type enables you to write the original Declare statement correctly as:

(64 bit version with PtrSafe Qualifier and LongPtr 64 bit Pointer)
Declare PtrSafe Function RegOpenKeyA Lib “advapire32.dll” (ByVal hKey as LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As Long [editor's note: Return value remains long]

Long,LongPtr and PtrSafe usage.

Note that the return value for the API call is a Long, so we don't need to change it. However, phkResult is a Pointer, which must be converted.

Also, note the use of the PtrSafe Qualifier in the 64 bit version above. This qualifier is mandatory on 64-bit systems.


Here's the relevant section of the original code I was using, which some of you will probably recognize right away. I've added some color to the relevant items:

(32 bit version)
Option Compare Database
Option Explicit

'This code was originally written by Ken Getz.
' It is not to be altered or distributed, 'except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code originally courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996
' Revised to support multiple files:
' 28 December 2007

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean

(64 bit version with PtrSafe Qualifier, LongPtr 64 bit Pointer and LongLong 64 bit datatype)

Option Compare Database
Option Explicit


'This code was originally written by Ken Getz.
' It is not to be altered or distributed, 'except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code originally courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996
' Revised to support multiple files:
' 28 December 2007
' ptrSafe added March, 2010
' LongPtr added March, 2010

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As LongPtr
hInstance As LongPtr
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As LongPtr
lpTemplateName As String
End Type

Private Declare PtrSafe Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "aht_apiGetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Private Declare PtrSafe Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "aht_apiGetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean

We found a reference to the Open File Name Structures on MSDN, MSDN Article

From here we identified the pointes in the type declaration that needed to be converted to LongPtrs and made the conversion.

And it still didn't work.

Okay, Banana quickly pointed out that the final piece of the puzzle is the size of structures.

Here's the relevant information:

lStructSize
DWORD
Specifies the length, in bytes, of the structure.

It turns out that, in the 64 bit world, we need to use the LenB() function to return the byte size of the structure, where in the 32 bit, we could use the Len() function.

Here are the relevent definitions, again from MSDN.

Len Function
Returns the number of characters in a string or the number of bytes required to store a variable.

The LenB function is used with byte data contained in a string. Instead of returning the number of characters in a string, LenB returns the number of bytes used to represent that string.

We found the place in the code where that call was made, converted it to LenB() and I was back in business.

With OFN
.lStructSize = LenB(OFN)
.hwndOwner = Hwnd
.strFilter = Filter

...

End With



As we move on into the really exciting world that lies ahead, we are going to need more lessons like this one.

Oh, and the demo that precipitated this trip into 64-bit land? I'm still tweaking it.

5 comments:

Banana said...

Just to give the credits where it's due - it was Rob Cooper who told me about the LenB trick.

http://www.utteraccess.com/forum/office-2010-x64-bit-qu-t1914261.html


I anticipate that we all will have to learn how to read MSDN documentation - we used to be able to ride the coattail of VB developers who needed APIs more than we do and went to various site like http://allapi.mentalis.org/ for VB declaration of API calls. But VB is no more, and definitely not going to be moved into 64-bit so that learns us with only MSDN as current & reliable documentation. Also we have to account for the fact that some APIs no longer work in Vista or newer due to new UAC model it's possible that documentation on newer APIs will very very sparse for VB context.

MSDN documentation on APIs are usually written for C++ programmers so we have to map the data types to work out when a Long parameter/return value should be now a LongPtr. Basically, anything that's a pointer, a handle, an instance (maybe htere's more but I can't think of any other) should be a LongPtr. All other Longs should remain the way they are. But if you look at the documentation and see "LPTSTR" which means "Long Pointer To STRing" it's easy to think we should be using LongPtr but not so - it stays as a plain String because VBA actually handles it as a LPTSTR behind the scene so we never work with pointers in VBA directly so I can imagine that reading those documentation would be quite confusing to people who are comfortable writing VBA code and never had to once think about what a pointer is.

Yuhong Bao said...

"it's easy to think we should be using LongPtr but not so - it stays as a plain String because VBA actually handles it as a LPTSTR behind the scene"
Actually, if you just use a plain String, VBA will always pass it as a pointer to an ANSI string (LPSTR) converted from Unicode. To pass a Unicode string, best to declare as LongPtr and use StrPtr to return the pointer to the raw Unicode string.

B-Koz said...

I found myself an issue with the Data Source (ODBC) tool with Windows 7 64. It is one version of odbcad32.exe in C:\WINDOWS\system32 and one in C:\WINDOWS\sysWOW64.
The last one (in the 64 bit folder!) is working fine with 32 bit connectors. The one in the 32 bit folders is only working with 64 connectors...

I think that it is also a copy of comdlg32.dll on both sysWOW64 and system32 folders. I cannot confirm that. They may work in a different way. Using the right one may be able to fix these kinds of issues.

Anonymous said...

You my friend are a Steely Eyed Missile man! Thanks.

Anonymous said...

Thank you, you saved the day!