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.EXE" "C:\BCBS\ipc_11177D-BCBSSC.mdb" /wrkgrp "C:\InventoryPro\SYSTEM8.MDW".

One other thing to note the machine that exhibits the problem has both access 2010 and access 2003 installed
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access Security can be used in 2010, but not with the .accdb format. You'd have to maintain the database in the .mdb format - but Access would use ULS as always if you did so.

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.

This is some of the suspect code
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, tQuery, SA, True
 Shell "Excel """ & SA & """", vbNormalFocus
What version of Excel are you running on the machine? If you're running newer versions, you might consider using a different SpreadsheetType.
I switch between the 2 libraries by executing my code using this shortcut for changing Office11 to Office14
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.
bensonwalkerAuthor Commented:
I tried using the where portion... same results drops the filter entirely.
Trying to swap between two versions of Access is problematic since whichever version of Access starts, it wants to take over the registry.  So usually you will see something that says "Installing..............." as the version of Access you opened attempts to take over the world.  The problem is that you need to be running as an administrator for this process to be successful.  If you are not running in administrator mode, the takeover will be incomplete and you end up with some but not all libraries that don't get swapped out but you won't get any error messages.

The simplest solution is to create two shortcuts.  One for each version of Access.  Configure these shortcuts to run with administrator permissions.

The best solution is to use either completely separate PCs or use Virtual machines to host the different versions as has already been suggested.

Typically, old apps can run in newer versions of Access so users never need to have multiple versions installed.  It is normally only developers who need multiple versions and that is so they can get the reference libraries correct and to test in the version the user is actually using.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bensonwalkerAuthor Commented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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).

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.