MS Access ADODB Connection String Selection

Good day,

I have an Excel based tool that updates and pulls data from an Access database.  I'm currently running Office 2007.  

The connection string for Access 2007 is "Provider=Microsoft.ACE.OLEDB.12.0....".  This connection string works fine.  I know that the connection string for an Access 2010 DB is "Provider = Microsoft.ACE.OLEDB.14.0...."

I want to be able to use an Access 2010 DB when the upgrade is applied without having to change the code.  I did this sort of thing with an Access 2003 DB by testing the Access file extension so that when the database was upgraded to Access 2007 the transition was automatic.

The problem is that the file extension for Access 20007 and 2010 is the same.  Are there any suggestions other than if the connection to Access 2007 throws an error to automatically try the 2010 connection string?  I don't yet have Access 2010 so I can't test this but I prefer to anticipate the upgrade.

Thoughts?

Thank you,
Jeff
jpjones23Asked:
Who is Participating?

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

x
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.

Rory ArchibaldCommented:
If you will be running the code from Excel 2010, test the Application.Version - if it's 12, use the 2007 code, if 14, use the 2010 code.
0
Rory ArchibaldCommented:
PS You could also use conditional compilation with the #VBA7 constant.
0
jpjones23Author Commented:
Thank you Rorya.  Goow suggestion but is it possible that Excel 2010 is installed while still using an Access 2007 DB?
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Rory ArchibaldCommented:
Actually, are you sure that there is a 14.0 provider? To the best of my knowledge you still use 12.0 in Excel 2010.
0
jpjones23Author Commented:
I did some searches and found the 14.0 provider reference for 2010 but didn't find any way to differentiate between the 2 versions for the connection.  But now, in thinking about it I can probably get the exact err number by trying to link to 14.0 rather than 12.
0
Rory ArchibaldCommented:
Where did you find that? It certainly doesn't work on my machine with 2010 installed, but the 12.0 provider does.
0
jpjones23Author Commented:
The err number, at least when connection to 14.0 for a 12.0 DB is 3706.  Knowing that might tighten up the code a bit.
0
Rory ArchibaldCommented:
I believe the beta driver documentation incorrectly states that the provider was 14.0 - see this Answers posting for example: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/b5ba0738-c03e-4a8c-bcc8-ea1e15657a24/


0
Rory ArchibaldCommented:
Yeah - 3706 is an incorrect provider error, so stick with 12.0 :)
0

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
jpjones23Author Commented:
LOL, yep works far better.  I'll test for the specific err number 3706 with the 12.0 provider .  If I get it I'll try to connect with 14.0.  If it's anything else I'll take other steps.

Thank you for your quick reply.  We can close this wondering.
0
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.