DAO Recordset vs. CurrentDb.Execute strSQL
Inserting data into a table, what is the better method?
1.
2.
1.
Dim strSQL As String
Dim strInput As String
strInput = "New data"
strSQL = "INSERT INTO target ( field ) VALUES ( " & strInput & ")"
CurentDb.Execute strSQL, dbFailOnerror2.
Dim rs As DAO.Recordset
Dim strInput As String
strInput = "New data"
Set rs = CurrentDb.OpenRecordset("target")
rs.AddNew
rs!field = strInput
rs.UpdateASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
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 SubSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great! Thanks to you all for your input.
you're welcome ~ happy to help
/gustav