Wednesday, June 2, 2010

CreateDatabase Problem in Access 2007 MDB

I ran into an interesting situation that I thought might be interesting to blog.

I have an Access 2003 format database that I am running in Access 2007 (my company recently upgraded, but I'm keeping the MDB file format for now).  In it, I have a line of VBA code like this:

Set db = CreateDatabase("C:\MyDatabase.mdb", dbLangGeneral)

It creates a blank Access database for me to export data to.  The database created is used as a back-end database for another MDB, that is, the tables are linked into another database application, which is also in 2003 format.

In this other front-end, I've started getting errors that said:

Microsoft Office Access does not support linking to an Access database or Microsoft Office Excel Workbook saved in a format that is a later version than the current database format.


This is odd because I'm working all in the Access 2003 MDB format.  But when I investigated, it turned out that the database created with the above code was an ACCDB format database (Access 2007) but with an MDB extension.  I can tell because if I open it, the LACCDB locking file is created.
 
In retrospect the problem is obvious.  The CreateDatabase method creates the database in the default format, which for Access 2007 is dbVersion120.  So I needed to specify the format I want:
 
Set db = CreateDatabase("C:\MyDatabase.mdb", dbLangGeneral, dbVersion40)
 
.

4 comments:

Anonymous said...

thank you
that solved my problem!

wilo

JoeOxfordCT said...

Perfect !

Just ran into this exact issue.
Thank you Roger ! (& Google)

Joe

Anonymous said...

Thank you so much Roger. Concisely explained, with sound reasoning. NOt only did it fix my problem, it also improved my understanding of these interop problems with various versions of Access.

Anonymous said...

You star, thank you!

I can now stop banging my head against a brick wall!