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.

Whitehall

Can you not read it from the Connection object. – Seventeenth

Are you really updating Access 97 or prior databases? Anything from Access 2000 on will use Jet 4.0. – Galiot

Jet 4 supports backward compatibility for A97. That is, you can use Jet 4 to update a Jet 3.x database -- no need to use Jet 3.x for that. – Felisafelise

@scope-creep The connection object only seems to give back the information I used to open the connection, not information on the database connected to. – Whitehall

@Galiot - The aim is to convert to Access 2003 (which Identifies the version as "old"). I think they are whatever version would be created by 2000, but can't find the document of Access -> Jet engine type at the moment. – Whitehall

A search through the information OLEDBConnection.GetSchema() provides doesn't find this info. – Whitehall

@Galiot - yes, it may have originally been created with Access 97 DB. Wikipedia en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine gives the Jet engine versions, if anybody needs them. – Whitehall

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)

Skaw

Why would anyone use ADO inside Access? DAO is by far the preferred data access interface for Jet data from within Access itself. – Felisafelise

Is there any way to get the information from an OLEDBConnection? – Whitehall

For some reason ADO seems to be expecting a Workgroup file and barfs at opening the connection "workgroup information file missing opened exclusively by another user" - this is before I open the connection with OLEDB. – Whitehall

OLEDBConnection is a general connection not specific to Jet, so it doesn't have Jet's connection properties. On the Workgroup file - is it set up as as a Workgroup file? If so, you'll need to add to your connection string something like "Jet OLEDB:System Database=MySystem.mdw" where MySystem equals your DSN. Things are are working well from my side in .NET with Console.WriteLine(cnn.Properties("Jet OLEDB:Engine Type").Value). Did you set your reference to "adodb" in .NET components instead of "ActiveX Data Objects" in COM? Oh, also, is the Access MBD completely closed? – Skaw

@Otaku - There isn't a .mdw file and never was. I may not have the chance to check the others before this question closes. Thanks for your help. Was using .NET ADODB. – Whitehall

@MZB: Sounds like you've got the connection open elsewhere and haven't closed it. You can try opening as exclusive. The code works though. – Skaw

@MZB: if it's an MDB, there was definitely a workgroup file, as it's an inherent part of the database engine. Whether or not you used the default one or a specific one is unknown, but there is always a workgroup file involved with any MDB file, no exceptions. – Felisafelise

@David-W-Fenton. Reading support.microsoft.com/kb/305542 I feel like Arthur Dent and the plans for the demolition of his house. There is an (automatically-created) file, but it is in a hidden location and you have to look at a KB article to find out it exists... – Whitehall

Knowing the location of the appropriate workgroup file is part of working with Jet, and if you don't know about its importance and use, you're just unaware of the details of how Jet works. It's widely documented in many other places than the cited KB article, though certainly that article is on point for the present situation. There is nothing illogical or arbitrary about this, so the comparison to Arthur Dent is completely unfounded. You were just ignorant of the issue, as would be the case for the specifics of operating any database engine with which you're not familiar. – Felisafelise

The actual error turned out to be specifying the DB user/password both in the connection string and as parameters in the call to Open(). The system database (System.mdw) file did not need to be specified. Unfortunately the Jet OleDB:EngineType refers to the current engine, not to the file. A comparison of all the ADODB.Connection properties reveals no property that distinguishes between different access file formats. – Whitehall

@MZB: Not so sure about that. Check msdn.microsoft.com/en-us/library/aa141406(office.10).aspx which specifically states Jet OLEDB:Engine Type: Once a database has been opened, this property can be read to determine what file version or format is open. – Skaw

@Otaku - my mistake. You are correct. I had a bad test case (Access 2000 versus Acccess 2003). Access knows the subtle difference, but the connection parameters don't distinguish between these two. Intriguingly Acccess will convert 2000 -> 2003, but JRO.CompactDatabase won't. – Whitehall

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;
}

Metacenter

Almost - but not quite. +1 for a near miss. Two issues: (a) Can't assume user has Access installed, only JET, and (b) the above solution opens up the Access UI and prompts user as to whether to open an "unsafe" file database may contain macros. As the user isn't even aware that JET is being used (and Access isn't on the System Requirements list) I can't do it this way. (I tried telling Access it was invisible, but presumably the cloak of invisibility gets lifted if it wants to prompt the user...) – Whitehall

Check out this link to avoid pop up boxes: support.microsoft.com/kb/317114 (but it won't help you as you can't assume the user has Access installed - you should update your question to note that). – Metacenter

Thanks for your help - the second solution looks close, but seems to always return "35" regardless of the DB format. (I tried to give you another +1 but the system reduced the score rather than increased it!) – Whitehall

While not the answer you seek, another solution would be to just add a property to any database your app opens, and check for your custom "HasBeenUpgraded" property, and if app doesn't find it, just upgrade it, then add the property to it. – Metacenter

Microsoft.Office.Interop.Access.Application requires Access installed. – Decile

Answer 3

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

Okinawa

If I'd been here before an answer automatically got accepted, you would have had the tick! It's always better to test for the presence of a feature rather than the presence of a particular version. Thanks. – Whitehall

But the answer is wrong, as the issue of whether or not SQL 92 fails or not is a matter of which version of Jet is being used to access the data file and which interface you're using (ADO uses SQL 92, DAO uses SQL 89). This is completely independent of the version of the data file. – Felisafelise

@David-W-Fenton. I'll have to investigate this further. The Jet drivers are definitely more recent than the database, but are failing DDL calls using newer syntax on an older database. Access is via OLEDB, incidentally, which presumably equals ADO. – Whitehall

Right, ADO is a wrapper around OLEDB, so if you're using it, you would use the so-called SQL92 syntax. But the point is that this is not going to work as a test for the version of the target file, as opposed to the data interface (ADO/DAO) and Jet/ACE version involved in accessing it. – Felisafelise

Answer 4

I know this an old post but I've looked for days to find a list of other properties that can be retrieved like the "Jet OLEDB:Engine Type". Here's a MS link with all the properties listed. Jet Provider Specific Connection Parameters Listing

Nursery