How to determine Jet database Engine Type programmatically

Question

I have a program which needs to upgrade any Access (Jet) database it opens to JET Version4.x if it isn't already that version. (This enables use of SQL-92 syntax features)

Upgrading is (relatively) easy. A call to the JRO.JetEngine object's CompactDatabase method (as described here) should do the trick, but before I do this I need to determine whether an upgrade is required. How do I determine the Jet OLEDB:Engine Type of an existing database? Can this be determined from an open OleDBConnection?

Note:

1. I'm talking about database versions, not Jet library versions.
2. C# or .Net solution greatly appreciated.
3. This is an application which uses the Jet engine, NOT an Access application.

Answer 1

You'll have to set a reference to ADO and then you can get the property.

From inside of Access

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

From outside of Access

Dim cnn As New ADODB.Connection
cnn.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Contact.mdb

And finally

Debug.Print cnn.Properties("Jet OLEDB:Engine Type").Value

This .Value will return 1 to 5. If it is 5, it is already in Jet4x, otherwise it is an earlier version.

Here's another example of the upgrade technique you're looking at as well: Convert MDB database to another format (JET, access version)

Answer 2

You can use Office Interop and get the info (blatently stolen from the article):

How Can I Determine Which Version of Access was Used to Create a Database?

    public void WhichVersion(string mdbPath)
    {
        Microsoft.Office.Interop.Access.Application oAccess = new Microsoft.Office.Interop.Access.ApplicationClass();
        oAccess.OpenCurrentDatabase(mdbPath, false, "");

        Microsoft.Office.Interop.Access.AcFileFormat fileFormat = oAccess.CurrentProject.FileFormat;

        switch (fileFormat)
        {
            case Microsoft.Office.Interop.Access.AcFileFormat.acFileFormatAccess2:
                Console.WriteLine("Microsoft Access 2"); break;
            case Microsoft.Office.Interop.Access.AcFileFormat.acFileFormatAccess95:
                Console.WriteLine("Microsoft Access 95"); break;
            case Microsoft.Office.Interop.Access.AcFileFormat.acFileFormatAccess97:
                Console.WriteLine("Microsoft Access 97"); break;
            case Microsoft.Office.Interop.Access.AcFileFormat.acFileFormatAccess2000:
                Console.WriteLine("Microsoft Access 2000"); break;
            case Microsoft.Office.Interop.Access.AcFileFormat.acFileFormatAccess2002:
                Console.WriteLine("Microsoft Access 2003"); break;
        }

        oAccess.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
        Marshal.ReleaseComObject(oAccess);
        oAccess = null;
    }
}

EDIT:

Another method is to use DAO (from this link translated from Japanese). You may have to tweak the values, but it looks like a good place to start.

public int GetCreatedVersion(string mdbPath)
{
    dao.DBEngine engine = new dao.DBEngine();
    dao.Database db = engine.OpenDatabase(mdbPath, false, false, "");
    string versionString = db.Properties["AccessVersion"].Value.ToString();
    int version = 0;
    int projVer = 0;

    switch (versionString.Substring(0, 2))
    {
        case "02":
            version = 2; break;
        case "06":
            version = 7; break;
        case "07":
            version = 8; break;
        case "08":
            foreach (dao.Property prop in db.Properties)
            {
                if (prop.Name == "ProjVer")
                {
                    projVer = int.Parse(prop.Value.ToString());
                    break;
                }
            }
            switch (projVer)
            {
                case 0:
                    version = 9; break;
                case 24:
                    version = 10; break;
                case 35:
                    version = 11; break;
                default:
                    version = -1; break;                            
            }
            break;
        case "09":
            foreach (dao.Property prop in db.Properties)
            {
                if (prop.Name == "ProjVer")
                {
                    projVer = int.Parse(prop.Value.ToString());
                    break;
                }
            }
            switch (projVer)
            {
                case 0:
                    version = 10; break;
                case 24:
                    version = 10; break;
                case 35:
                    version = 11; break;
                default:
                    version = -1; break;
            }
            break;
    }
    db.Close();

    return version;
}

Answer 3

Just make a test call of a statement which uses SQL-92 language features. If it fails, you need to upgrade.