Table Name for Append Query 1

[TeddyTerrier]

I need to append some data to about 1500 different tables in Access. I would like to append the data to the different tables without having to specify the destination table for each table. I have a field in the original table (table that I am getting the appending data from) that has the different table names in it. What I did was this:

Transfer is the name of the table that the original data is coming from


1. Declared a public variable as a string: Public gTableName As String

2. Assigned a value to gTableName: gTableName = [tbridgeno] (tbridgeno is the field name in the original table)
(I checked in the watch window to see if the value of gTableName would change when scrolling through the table and it does)

3. Run a SQL: DoCmd.RunSQL “Insert Into [gTableName] (Date_of_Survey) Select Transfer.Inspdate From Transfer",-1


When I run this code I get an error message: “Table gTableName not found”
My variable gTableName is not being read as a variable but as if I was looking for the table “gTableName”

Do you know of a way to do this?

Thank you,

Primm Scott

 

[glalsop]

Hi Teddy

Try this:

DoCmd.RunSQL "INSERT INTO " & gTableName & "(Date_of_Survey) Select Transfer.Inspdate From Transfer",-1

This should make Access read your variable name as a table name.

-Gary

 

[TeddyTerrier]

Thank you very much Gary. I just tried your suggestion and it worked perfectly.

Primm