Problem with Access Project compiled in 2003 running with 2010 Access library
We have a 2003 compiled access project that we are trying to run using MS Office 2010. Before you tell me to update the project you need to know that this project uses Access Security which is no longer available with 2010. That being said we have seen a couple of issues that are causing us to pull our hair out.
We have a report where we set a date range for filtering purposes. When I run against the office 2003 library it works fine when I run against the office 2010 library the date criteria is ignored this is the call that I'm using in code.
DoCmd.OpenReport DocName, outDevice, , Criteria
Another problem we're seeing is that we can export an xls spreadsheet and launch excel when running with the office 2003 library however when running using the office 2010 library the sheet is created but excel is never launched
This is some of the suspect code
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, tQuery, SA, True
Shell "Excel """ & SA & """", vbNormalFocus
I switch between the 2 libraries by executing my code using this shortcut for changing Office11 to Office14
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.E
One other thing to note the machine that exhibits the problem has both access 2010 and access 2003 installed
We have a report where we set a date range for filtering purposes. When I run against the office 2003 library it works fine when I run against the office 2010 library the date criteria is ignored this is the call that I'm using in code.
DoCmd.OpenReport DocName, outDevice, , Criteria
Another problem we're seeing is that we can export an xls spreadsheet and launch excel when running with the office 2003 library however when running using the office 2010 library the sheet is created but excel is never launched
This is some of the suspect code
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, tQuery, SA, True
Shell "Excel """ & SA & """", vbNormalFocus
I switch between the 2 libraries by executing my code using this shortcut for changing Office11 to Office14
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.E
One other thing to note the machine that exhibits the problem has both access 2010 and access 2003 installed
ASKER
I tried using the where portion... same results drops the filter entirely.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I here what your saying. I'm setting up a test box so I can mimic the 2010 environment they're running and will try it there.
I would add that Microsoft tightened up on a lot of things internally in Access starting with A2007 and things that used to work and you got away with won't any longer (mostly in dealing with properties).
With that said, what you described should not be an issue in A2010. Overall there are very few issues in taking an Access 2003 MDB un-covnerted and running it in Access 2010.
I would take a copy of the DB, do a compact/repair, possibly a /decompile, and then make sure it compiles without error in 2003. After that, try it in 2010 and see if you get different results (and I expect you will).
Jim.
With that said, what you described should not be an issue in A2010. Overall there are very few issues in taking an Access 2003 MDB un-covnerted and running it in Access 2010.
I would take a copy of the DB, do a compact/repair, possibly a /decompile, and then make sure it compiles without error in 2003. After that, try it in 2010 and see if you get different results (and I expect you will).
Jim.
What is the value of your "Criteria" call when you find the failure? Also, be sure you're using the "WHERE" portion of the OpenReport method. While you could use the "FILTER" portion in earlier versions, MSFT tightened up the code in newer versions, and that call will fail if it's not properly written.
What version of Excel are you running on the machine? If you're running newer versions, you might consider using a different SpreadsheetType.
Be sure that you don't run those two versions simultaneously. You can cause serious issues if you do - in fact, I experienced data loss in a few databases while trying to run them simultaneously.
Most developers have installed only ONE version of the newer versions, and then run 2003 or earlier in a virtual environment. You would be wise to consider doing this as well.