Microsoft KB Archive/244136

= INFO: When Is the Access AutoNumber Field Available? =

Article ID: 244136

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * 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

-



This article was previously published under Q244136



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 OLE DB Provider for Jet 4.0 driver and with the Jet 4.0 ODBC 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:

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

Dim sconnect As String

Private Sub Form_Load Dim sconnect As String 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 'Test Office 97 using JETOLEDB '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"

' Test Office 2000 using JETOLEDB 'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb" 'sconnect = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb"

'Test Office XP using JETOLEDB 'sconnect = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb" 'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb"

' Using ODBC drivers

'Test Office 97 using ODBC drivers 'sconnect = "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=nwind.mdb;" & _ "DefaultDir=D:\Program Files\Microsoft Visual Studio\VB98\;" & _ "Uid=Admin;Pwd=;" ' 'Test Office 2000 / 9 using ODBC drivers 'sconnect = "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=northwind.mdb;" & _ "DefaultDir=D:\Program Files\Microsoft Office\Office\Samples;" & _ "Uid=Admin;Pwd=;"

'Test Office XP / 10 using ODBC drivers 'sconnect = "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=northwind.mdb;" & _ "DefaultDir=D:\Program Files\Microsoft Office\Office10\Samples;" & _ "Uid=Admin;Pwd=;"

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

