As a hypothetical example here's some code which would append all rows where
the FirstName column is "John" from a table Contacts to a table Johns:
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
strSQL = "INSERT INTO Johns([First Name], [Last Name]) "& _
"SELECT FirstName, LastName "& _
"FROM Contacts "& _
"WHERE FirstName = ""John"""
cmd.CommandText = strSQL
cmd.Execute
I've deliberately used column names with spaces in Johns and without in
Contacts. As you see for those with spaces (or other special characters) you
wrap the column name in square brackets in the SQL statement. The same
applies to table names. If in doubt put the brackets around all table and
column names.
Bear in mind that if your 4 tables are related and referential integrity is
enforced, then its important that the 'append' queries like that above are
executed in the correct order. Rows must be inserted into a referenced table
before inserting rows into a referencing table, e.g. it would be necessary
to insert rows into a Customers table before rows which reference those
customers can be inserted into an Orders table.
Ken Sheridan
Stafford, England
I needed to create a routine to append 4 linked tables with 4-30
records ... Using 2007.
I have written some VB code and am looking for some basic starting code.
The program already exists and I am modifying it to allow for adding new
records to include appending some records from a existing tables.
One problem may be that some of the field names have spaces in their names.
Any recommendations would be appreciated!
Thanks,
Bob