Adding a Record to a table, execution time

With all else equal, same table, same network, same number of users, same machine, same operating system etc..

Access 2003 front end and Access 2003 backend, linked tables, backend is on a server, front end is on each clients local machine.  

All values to be inserted in the new record are in temp variables, not being pulled from another table or query.

I'm trying to improve the efficiency of a client application.

I have used  both of the below techniques to add a record to a table.  In your experience, are either of these two techniques of adding a record to a table is better?  Or perhaps they are the same.
I realize the two examples refer to different tables, I am interested in processing time, reducing record locking etc..  between the two formats if they were being performed on the same table with all else being the same.
'
CurrentDb.Execute " insert into tblErrorLog " & _
              "( [ErrNum], [ErrText], [ErrUser], [ErrDateTime]  )  " & _
  "   values(" & passedErrNum & _
          ", " & Chr(34) & wkFullErrDesc & Chr(34) & _
          ", " & Chr(34) & wkErrUser & Chr(34) & _
          ", " & Chr(35) & wkErrDateTime & Chr(35) & _
          ")", dbFailOnError

Open in new window


or

'
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblBatchPaymentLog", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
rs.AddNew
    '
    rs!NumberOfPayments = wkNumPaymentsCreated
    rs!AmountOfPayments = wkTotAmountPaymentsCreated
    rs!DateTimeOfBatch = thisBatchDateTime
    rs!UserCreating = GimmeUserName
    rs!BatchNum = thisBatchNumber
    '
rs.Update
'
rs.Close
Set rs = Nothing
'

Open in new window


Is there any advantage to either technique?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
In general, I don't think either method is more efficient than the other for inserting a single record.  My guess is that the latter method would be quicker and more efficient for inserting multiple records.  However, I have found, that in a multi-user environment, if you need to get the PK of the record being added, the latter method is more accurate.

It is possible (although unlikely) that two users might insert a new record at the 'same time'.  Using the first method, the common method for getting the PK of that newly inserted record would be:

lngPK = DMAX("ID", "yourTable")

but if the two records are inserted at the 'same time', it may be possible that both users would end up with the same value for lngPK.  With the latter technique, you can use the recordsets bookmark property to set the pointer at the most recently inserted record:

rs.Bookmark = rs.LastModified
lngPK = rs!ID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You forgot the third method - which probably is the fastest - using DAO:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select From tblBatchPaymentLog", , dbAppendOnly)

rs.AddNew
    rs!NumberOfPayments = wkNumPaymentsCreated
    rs!AmountOfPayments = wkTotAmountPaymentsCreated
    rs!DateTimeOfBatch = thisBatchDateTime
    rs!UserCreating = GimmeUserName
    rs!BatchNum = thisBatchNumber
rs.Update
rs.Close

Set rs = Nothing

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
@Gustav,

I missed that altogether.  I just assumed the recordset method above was DAO rather than ADO.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

mlcktmguyAuthor Commented:
Are you suggesting that DAO would be quicker than both of my examples?
Gustav BrockCIOCommented:
Don't know, but often DAO is faster.

Why don't you check it out? It would only take you a few minutes ...

/gustav
John TsioumprisSoftware & Systems EngineerCommented:
As an semi-extra option there is also the "transaction" mechanism if you do massive operations
Check this here
PatHartmanCommented:
I seriously doubt that you would be able to come up with an accurate timing difference for inserting a single record.  If you want to test the three methods,  create a loop to insert 1,000 records.  You will see that the execute method is by far the slowest.  Of the other two (make sure the loop is INSIDE the set rs.xxx because there is no reason to open/close the recordset for each record. Open it once, insert the 1,000 records and then close it  - although you might want to include the overhead of opening/closing for purposes of this test. ), DAO should be faster than ADO if the BE is Jet/ACE.  If the BE is something else, ADO might win out.  

Once you get a number for 1,000, you can figure out the average for 1 and make your decision based on that.
mlcktmguyAuthor Commented:
Based on the answers, I've left it as ADO for now.  Haven't had time to set up a comparison test yet.
Gustav BrockCIOCommented:
Hmm ... it would have been nothing more than a copy-n-paste ...

/gustav
mlcktmguyAuthor Commented:
Still trying to get time
mlcktmguyAuthor Commented:
Thanks, all suggestions were helpful.  I couldn't determine any difference in the techniques for a single record.  When I tested with a batc of records, the biggest improvement was achieved using the transaction processing technique.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.