CODEWITHSUNDEEP
ms-accessvbscript
p4r1
p4r1

Reputation: 2650

How to programmatically convert Access 1997 .mdb to Access 2007 .accdb

I'm in the starting process of building an application that walks through a folder structure, starting at a given root path, and converts all found Access 1997 .mdb files into the newer Access 2007/2010 .accdb format. However, I'm running into some troubles when doing to actual file conversion.

I'm using Microsoft's Access Interop API (https://msdn.microsoft.com/en-us/library/office/ff193465.aspx) to handle the conversion, so this has to be written in VBScript. Script is below:

Dim app 
Set app = CreateObject("Access.Application")
app.ConvertAccessProject "C:\Users\[User]\Access Conversion Utility\sample.mdb", "C:\Users\[User]\Access Conversion Utility\converted.accdb", acFileFormatAccess12

When run, I get the error "The project cannot be converted into this format. The project can only be converted to Access 2000 or newer format." Yet, this same error message shows up regardless of the file parameter enum value, be it 'acFileFormatAccess97' or 'acFileFormatAccess2000'. Does anyone know the details of this error and what a possible solution could be? I've tried changing the extension of the second parameter, thinking that was part of the issue, but this made no changes to the error message.

The sample file I'm using is able to be opened in Access just fine, it's just the conversion itself that fails.

By all means, if anyone has a better idea or approach to do the conversion programatically, I would love to hear it, but this is the only one I was able to find. The plan is to run this script from a GUI application written in C#, but also allow for the application to be run via the command line as well.

Upvotes: 3

Views: 4173

Answers (4)

Enigma
Enigma

Reputation: 61

If the Access 97 is Encoded it wont be converted to Access 2007 you must decode the database first then do the conversion.

I end up with converting the 97 database to access 2000 first with code by using:

app.ConvertAccessProject(
        @"C:\Users\Public\test\a97test.mdb",
        @"C:\Users\Public\test\a2000converted.mdb",acFileFormatAccess2000);

then manually decode the database, then convert the 2000 database to 2007 database format:

app.ConvertAccessProject(
            @"C:\Users\Public\test\a2000converted.mdb",
            @"C:\Users\Public\test\a2007converted.accdb", acFileFormatAccess2007);

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123849

I'm using Microsoft's Access Interop API ... so this has to be written in VBScript.

Nonsense. I just tried this in C# (Visual Studio 2010) and it worked fine for me with Access 2010:

// COM reference required for project
//     Microsoft Access 14.0 Object Library

var app = new Microsoft.Office.Interop.Access.Application();
app.ConvertAccessProject(
        @"C:\Users\Public\test\a97test.mdb",
        @"C:\Users\Public\test\a2007converted.accdb",
        Microsoft.Office.Interop.Access.AcFileFormat.acFileFormatAccess2007);
app.Quit();

Note that this will almost certainly not work with Access 2013 and later, but neither would a VBScript implementation of COM Interop for those versions of Access since they absolutely refuse to open Access_97 (and earlier) databases.

Upvotes: 3

Gustav
Gustav

Reputation: 56006

Problem is twofold.

First, Access 2007 and later won't read older formats than the JET 4 format of Access 2000.

Second, the constant for the 2007 format is acFileFormatAccess2007, not acFileFormatAccess12, it's the numeric value of the constant that is 12.

On this point, the documentation is wrong:

AcFileFormat Enumeration (Access)

So you will have to obtain an older version of Access for the first conversion to JET 4, then another step to convert to 2007 format.

Edit:

Or, of course, do as Gord shows.

Upvotes: 1

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

Plain VBScript does not know about the ac* constants. So define them properly.

Upvotes: 0

Related Questions