Avatar of JoySloan
JoySloan
Flag for United States of America asked on

Microsoft Access 64-bit Compatibility Issue

I have legacy code that exports Access queries to excel and formats the output.  The database that was created on a 32-bit Windows operating system and is giving me major headaches on my new 64-bit system.  I've done research and most experts say that it's best to uninstall the 64-bit system, but that is not an option for me, as IT dictates what's installed on our computers.  So what I need to know how to rewrite the code to work with the new 64-bit system.  Thank you!
Code_Snippet.png
DatabasesMicrosoft AccessWindows OS

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Gustav Brock

Apart from the unknown IsMSExcelRunning, the code will run without issues in both 32- and 64-bit Access.

So, remove the "On Error Goto …" and debug IsMSExcelRunning for one or more API calls not modified to run in 64-bit also.
JoySloan

ASKER
Thank you for your comment!  
I get a Run-Time error '1004': Application-defined or object-defined error with or without the IsMSExcelRunning code.  I've attached the IsMSExcelRunning code FYI.
John Tsioumpris

No Code is attached but normally you shouldn't have issues...unless API or ActiveX are used your code should run fine.
Check if the IsMSExcelRunning uses some API calls..
Probably you should start first by opening your application on the 64bit and compiling the code...if there is some controversial code errors should pop up.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Anders Ebro (Microsoft MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
JoySloan

ASKER
Thank you!

Sorry about not posting the code.  I've added it here.  Also, the code errors at this line:

xlApp.range("a2").activewindow.freezepanes = True

I tried Anders' code and I'm getting the same error.  Anders, could you please send the code to create a blank workbook?  I may have missed a step.

I feel like this must have something to do with ActiveX references, as the code ran perfectly in my 32-bit OS.  I am running Office 365 64-bit.  Could you please tell me what references I should have checked?  I am at a loss.
isMSExcelRunning.png
Gustav Brock

FreezePanes servers a purpose when the user interacts. It should not be needed here.
Try to remove the line completely and study the output.
JoySloan

ASKER
I got the code to work! **UPDATE** I ended up using the DoCmd.OutputTo but instead of opening the file using that line of code, I saved the file and then opened it using xlApp.Workbooks.Open.

1. I had to add Microsoft Excel 16.0 Object Library to my references so that I could open the Excel workbook (thanks Anders!)
2. I had to delete FreezePanes (thanks Gustav!)
3. I changed the first section of the code as follows (and formatted it as code (thanks Dale!):


Public Function Donations_Export()
On Error GoTo LeaveExport

Dim xlApp As Object
Dim xlWb As Excel.Workbook

'Open a new Excel workbook
Set xlApp = CreateObject("Excel.Application")

'Download Query to Excel and save it
DoCmd.OutputTo acOutputQuery, "Donations_Export", acFormatXLS, "Donations_Export", False, , , acExport

'Open saved workbook for formatting
Set xlWb = xlApp.Workbooks.Open("Donations_Export")
xlApp.Visible = True

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

Just a quick point about posting etiquette.  The preferred method of posting code is to copy your code, paste it into your post, and then click the "Code" button in the formatting toolbar.  This will indent it and wrap it in a window of its own.  This also allows those who are attempting to help you to simply copy your code into an Access database to test it.

It would look something like this:
Dim x as integer, y as integer
x = 1
y = 2
debug.print x, y, x+1

Open in new window