[AccessD] Fastest - DAO or Sql

 *Well, there is no doubt about it in my mind now.  It is the opening of the
record set that takes up the time.   As has often been stated here by many
of the Listers.*
**
*If you can keep the record open, then the DAO.AddNew method takes 4 seconds
for 100,000 records and the .Execute(Sql) takes 2 min 47 seconds whereas if
you don't hold the record set open then the DAO.AddNew method takes over
nearly 10 minutes.  Clearly the difference is in opening the record set.  *
**
*So for me, the message is clear.  Open record set. Keep it open if it is
going to be used again.  Single records makes no difference as both methods
have to open the record set*
**
*Summary of Times:*
**
*SQL*
*'18 secs for 10,000
' 2min 47 secs for 100,000
DAO Open Record set*
*' 1 sec for 10,000
' 4 sec for 100,000*
*DAO Closed Record sets*
*' 18 secs for 10,000
' 9min 47 secs for 100,000
*
*Test Code below.*
** * *
** *Max*
*ps. Thanks for all those who provided feedback and AD for his examples.*
*pps. ADO not tested.*
**
**
*Option Compare Database
Option Explicit
Const conQty As Long = 10000
Const conDeleteAll As String = "Delete * from tblAppendTest"
Const conName As String = "abcdefghijklmnopqrstuvwxys"
Const conLong As Long = 123456789
Private sql As String
Private lngLoop As Long
Private datStart As Date, datEnd As Date, datLapsed As Date*
**
*Public Function pfRunTests()
  On Error Resume Next
  sql = "Drop table tblAppendTest"
  CurrentDb.Execute (sql)
  sql = "Create Table tblAppendTest (txtName Text, lngLong Long)"
  CurrentDb.Execute (sql)
  CurrentDb.Execute (conDeleteAll)
  Call pfAppendTestSql
  CurrentDb.Execute (conDeleteAll)
  Call pfAppendTestDAO
  CurrentDb.Execute (conDeleteAll)
  Call pfAppendTestDAO2
End Function
*
**
*Public Function pfAppendTestSql()
'18 secs for 10,000
' 2min 47 secs for 100,000
  datStart = Now()
  Debug.Print "SQL Insert Test"
  'Debug.Print Format(datStart, "hh:mm:ss")
  For lngLoop = 1 To conQty
    CurrentDb.Execute ("Insert into tblAppendTest (txtName,lngLong) values
('" & conName & "'," & conLong & ")")
  Next lngLoop
  datEnd = Now
  'Debug.Print Format(datEnd, "hh:mm:ss")
  Debug.Print Format(datEnd - datStart, "hh:mm:ss")
End Function
*
** **
*Public Function pfAppendTestDAO()
' 1 sec for 10,000
' 4 sec for 100,000*
**
*  Dim rst As DAO.Recordset
  Set rst = CurrentDb.OpenRecordset("Select * from tblAppendTest")
  datStart = Now()
  Debug.Print "DAO AddNew Test"
  'Debug.Print Format(datStart, "hh:mm:ss")
  For lngLoop = 1 To conQty
    rst.AddNew
    rst!txtname = conName
    rst!lnglong = conLong
    rst.Update
  Next lngLoop
  datEnd = Now
  'Debug.Print Format(datEnd, "hh:mm:ss")
  Debug.Print Format(datEnd - datStart, "hh:mm:ss")
  Set rst = Nothing
End Function
*
**
*Public Function pfAppendTestDAO2()
' 18 secs for 10,000
' 9min 47 secs for 100,000
  Dim rst As DAO.Recordset
  datStart = Now()
  Debug.Print "DAO AddNew Test2"
  'Debug.Print Format(datStart, "hh:mm:ss")
  For lngLoop = 1 To conQty
    Set rst = CurrentDb.OpenRecordset("Select * from tblAppendTest")
    rst.AddNew
    rst!txtname = conName
    rst!lnglong = conLong
    rst.Update
    Set rst = Nothing
  Next lngLoop
  datEnd = Now
  'Debug.Print Format(datEnd, "hh:mm:ss")
  Debug.Print Format(datEnd - datStart, "hh:mm:ss")
  Set rst = Nothing
End Function*

**