my function to add records to a database with an autonummer ID field
Hi Jason,
Thanks for your reply,
I checked your idea, but all recordsets are probably closed.
Our problem is locking the records by the recordset.Open command. We have reduced the problem by just selecting the first record.
What I try to make is an function to add records to a database with an autonummer ID field. The ID the database gives to the new record must be returned (this way the business-object knows where he can find the record he just saved).
I have added the code below, so you will see what I'm doing.
Public Function AddNew(iSampleType As Integer, sngQn As Single) As Long
Dim conSample As ADOdB.Connection
Dim rsSample As ADOdB.Recordset
Dim ObjContext As ObjectContext
Dim lngCountError As Long
Dim lngRecordSampleID As Long
Set conSample = New ADOdB.Connection
Set rsSample = New ADOdB.Recordset
Set ObjContext = GetObjectContext
lngCountError = 0
'Prepare connection
conSample.Provider = mPROVIDER
conSample.CONNECTIONSTRING = mCONNECTIONSTRING
conSample.Open
rsSample.CursorLocation = adUseClient
rsSample.Open "SELECT TOP 1 * FROM Samples", conSample, adOpenForwardOnly, adLockOptimistic, adCmdText
rsSample.AddNew
rsSample![sampletype] = iSampleType
rsSample![qn] = sngQn
rsSample.Update
lngRecordSampleID = rsSample![SampleID]
AddNew = lngRecordSampleID
rsSample.Close
ObjContext.SetComplete
SetToNothing:
Set rsSample = Nothing
Set conSample = Nothing
End Function
Thanks Tom, I will test your idea
Thanks Tom,
Because my database is pretty big (at this moment 300.000 records, just for testing), the WHERE statement could be slower.
I will test your idea, it will be a better solution than just trapping the deadlock error and trying again.
THANKS!
Fedor