Copy a record in access using ADO and an SQL string

I am trying to copy a record held within an access database and create a new one from it using unbound data with ADO. How can I do this using a SQL string?

Let's say we have two tables, Customers and Prospects which have identical field lists. Instead of working
with two tables we can combine them into a single table with a Type field that identifies the type of
person as a customer or a prospect. Here's the SQL statement:

INSERT INTO Customers (CustID, CustType, CustName, CustPhone)
SELECT ProspectID, "Prospect" As CustType, ProspectName, ProspectPhone
FROM Prospects;

Note: The Visual Basic help file topic for the INSERT INTO statement incorrectly states that if the table you
are copying from has a counter field that you should not include the counter in the query. The fact is that
you can include the counter field if the values don't violate the primary key in the destination table.


Iouri Boutchkine
[email protected]