INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity

ID: Q232144

The information in this article applies to:

Microsoft OLE DB Provider for Jet, version 4.0
Microsoft Data Access Components version 2.1 SP2
ActiveX Data Objects (ADO), version 2.1 SP2
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

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. 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"

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by David Sceppa, Microsoft Corporation.

Additional query words: ADOX, Jet, Identity, AutoIncrement

Keywords : kbADO kbDatabase kbJET kbMDAC kbOLEDB210 kbGrpVBDB kbGrpMDAC
Version : WINDOWS:2.1 SP2,4.0,5.0,6.0
Platform : WINDOWS
Issue type : kbinfo