>>>>Check the syntax of «Insert Into». You can do this using a single SQL query.
>>>
>>>Ok, here's a dumb question. Look it up where? MSDN only shows the T-SQL Insert, do I use that, or is there a specific help file for the JET OLE-DB provider?
>>>
>>>IAC, INSERT INTO requires the table to exist, correct?
>>
>>You can use SELECT INTO if the table does not exist.
>
>Eric,
>
>Doesn't this fail if one of the fields is an MS Access autonumber field? That is the experience I had, of course I could always have programmed it wrong.
>
>Also, will this work if the source table is in an MSAccess 97 database and the target table is in an MSAccess 2000 database?
>
>I found I had to do it the "old fashioned" way of reading each record in the source, writing it to the target, and then deleting it from the source when the there was not an error.
>
>Any suggestions for improving this are greatly appreciated.
>
>Thanks,

Doesn't the copyobject function work for you? (See my reply to Roi)

Although it wouldn't work from A97 to A2K.