Preferred method for SQL in VBA

Good day,

What is the best way to perform SQL statements in VBA? Through the
docmd.runSQL (for action queries) or the with db.Execute (where db is a
DAO.database object) and querydef objects? my database is an MDB file
and I doubt it will ever be upgraded to anything more.

Also, I have created a few SQL statements, but to do what I need to be
done I needed to Select Into a temporary table, then update another
table from that tempTable. Is there any way to use recordsets in SQL
statements, thus eliminating this creation and destruction of the
buffer table?

TIA

Tim

re: Preferred method for SQL in VBA

See:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

The article explains the difference between RunSQL and the Execute method.

If you need to examine records programmatically (i.e. not showing them to a
user), you can use OpenRecordset() with a SQL statement.

If you need to show them to a user, you can set the RecordSource of a form
or report (assuming the field names are constant, and you are just selecting
which records to show.) Or you can just set the Filter of the form, or the
WhereCondition of OpenReport.

--
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.

<tricard@gmail.comwrote in message
news:1156510931.553528.33640@75g2000cwc.googlegrou ps.com...

re: Preferred method for SQL in VBA

tricard@gmail.com wrote:RunSQL are used for action queries (Update/Delete/Append/Make-Table)

You cannot run SELECT queries using RunSQL

Move Records to Another Table

Sub DoArchive()
On Error GoTo Err_DoArchive
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.

'Step 1: Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

'Step 2: Execute the append.
strSql = "INSERT INTO MyArchiveTable ( MyField, AnotherField, Field3
) " & _
"IN ""C:\My Documents\MyArchive.mdb"" " & _
"SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField
= True);"
db.Execute strSql, dbFailOnError

'Step 3: Execute the delete.
strSql = "DELETE FROM MyTable WHERE (MyYesNoField = True);"
db.Execute strSql, dbFailOnError

'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

Exit_DoArchive:
'Step 5: Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub

Err_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.number
Resume Exit_DoArchive
End Sub

Does this help?