My version of Access is 32 bit. When certain users try to access it using a Citrix server they get this error:
Your Microsoft Access database or project contains a missing or broken reference to the file 'MSCOMCTL.OCX' VERSION 2.2. When they click OK this msg comes up:
Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute. Finally this code is highlighted in the VBA editor:
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Can anyone tell me what, I need to do to get this to work in the Citrix environment. I guess this would happen on any version of Access that is 64 bit.:banghead:
The question is, does the Citrix server have its own version of Access installed? Or did you actually develop using the facilities of the Citrix server?
I have added this code but, am still getting a missing lib ref error msg.
#If Win64 Then
Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As LongLong) As LongLong
#Else
Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#End If
Here is the problem: You have a 32-bit app running on the Citrix copy of a 64-bit utility. Citrix will run the shared app that it has in its Programs folder. As you note in your posts, you KNOW that you have a 64-bit office. To make this work, particularly with Citrix, you have an uphill battle.
First, you have to determine whether you are going to be required to use the 64-bit version of Office because of other users not using Access but who ARE using other 64-bit Office programs.
If ANY users are doing so, you face the problem of having to have both "bitnesses" active at the same time. I've never seen that work.
If nobody is using Office except for you and your users, you MIGHT be able to persuade the Citrix manager to remove the 64-bit Office and replace it with the 32-bit Office. They are distributed on the same disk, so as long as Office was installed from a CD/DVD, you should be able to do the remove and install.
THEN you run into the technical issue, more of a concern for your Citrix manager than for you, that the Office version needs to have a multi-user license if you are going to share via Citrix. The EULA for Office off-the-shelf specifically says it is a single-user version.
THEN you run into the technical issue that your users will need some private storage on the Citrix server because otherwise they would be sharing the same FE file (or worse, the same whole database), which is how you get serious corruption issues. Search this forum for articles on Citrix use. Running from Citrix IS possible but is fraught with perils caused by sharing the unshareable.
FINALLY, if you are forced to work within the 64-bit Access, this article might help regarding how to manage that COMCTL library...
BUT... for a 32-bit app running with a 64-bit Access version, there is potential for a LOT of headaches because not all 32-bit libraries are compatible with 64-bit Office. And Microsoft has not so far shown any intentions of fixing that problem. Which means that when you installed 64-bit office, you only got the libraries that HAD been converted. And there is no telling exactly which libraries haven't made it over. Whereas if you had the ability to get to the 32-bit version, you would probably be able to see all of the familiar libraries.
I've oft read that ActiveX controls are problematic in that they become deprecated from newer versions, so I've always stayed away from them. That may be the primary problem. Some users may no longer have that library.
Not being a Citrix expert, I can only recall using it from home to access db's that were on a shared network drive - the same one I would have used if I was at work. The suggestion seems to be that Access files must be on a Citrix server, which isn't what I think I was dealing with.
The old Microsoft Windows Common Controls library, MSCOMCTL.OCX is not included in the list of available references with 32-bit Windows 10 though it can be added to the list.
However, from memory it is NOT installed with 64-bit Windows and if you install it you have to register it to get it to work.
Here’s another link that may be useful: https://www.urtech.ca/2017/11/solved-mscomctl-ocx-download-register-64-bit-windows/
My advice would be to remove that reference, and modify code if necessary to prevent it being an issue
From post #11
I have added this code but, am still getting a missing lib ref error msg.
#If Win64 Then
Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" (ByVal lpBuffer As String, nSize As LongLong) As LongLong
#Else
Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#End If.
The problem is that you are using conditional compilation incorrectly
As part of VBA7 (A2010) PtrSafe and LongLong / LongPtr were added
PtrSafe actually does nothing other than tell the compiler the statement is safe to use in VBA7 LongLong is used for pointers BUT only works in 64-bit whereas LongPtr has the same purpose but works in both 32-bit & 64-bit. You should NOT use a brute force approach and convert all Long to LongLong/LongPtr.
If you do so, the statement will compile but may not work in 64-bit NOTE: I am guilty of doing exactly this in the past and am currently in the process of converting all such APIs properly.
Taking a different API as an example, the older 32-bit version is
Code:
Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Below is one method of dealing with conditional compilation for this API:
Code:
#If Win64 Then 'PtrSafe and datatype LongLong for pointers
Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As LongLong, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongLong
#ElseIf VBA7 Then ‘32-bit Office with VBA7
Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else 'older 32-bit Office
Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String,
ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
However, its unnecessarily complex. If we use LongPtr instead of LongLong, the first two sections can be combined
Code:
#If VBA7 Then ‘32-bit or 64-bit Office with VBA7
Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
#Else 'older 32-bit Office
Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
BUT if all of your users are running 2010 or later, you don’t need the else part so this can be reduced to:
Code:
Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
Returning to your GetUserName API, it should be written as
Code:
#If VBA7 Then
Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
#Else
Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
#End If
Or if all users are running 2010 or later then just use
Code:
Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
==============================================
Having got that out of my system, the GetUserName API is just one of three methods of getting that information
The other two are much simpler:
a) Environ(“UserName”)
b) CreateObject("WScript.Network").UserName
Neither of these require an API so work as written in both bit-nesses and all versions of Access (from A97 onwards!)
However the Environ function can be ‘spoofed’ so the WScript method is preferred
