The example you provide shows the cursor as being client sided.
Should this not be a server sided cursor where the database is on a different machine to that which the code is running?
I have just completed a timesheet entry program where the database is on a networked machine internally remote to that of the client machine. With a client cursor the record save (14 fields of string data) takes approximately 4 seconds. With a server side cursor the save is reduced to the point where it is apparently instantaneous.
The other thing I have seen is numerous ways of getting the auto id of the last record input.
With a server side cursor the recordset will, following the update, contain the id field of the updated recordset. Below is the code I use (taken out of context) to add a new record and retrieve its auto id (the field not surprisingly is called id). The dbip bit is the IP address of the server (which changes depending on whether I am working at work or at home)
The code below has always returned to me the correct auto id. This is on an Access 2 and Access 2003 database.
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\" + dbip + "\Server\Database\casecharging2.mdb;Persist Security Info=False"
lpConnection = FF_AdoCreateObject("ADODB.Connection")
If IsFalse lpConnection Then
myerror="Cannot Create ADO Connection Object!"
GoTo Terminate2
End If
' Sets the connection string
FF_AdoConnection_SetConnectionString(lpConnection, Constr)
' Opens the database
FF_AdoConnection_Open(lpConnection)
If FF_AdoResult Then
myerror="Cannot Open The Database!"
GoTo Terminate2
End If
'Creates an ADO recordset Object
lpRecordset = FF_AdoCreateObject("ADODB.Recordset")
If IsFalse lpRecordset Then
myerror="Cannot Create A Recordset!"
GoTo Terminate2
End If
' Sets the recordset's active connection
FF_AdoRecordset_PutRefActiveConnection(lpRecordset, lpConnection)
' Sets the cursor location
FF_AdoRecordset_SetCursorLocation(lpRecordset, %adUseServer)
' Sets the cursor type
FF_AdoRecordset_SetCursorType(lpRecordset, %adOpenKeyset)
' Sets the lock type
FF_AdoRecordset_SetLockType(lpRecordset, %adLockOptimistic)
' Sets the source for the recordset
SqlStr = "SELECT * FROM EXPENSES"
FF_AdoRecordset_SetSource(lpRecordset, SqlStr)
If FF_AdoResult Then
myerror="Database Query String Failed!"
GoTo Terminate2
End If
' Opens the recordset
FF_AdoRecordset_Open(lpRecordset)
If FF_AdoResult Then
myerror=FF_AdoGetErrorInfo(lpConnection, FF_AdoResult)
GoTo Terminate2
End If
Local v1 As Variant, v2 As Variant
v1 = vFieldList()
v2 = vValues()
FF_AdoRecordset_AddNew(lpRecordset, v1, v2)
FF_AdoRecordset_UpDate(lpRecordset)
If FF_AdoResult Then GoTo Terminate
FF_AdoRecordset_GetCollect(lpRecordset, "id", vRes)
tempid$= Str$(Variant#(vRes))
This results in tempid$ holding the string value of the auto id for the newly input record.
I mention this as I am now concerned that I have been doing this wrong and have so far got away with it.
Cheers
Paul
Choosing the most appropiate cursor location depends of the functionality that you need. I agree that in the AddNew example I should have chosen a server cursor, since the example only demonstrates how to use the AddNew method. However, if you need, for example, to retrieve the record count, you need to use a client cursor, and if AddNew is slow using this kind of cursor then you can perform the insert using an SQL string or an stored procedure. If you want scrolling, you are better off using a client cursor.
Quote
With a server side cursor the recordset will, following the update, contain the id field of the updated recordset.
As noted in the example, "The Identity (or AutoNumber) field should always be available when a server-side cursor is used." So I thought that there was not need to write an example to demonstrate it. Using a client cursor, it is available if you use Access 2000 or Office XP and a Jet OLE DB 4.0 or Jet ODBC 4.0 driver, but not if you use Access 97 or a 3.51 driver.