DAO Recordset vs. CurrentDb.Execute strSQL

Inserting data into a table, what is the better method?

1.
Dim strSQL As String
Dim strInput As String

    strInput = "New data"
    strSQL = "INSERT INTO target ( field ) VALUES ( " & strInput & ")"
    CurentDb.Execute strSQL, dbFailOnerror

Open in new window


2.
Dim rs As DAO.Recordset
Dim strInput As String

    strInput = "New data"
    Set rs = CurrentDb.OpenRecordset("target")
    rs.AddNew
    rs!field = strInput
    rs.Update

Open in new window

LVL 28
MacroShadowAsked:
Who is Participating?
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
hi,

if you have multiple records, inserting in a batch using SQL gives better performance. For just one record, I am not sure there would be a big difference, but I'd be inclined to guess that SQL might perform better there as well.

have an awesome day,
crystal
0
 
PatHartmanConnect With a Mentor Commented:
It is unlikely you would ever be able to measure the difference between 1 and 2 if you were inserting only a single record.  However, inside a loop, only the .addnew - .update parts of method 2 would execute for each record.  You would not open and close the recordset each time.  Given that, #2 would be far superior for multiple records.  The process of instantiating the object adds overhead.  Method 1 would force that for each row but method 2 would not.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
There are ways to use ADO to insert multiple rows in the same call:

strSQL = "INSERT INTO target ( field ) VALUES ( " & strInput & ") , (" & secondvalues & ") , (" & thirdvalues & ")"
YourConnection.Execute strSQL

It's an oddball way of doing it, but it will avoid the overhead Pat is referring to. Still, for readability I'd use the .AddNew process for multiple records.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<Inserting data into a table, what is the better method?>>

 It depends on:

1. Where the table is

2. Single or multiple records.

If the table is in something other than JET, then #1 will out perform #2.   For JET, #1 still probably has the edge for a single record, but for multiples, DAO would be the fastest way to talk to the database.

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
I would run tests for both cases for multiple records ...
0
 
Gustav BrockConnect With a Mentor CIOCommented:
#2. DAO is native to Access VBA.

It is way faster, indeed in loops.

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<DAO is native to Access VBA.>>

 um...more correctly, it makes calls directly to JET DLLs

 You can use DAO in other languages as well after all.

Jim.
1
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Academically ... from a due diligence standpoint ... I would run a test ... say on 100K records, SQL insert vs VBA/DAO code ...
0
 
Gustav BrockCIOCommented:
Been there, done that, Joe. Made me a guru magician in a client's view when fixing up a VB application flooded with SQL calls.

/gustav
0
 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
Overall, queries are faster.

But that's missing an important factor:
The complexity of the algorithm manipulating data.

Personally, unless perfomances are really critical, I'll never sacrifice maintainability over performances.
Better have an application run slower than struggling days and days trying to fix a monstruous query.
1
 
MacroShadowAuthor Commented:
Thanks guys for your input.
Testing using the following code it would seem that using a DAO recordset is far more efficient than a query (54.7586 sec. vs. 704.5164 sec. for 100,000 iterations).
Option Explicit
 '
 ' COPYRIGHT © DECISION MODELS LIMITED 2006. All rights reserved
 ' May be redistributed for free but
 ' may not be sold without the author's explicit permission.
 '
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
 
Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\0"
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002
Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Declare PtrSafe Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long
 
Function MicroTimer() As Double
     '
     ' returns seconds
     '
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
     '
    MicroTimer = 0
    If cyFrequency = 0 Then getFrequency cyFrequency ' get ticks/sec
    getTickCount cyTicks1 ' get ticks
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency ' calc seconds
     
End Function
 
 'Calling macro
Sub Test()
    Dim i As Long, x As Long
    Dim Tim As Double
    Dim Result1 As Double, Result2 As Double
    Dim strSQL As String
    Dim rs As DAO.Recordset
       
    Tim = MicroTimer
    Set rs = CurrentDb.OpenRecordset("tblTest")
    For i = 1 To 100000
        rs.AddNew
        rs!SomeData = i
        rs.Update
        DoEvents
    Next
    Result1 = MicroTimer - Tim
     
    Tim = MicroTimer
    For x = 1 To 100000
        strSQL = "INSERT INTO tblTest ( SomeOtherData ) VALUES ( " & x & ")"
        CurrentDb.Execute strSQL, dbFailOnError
        DoEvents
    Next
    Result2 = MicroTimer - Tim
     
    Debug.Print "recordset: " & vbTab & Int(Result1) & vbCr & _
    "query: " & vbTab & Int(Result2)
End Sub

Open in new window

1
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
hi,

if you can find a way to batch it -- like connecting to a text file or wherever the data is coming from,  using SQL would be faster.  However, if you are going to add many records one a time, using a recordset will be better performance.

btw, here is a Stop Watch app that runs in Access:
http://www.rogersaccesslibrary.com/forum/stop-watch_topic612.html

have an awesome day,
crystal
1
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<it would seem that using a DAO recordset is far more efficient than a query>>

  Just be aware that you can't make a blank statement on that.  It would be fastest for a JET/ACE DB (and that's no surprise), but if the data was moved to SQL server or some other data store, it would be a different story.

  DAO makes native calls to the JET DLL's.   But if the data is else where, then it needs to go through a few more layers to get to the data source, where a SQL statement would take a slightly more direct route.

 For a JET/ACE DB it's:    DAO | JET | Database

 For a linked table, using DAO, it's:   DAO | JET | ODBC Manager | ODBC Driver | Server Software | Database

 But when you use SQL, the DAO layer is skipped and if the SQL can be a pass-through, most of the JET layer is bypassed as well.

 In the past, there was also the option of ODBCDirect, and then DAO almost always came out on top, but that was dropped from Access/JET years ago.

Jim.
1
 
MacroShadowAuthor Commented:
Great! Thanks to you all for your input.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.