DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 232144 - Last Review: December 3, 2003 - Revision: 3.1

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"

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
Keywords: 
kbdatabase kbinfo kbjet KB232144
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support