StrataFrame Forum

OLE DB DataSource can't access Office 2007 Dbs

http://forum.strataframe.net/Topic10752.aspx

By Andria Jensen - 8/7/2007

I have recently upgraded to Office 2007 as many of our users are sure to do if they haven't already.  I have found that the few Access dbs that are being used are now not working with my BOs.  I first got code errors, but after looking at the BO Mapper realized that it's just not recognizing the db as a valid db.  When I go to the BO Properties, I have a connection string in the textbox at the bottom which used to work...Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=C:\Production\Local\ToolTips.mdb;   Now, this no longer works correctly and gives Unspecified Error with the Source being the JET Database Engine.

I looked around on the internet a little and discovered that Office 2007 no longer uses JET, but instead uses ACE for its dbs.  And not only that, but if you have an Office 2007 database it's not backwards compatible and cannot be opened using JET.  So, I am betting on that being my issue.  It's trying to use JET to open what is now an ACE db.  My problem is that I don't know how to change the configuration now.  Is there a different provider I need to be using?  The trick is that I won't know ahead of time whether a user will have JET or ACE and it may change since they may upgrade one day.  Is there some way I can make the BO work for both engines??  Please advise, I'm a little lost on this one.  Thanks.

By StrataFrame Team - 8/8/2007

I found this online: http://www.connectionstrings.com/?carrier=access2007 Looks like you'll just have to manually type in your connection string, you won't be able to use the connection string builder for Access.  Changing the provider from Jet to that ACE provider should do the trick:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

And if you've set the password on your accdb file, you'll need to use the connection string like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:database Password=MyDbPassword;

Once you change the conn string, everything will be just plain OLE DB, so the BOMapper and your app should work like normal (unless Microsoft decided to change the way that the GetSchema() returns for Access 2007...).