Query To Append To Multiple Tables

[Russie]

Hi.

I have a set of Access queries that extract data from SQL Server. These queries are using linked tables which i find faster than creating a connection object and reading in each line of the recordset into Access seperately.

My question:

Is there anyway to pass the 'table to be appended to' information to my query, I.e. there are 10 sets of debtors that have 10 different debtor tables. Could I use one query to append the debtor listing to 10 possible tables?

I know that I can use 'queryDef' to select any debtor listing from one query into a 'flushing' table, and then copy this to the pertinent debtor table, but I would like to pass the 'append to' table definition directly to the query and do away with the copying into the flushing table and then to the relevant debtor table.

A star as usual for a workable answer. 2 stars for any answer that includes a connection object and a bulk select into my access table, i.e. without defining field variables and 'reading' the recordset in.

Thanks for anything.

Russie.

 

[Norris68]

The easiest way would be to create a sub routine that took the name of your destination as a parameter. I assume that all the field definitions match and all the tables are attached.

e.g.

Public Sub AppendDebtor(TableName As String)
DoCmd.RunSQL "INSERT INTO " & TableName & " SELECT * FROM SourceTable"
End Sub