Under the comes back to haunt you category

[stanl]

I developed multiple scripts around 10 years ago, all that used ACE Provider 12.0 for ADO connections. Some users have written saying they just upgraded to Office 365 or Office 2016 which register ACE 16.0 [or 14.0 in some instances]. So I wanted to write a function to determine if 32-bit ACE 12.0 was installed, or a 64-bit version. They are not supposed to coexist, and there is a work-around but it is a lot of work and not guaranteed.


Long story short: Haven't used WB registry functions in a while but RegExistKey(handle, subkey-string [,view-flag]) seems to be the ticket.

   
• 32-bit:  look for HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE
• 64-bit:  look for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE

but then in the notes there is this: using the 32 or 64 values cause these functions to error if a function's subkey parameter contains the string 'wow6432node'


I understand a user could have a 64-bit OS but still have 32-bit Office, and/or WB can compile a 64-bit script.


What is the best way to come up with a function to determine ACE provider, then adjust an existing script to run successfully with the correct ACE Provider?

[td]

Use the View flag and call the function twice...

[stanl]

Use the View flag and call the function twice...

So by "twice' you would recommend

   
• call with the 32-bit flag for ACE 12.0
• If not there... call with the 64-bit flag for ACE.14 or ACE 16.0

like...

Code Select Expand


ace = "12.0" ;to be replace in ADO Provider string if present
If Call32() = ""
else
    ace = "16.0"
     Call64()
endif
Return ace






[td]

Not what I mean. If you need to determine if the target driver is present on the system, you need to check both views by calling the function twice with the same key.  If you find only one target, you would likely need a script compiled to the bitness of the driver present. That said MSFT seems to be providing both versions of some of their COM-related drivers in more recent releases. That may be due to the fact that some dotNet classes are nothing more than covers for underlying COM-based functionality. But I think the ACE ODBC stuff cannot be installed as both 32-bit and 64-bit. I have a vague notion that there is or was a sneaky workaround for this but I don't recall what it was. It may be a false memory or something.

32-bit registry redirection for 32-bit apps has been around since VISTA (2007) so it is not something new. It has been well documented but most of the forum discussions concerning it are buried in the deep past by now.

Note that if one registry key can be used with both 32-bit and 64-bit you don't need to worry about it. Just call into the registry with the default view. 

[stanl]

But I think the ACE ODBC stuff cannot be installed as both 32-bit and 64-bit. I have a vague notion that there is or was a sneaky workaround for this but I don't recall what it was. It may be a false memory or something.

I remember posting something about that here. Assume you have the ACE Provider for 64. You can download the AccessDatabaseEngine.exe 2010; call it from the command line with /QUIET parameter. Was supposed to install the ACE 12.0 32bit Provider.

[td]

Drifting a bit off topic but I vaguely remember a "/passive" switch but could be wrong. I tried it with ACE 16 and it doesn't appear to work. There may also be a registry setting you could temporarily rename to get the 64-bit provider to install with the 32-bit install already present.

For some reason, I can install the Office 10 32-bit provider on my system even though I have 64-bit Office 16 Access installed on my system.

You likely already have this figured out but this quick and dirty detects the bitness of the installed provider:

Code (winbatch) Select Expand

Text = "None"

if RegExistKey( @REGMACHINE, "SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE", 32) then Text = "32-bit"      ;
if RegExistKey( @REGMACHINE, "SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE", 64) then Text:=" 64-bt"

Message("ACE Version 12 providers", Text)
exit

The script reports "32-bit" as expected.

[td]

This may be obvious but I should have mentioned that "the both" views check is only necessary or informative on 64-bit systems. You can use WinMetrics to sort that out.

Code (winbatch) Select Expand

if WinMetrics( -3 ) == 5 || WinMetrics(-7) == 2    ; If wow64 or 64-bit winbatch.

[stanl]

Thanks for the code. But this does become a moot point. I compiled a WB script 2009-2010: basically did some web-scraping for luxury used cars. The user distributed the exe across Canada to several dealerships. Most had since moved on to licensing more advanced programs but a few remain and function on that exe - they have, however, moved to Office 365 which is 64 bit. I just changed the 12.0 to 16.0 for Provider string and re-compiled in WB 64 and sent out a zipped package [no charge]. The script was actually just a nice-to-have, he makes his $$$ as a consultant with invites to hold seminars. The few tweaks he asked for in the code were handled with his [probably 1 trillion] frequent flyer miles which he freely set us up with to fly to Atlanta while our daughter was a Ph.D. candidate at Emory.


I haven't used WB related to any work or business since 2017... but I have liked this forum and as annoying as I can appear to be will always be a supporter not a detractor.

[td]

Since the topics of this forum are all public, a question posted by one can be useful to others even if the original poster does not gain a benefit.