The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection. Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.
The following code demonstrates using the SELECT @@Identity to retrieve the value of the newly inserted auto-increment field. The code snippet also includes code to create the table for the query.
Dim cnDatabase As ADODB.Connection
Dim rsNewAutoIncrement As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strPathToMDB As String
strPathToMDB = "C:\NewJet4.MDB"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToMDB & ";"
Set cnDatabase = New ADODB.Connection
cnDatabase.Open strConn
strSQL = "CREATE TABLE AutoIncrementTest " & _
"(ID int identity, Description varchar(40), " & _
"CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
strSQL = "INSERT INTO AutoIncrementTest " & _
"(Description) VALUES ('AutoIncrement Test')"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
strSQL = "SELECT @@Identity"
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.Open strSQL, cnDatabase, adOpenForwardOnly, _
adLockReadOnly, adCmdText
MsgBox "New Auto-increment value is: " & rsNewAutoIncrement(0).Value
rsNewAutoIncrement.Close
Set rsNewAutoIncrement = Nothing
strSQL = "DROP TABLE AutoIncrementTest"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
cnDatabase.Close
Set cnDatabase = Nothing
Thanks to this newly added functionality, you can see the newly added auto-increment values in your client-side ActiveX Data Objects (ADO) recordsets in ADO 2.1 and later. When you submit the new row to the Jet provider by calling Update or UpdateBatch (depending on your choice of LockType), the ADO cursor engine generates an INSERT INTO query to create the new row in the table. If the recordset contains an auto-increment field, ADO will also generate a SELECT @@Identity query to retrieve the value generated for that auto-increment field. The following code demonstrates this feature:
Dim cnDatabase As ADODB.Connection
Dim rsNewAutoIncrement As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strPathToMDB As String
strPathToMDB = "C:\NewJet4.MDB"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToMDB & ";"
Set cnDatabase = New ADODB.Connection
cnDatabase.Open strConn
strSQL = "CREATE TABLE AutoIncrementTest " & _
"(ID int identity, Description varchar(40), " & _
"CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
strSQL = "SELECT ID, Description FROM AutoIncrementTest"
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.CursorLocation = adUseClient
rsNewAutoIncrement.Open strSQL, cnDatabase, adOpenStatic, _
adLockOptimistic, adCmdText
rsNewAutoIncrement.AddNew
rsNewAutoIncrement("Description").Value = "AutoIncrement Test"
rsNewAutoIncrement.Update
MsgBox "New Auto-increment value is: " & rsNewAutoIncrement(0).Value
rsNewAutoIncrement.Close
Set rsNewAutoIncrement = Nothing
strSQL = "DROP TABLE AutoIncrementTest"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
cnDatabase.Close
Set cnDatabase = Nothing
You can create a new Jet 4.0 database using Microsoft Access 2000 or using the ADOX library that is included with MDAC 2.1. To use this library in your Visual Basic project, create a reference to
Microsoft ADO Ext. 2.1 for DDL and Security. You can then use code like the following to create a new Jet 4.0 database:
Dim strPathToMDB As String
Dim catNewDatabase As ADOX.Catalog
strPathToMDB = "C:\NewJet4.MDB"
If Dir(strPathToMDB) <> "" Then
Kill strPathToMDB
End If
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToMDB & ";"
Set catNewDatabase = New ADOX.Catalog
catNewDatabase.Create strConn
Set catNewDatabase = Nothing
To determine the format of your Microsoft Access database, check the dynamic "Jet OLEDB:Engine Type" property in the Connection object's Properties collection. The property will return a value of 5 for Jet 4.x databases. The following code snippet demonstrates using the property:
Dim cnDatabase As ADODB.Connection
Dim strConn As String
Dim strPathToMDB As String
strPathToMDB = "C:\NewJet4.MDB"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToMDB & ";"
Set cnDatabase = New ADODB.Connection
cnDatabase.Open strConn
If cnDatabase.Properties("Jet OLEDB:Engine Type").Value = 5 Then
MsgBox "Jet 4.0 database"
Else
MsgBox "Not a Jet 4.0 database"
End If
cnDatabase.Close
Set cnDatabase = Nothing
Microsoft Data Access SDK; search on: "Provider-Defined Properties";
topic: "DBPROPSET_JETOLEDB_DBINIT"
