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?
 
Rory ArchibaldConnect With a Mentor Commented:
Yeah - 3706 is an incorrect provider error, so stick with 12.0 :)
0
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
jpjones23Author Commented:
Thank you Rorya.  Goow suggestion but is it possible that Excel 2010 is installed while still using an Access 2007 DB?
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.