Example docmd.runsql insert using recordset

A

AJ

Does anyone have an example of a do.cmd.runsql insert using recordset fields?
I have a table that I am populating with a few fields from a recordset and
cannot seem to figure out how to do the insert.
Thanks.
 
D

Douglas J. Steele

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Field1, Field2, Field3 FROM MyTable"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)

Do Until rsCurr.EOF
strSQL = "INSERT INTO Table2 " & _
"(NumericField, TextField, DateField) " & _
"VALUES (" & rsCurr.Field1 & ", '" & rsCurr.Field2 & "', " & _
Format(rsCurr.Field3, "\#yyyy\-mm\-dd hh\:nn\:ss\#") & ")"
dbCurr.Execute strSQL, dbFailOnError
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


Note that I'm using the Execute method of the database object rather than
DoCmd.RunSQL, for two reasons. First, it eliminates the annoying "You are
about to insert ..." message, and second, by using the dbFailOnError
parameter, a trappable error will be raised if something goes wrong
executing the SQL statement. Note, too, that because I'm assuming that the
second field is a Text field, there are quotes around its value: exagerated
for clarity, that line of code is

"VALUES (" & rsCurr.Field1 & ", ' " & rsCurr.Field2 & " ', " & _
 
Top