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
Code_Snippet.png
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.
So, remove the "On Error Goto …" and debug IsMSExcelRunning for one or more API calls not modified to run in 64-bit also.
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.
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.
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.
Like Gustav, I don't spot anything exactly wrong with your code. That said, I have never used the OutputTo for excel, and have either used the Docmd.TransferSpreadsheet for simple export, or used full automation for advanced export (which seems to be what you are doing.).
It would be helpfull if you tell us which line is causing the error!
So try to remove the OutputTo. Once the excel app is created, use it to create a blank workbook, then use something like below:
The rest of the code should work pretty much as is.
It would be helpfull if you tell us which line is causing the error!
So try to remove the OutputTo. Once the excel app is created, use it to create a blank workbook, then use something like below:
Dim rs as dao.recordset
Set rs=currentdb.Openrecordset("YourQuery",dbopendynaset)
xlApp.Worksheets(1).range("A2").Copyfromrecordset rs
The rest of the code should work pretty much as is.
Thank you!
Sorry about not posting the code. I've added it here. Also, the code errors at this line:
xlApp.range("a2").activewi
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
Sorry about not posting the code. I've added it here. Also, the code errors at this line:
xlApp.range("a2").activewi
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.
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!):
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
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:
It would look something like this:
Dim x as integer, y as integer
x = 1
y = 2
debug.print x, y, x+1
Premium Content
You need an Expert Office subscription to comment.Start Free Trial