Which Code run faster in MS Access SQL or DAO

[Danny Dy]

Hi To All,

I always write My VBA code in SQL(see Example).

Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim stSQL as String
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
stSQL = "INSERT INTO tblAE (AEName )" _
& " SELECT " & NewData & " AS AEName;”
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL stSQL
DoCmd.SetWarnings True

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If
End Sub

When I go to website http://www.mvps.org/access/forms/frm0015.htm I see
the code of Dev Ashish

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the
current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to
re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

Is this code
1. Run faster compare to the my Example Code
2. Use Less Memory
3. Is there any other reasons.

Thanks,

Danny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

In general, executing an action query is faster than looping through records
in DAO. For a single update as in your example, there is no practical
difference.

One problem with RunSQL is that you do not know if the action query
succeeded or not. It would therefore be better to use:
dbEngine(0)(0).Execute strSQL, dbFailOnError
assuming that your query is not referring to something that needs the
Expression Service to resolve it.

If Dev were writing that code today, I suspect he would use the Execute
method. If you are interested in saving memory with the DAO approach, you
might use:
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset, dbAppendOnly)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Danny Dy" <ha********@yahoo.com> wrote in message
news:40**********************@news.newsgroups.ws.. .

Hi To All,

I always write My VBA code in SQL(see Example).

Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim stSQL as String
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
stSQL = "INSERT INTO tblAE (AEName )" _
& " SELECT " & NewData & " AS AEName;"
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL stSQL
DoCmd.SetWarnings True

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If
End Sub

When I go to website http://www.mvps.org/access/forms/frm0015.htm I see
the code of Dev Ashish

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the
current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to
re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

Is this code
1. Run faster compare to the my Example Code
2. Use Less Memory
3. Is there any other reasons.

Thanks,

Danny