Link to home
Start Free TrialLog in
Avatar of JoySloan
JoySloanFlag 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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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.
Avatar of 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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
FreezePanes servers a purpose when the user interacts. It should not be needed here.
Try to remove the line completely and study the output.
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

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