SQL or RecordSet object?

[RBohannon]

Suppose I want to add a new record to a table. I could use SQL and
DoCmd.RunSQL:

'=====
Dim strSQL as string

' assume myVars are properly delineated for data types of myFields
strSQL = "INSERT INTO myTable(myField01, myField02, myField03) " & _
"VALUES(" & myVar01 & myVar02 & myVar03 & ");"

DoCmd.RunSQL strSQL
'=====

or I could declare Database and RecordSet objects:

'=====
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select * From myTable"
Set rs = db.OpenRecordset(strSQL)

With rs
.AddNew
![myField01] = myVar01
![myField02] = myVar02
![myField03] = myVar03
.Update
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
'=====

Is there any advantage to using one of these methods over the other?

Thanks for the insight.

Randy

[pietlinden]

Absolutely. How about SPEED?

Use a recordset. RunSQL is about the slowest thing in the world. If
you don't believe me, test it. I tried option 1 once and it was
incredibly slow and frustrating.

[Randy Harris]

<pi********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Absolutely. How about SPEED?

Use a recordset. RunSQL is about the slowest thing in the world. If
you don't believe me, test it. I tried option 1 once and it was
incredibly slow and frustrating.

Nonsense. If you are going to insert one record, RunSQL is as fast or
faster than opening a recordset. The recordset gives you an advantage only
if you are inserting multiple records. Personally, I find the RunSQL easier
to code.

[pietlinden]

Well, yeah, but I was assuming he was going to enter multiple records
that way. Been there, done it. Multiple RunSQLs in sequence is
obscenely slow.