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.OL
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
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.OL
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
Rory Archibald
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.
PS You could also use conditional compilation with the #VBA7 constant.
ASKER
Thank you Rorya. Goow suggestion but is it possible that Excel 2010 is installed while still using an Access 2007 DB?
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.
ASKER
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.
Where did you find that? It certainly doesn't work on my machine with 2010 installed, but the 12.0 provider does.
ASKER
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.
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/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thank you for your quick reply. We can close this wondering.