APPEND QUERY USING A RECORDSET

[rdemyan via AccessMonster.com]

I know how to do the following:

strSQLAPPEND = "INSERT INTO [TableA] (FIELD1, FIELD2, FIELD3) " & _
"SELECT '" & rst.Fields(0) & "', '" & rst.Fields(1) & "', '" & rst.Fields(2)
& "'"

But what if the recordset rst has all of the fields in TableA and I don't
want to specify each field (like using an * instead).

I want to do something like:

strSQLAPPEND = "INSERT INTO [TableA] " & _
"SELECT '" & rst &

The idea would be that the entire recordset would be inserted. I want to
keep this generic because I'm looping through all of the backend tables and
am comparing them with a snapshot of the backend taken at the point my app
was opened. Then when I find an existing row in a table where a value in any
field has changed I want to write that row to another table using an Append
query like what I have above. Everything except this is working just fine.
This is the last piece that I need to get this whole thing working.

Thanks.

Thanks.

 

[Ken Snell \(MVP\)]

Because the data are in the recordset, you must specify the individual field
values from the recordset in your SQL string; however, you can forgo the
list of fields that are in TableA so long as those fields are in the same
order as the order in which you list the recordset's fields in the statement
and so long as the number of fields in TableA matches the number of fields
in the recordset.

strSQLAPPEND = "INSERT INTO [TableA] " & _
"SELECT '" & rst.Fields(0) & "', '" & rst.Fields(1) & "', '" & rst.Fields(2)
& "'"

To avoid having to list the field values from the recordset in your SQL
statement, you'd need to save the recordset into a table or save its SQL
statement into a query. Then you could reference the query or table using
the * syntax that you wish to use (instead of listing the recordset's
fields' values).