Article ID: 232144
Article Last Modified on 12/3/2003
APPLIES TO
- Microsoft OLE DB Provider for Jet 4.0
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
This article was previously published under Q232144
SUMMARY
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.
MORE INFORMATION
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
REFERENCES
Microsoft Data Access SDK; search on: "Provider-Defined Properties"; topic: "DBPROPSET_JETOLEDB_DBINIT"
Additional query words: ADOX Jet Identity AutoIncrement
Keywords: kbinfo kbdatabase kbjet KB232144