Jason,

You did read correctly. I am trying to do this without opening a recordset at all. Here are the problems with using the Insert statement that I haven't overcome yet:

1. My orginating data does come from a recordset - either an Access 97 table in one database or an Access 2000 table in another database - so the statement would look like: INSERT INTO tblMyTable (Field1, Field2) VALUES(rsData!Field1, rsData!Field2) with all the correct ticks in place to make it look like a real SQL statement. What I am now encountering is the vendor changing the database - so everytime it changes, I will have to change the code as well as update the database - I don't "own" two of the databases.

2. Insert doesn't seem to work if one of the fields is an autonumber field. I always get an error about mismatched type. But I do want to preserve the autonumber field and bring it over. I may be able to overcome this by changeing the datatype to a long integer since the autonumber is more of a feature the vendor needs and not one I need.

Want I really was looking for is advice on the easiest way to copy all of the data from one database into another in VB when the datasource is sometimes Access 97 and sometimes Access 2000 and the data store is always Access 2000 now, but could change shortly to MSDE or SQL Server. I need to do the replication so that I can work with the data and not conflict with the work happening in the data source databases.

Hope this all makes some sense.

Thanks,



>Using the SELECT * FROM tblMyTable WHERE 0=1 does work and I often use it. However I read the subject as being that you don't want to use a recordset at all. You can accomplish this with only the Connection object by using:
>
>DIM CN As ADODB.Connection
>
>Set CN = New ADODB.Connection
>
>'Note: Use you own conenction string info here
>CN.ConnectionString = "Provider=SQLOLEDB.1;" & _
>"Persist Security Info=False;User ID=" & UID & ";" & _
>"Initial Catalog=" & DBName & ";Data Source=" & SQLName & ";" & _
>"Password=" & PWD
>
>CN.Open
>CN.Execute ("INSERT INTO tblMyTable (Field1, Field2) VALUES('a','b')")
>
>Set CN = Nothing
>CN.Close