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 & " ', " & _
