Sunday, March 28, 2010

A work in Progress and 64 Bit VBA

I'm constantly amazed by the fact that this blog post remains near the top of the list of most viewed posts on my whole canon of 100+ posts.

Recently, I've found that there is at least one other first class post on the subject these days. Please read through my offering, then go visit Phillip Steifel at Code Kabinett.
============================================================
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.