INFO: When is the Access Autonumber Field Available?

ID: Q244136

The information in this article applies to:

Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 6.0
ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2

SUMMARY

The Access autonumber field is always available when a server side cursor is used. When a client side cursor is used, the autonumber field is only returned immediately when an Access 2000 database is used with the JetOledb 4.0 driver.

MORE INFORMATION

The following table shows when the autonumber field is immediately available without a requery.

Client side cursor for Access 97 and 2000 with different drivers:

Driver Access 97 Access 2000

Jet Oledb 3.51 NO Unrecognized Database Format

Jet Oledb 4.0 NO YES

ODBC NO NO

The following code sample demonstrates the results shown in the above table:

Open a standard EXE project in Visual Basic. Form1 is created by default.

Under Project References, select Microsoft ActiveX Data Objects .

Place two CommandButtons on the form.

Paste the following code in the form code window:


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Command1_Click()
   rs.Open "select * from ORDERS", cn, adOpenKeyset, adLockOptimistic
End Sub

Private Sub Command2_Click()
   rs.AddNew
   rs!CustomerID = "ALFKI"
   rs!EmployeeID = 1
   rs.UpdateBatch
   Debug.Print rs!OrderID & Chr(9) & rs!CustomerID & Chr(9); rs!EmployeeID
End Sub

Private Sub Form_Load()
 Set cn = New ADODB.Connection
 Set rs = New ADODB.Recordset
 
 'Change the paths to the mdb's in the following statements for your machine; 
 'Uncomment ONE of the statements to set sconnect to a valid connection string.
 
' Using JETOLEDB drivers
 
'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb"
'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
'sconnect = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
'sconnect = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb"

' Using ODBC drivers

'sconnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                        "Dbq=nwind.mdb;" & _
                        "DefaultDir=D:\Program Files\Microsoft Visual Studio\VB98;" & _
                        "Uid=Admin;Pwd=;"
'sconnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                        "Dbq=northwind.mdb;" & _
                        "DefaultDir=D:\Program Files\Office2000\Office\Samples;" & _
                        "Uid=Admin;Pwd=;"

cn.CursorLocation = adUseClient
'cn.CursorLocation = adUseServer
cn.Open sconnect
End Sub

REFERENCES

For additional information, please click the article number below to view the article in the Microsoft Knowledge Base:

Q190370 PRB: AutoNumber Field Is Not Incremented When Using ADO

Additional query words:

Keywords : kbADO kbDatabase kbJET kbMDAC kbODBC kbOLEDB210 kbGrpVBDB kbDSupport
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbinfo