How to determine Jet database Engine Type programmatically

c# .net ms-access jet
MZB picture MZB · May 24, 2010 · Viewed 9.6k times · Source

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

Todd Main picture Todd Main · May 29, 2010

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)

Related questions

How can I get a list of tables in an Access (Jet) database?

I need to see if a table exists in an Access database used by my c# program. Is know there are SQL commands for other databases that will return a list of tables. Is there such a command for Access/…

Read More
Connecting to ACCDB format MS-ACCESS database through OLEDB

I've recently made another question about connecting to MS-ACCESS database with .NET in C# or VB.NET. It worked just as intended with MDB, but with accdb it caused an exception in conn.Open(); which follows: alt text http://dl.…

Read More
Loading Access DB Table to Datatable

I have a database in .ACCDB format with some tables. I'm successfully loading it into an OleDbDataReader with the following code: string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\\marcelo.accdb"; OleDbConnection conn = new OleDbConnection(connectionString); string sql = "SELECT * …

Read More