Create a 2003 .mdb file

Question

Dim LFileName as String
Applicaion.FileDialog(msoFileDialogSaveAs).InitialFileName = "\\filepath\\" & Format(Now,"mm-dd-yyyy") & ".mdb"
If intChoice <> 0 Then
    LFileName = Application.FineDialog(msoFileDialogSaveAs).SelectedItems(1)
End If

If Dir(LFilename) <> "" Then Kill LFilename

Set ws = dbEngine.Workspaces(0)
Set db = ws.CreateDatabase(LFileName, dbLangGeneral)

This is "create .mdb file" code, and it works. The only problem is that after I transfer my tables over and everything, and then try to relink my frontend to the backend (this created db) it gives me this error:

Microsoft Access dos 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

BUT. If I take the newly created backend file (made with the vba code) and Save & Publish > Microsoft Access 2003 .mdb format, then try to relink it, it works. Is it saving as a .accdb but with the .mdb suffix or something (?)

So this makes me wonder if there's a way to specifically create a mdb 2003 format mdb with VBA? I've been googling this all day and I can't find anything other than the code above. Is there a way to word this question better?

Info: I'm using Access 2010, but I have to use .mdb because some people in the office still use office 2007. I know office 2007 can use .accdb files, but it can't handle 2010 .accdb files for some reason.

Answer 1

Is [Access 2010] saving as a .accdb but with the .mdb suffix or something (?)

Yes, it is. If you open the ".mdb" file in Access 2010 the title bar will say

...: Database (Access 2007 - 2010) - Microsoft Access

If you want to create an Access_2003-format database file use

Set db = ws.CreateDatabase(LFileName, dbLangGeneral, dbVersion40)

Answer 2

I'm migrating many Access 2000, 2002-2003 format database application to be used in an Access 2016 environment. I've noticed that some of these database applications create a temporary database.

In an Access 2003 environment the temporary database file created will use whatever default database format has been configured in the instance of Access 2003 e.g. format 2000 or 2002-2003 using the following code:

Set dbnew = wk.CreateDatabase(gstrtmpdb, dbLangGeneral)

Note: the 'Option' database version argument is not being used here.

In an Access 2016 environment, the same code will create a database with the 2007-2016 irrespective of whatever default format is configured in the Access instance. In this case, the front-end database format 2003 attempts to create a linked table to the newly created temporary database (format 2007-2016) and the same error is thrown: Microsoft Access does not support linking to an Access database or Microsoft Office Excel workbook saved in a format that is later than the current database format.

Confusion can occur when using Windows Explorer to view the details of this temporary database file, created in the Access 2016 environment and its associated lock file created when the database is opened. The VBA code creates the temporary database file e.g. tempdb.mdb.
On the face of it, this looks like an Access 2000 or 2002-2003 format database file.
If you open this tempdb.mdb the lock file created is named
tempdb.laccdb (the 2007-2016 format of the lock file). This is quite confusing.

To avoid confusion if you need a 2007-2016 format database created then it would be best to amend the VBA code by renaming the database that gets created to have a .accdb extension rather than leaving it to be an .mdb In this case, we want a 2003 format with a .mdb extension so the fix as already mentioned is to use the ‘Option’ database version argument, In this case
using the following code.

Create a 2003 format database

---

Set dbnew = wk.CreateDatabase(gstrtmpdb, dbLangGeneral, dbVersion40)

Note: the 'Option' database version argument is being used here.

Hope this help people.