Need good example of VBA parameter pass to executed append query

[G Lykos]

Greetings! Have an append query to a master compilation file that will be
run against a series of linked tables.

Individual queries would differ only by the input table, and by two
calculated text identification fields added along with the linked table
fields per record.

It would seem that a preferable approach would be to use a common query and
run it from a macro with a list of the source table names with associated
constants (or even, I suppose, put the three parameters into a feeder table
and read it into the macro, although I would be happy to just hard-code the
parameters in the macro - they won't change).

A referral to a good example of such done in VBA code would be much
appreciated.

Thanks!
George

 

[G Lykos]

P.S. Access 2003. Thanks again!

 

[John Nurick]

Hi George,

You can't use query parameters to pass table names into a query the way
you can field values. So I'd tackle this by writing VBA code to build a
string containing the SQL statement for the append query and then
execute it.

Suppose the basic query is this:

INSERT INTO MyTable SELECT Field1, Field2, "XXX" AS ID1,
"YYY" AS ID2 FROM LinkedTable;

I'd do something like this

Dim strSQL As String
Dim strSourceTable As String
Dim strID1 As String 'first identification field
Dim strID2 As String
Dim dbD As DAO.Database

Set dbD = CurrentDB()

'for each linked table in your list
'(start of loop)
strSourceTable = 'name of linked table
strID1 = 'whatever
strID2 = 'whatever

strSQL = "INSERT INTO MyTable SELECT Field1, Field2, " _
& "'" & strID1 & "' AS ID1, '" & strID2 & "' AS ID2 " _
& "FROM " & strSourceTable & ";"
dbD.Execute strSQL, dbFailOnError
'end of loop

 

[G Lykos]

John, thanks for the ideas. FYI, I ended up creating a query configuration
table with the constants, including a memo field for the SQL code, for each
entry, then used a Replace with the constants to adjust each SQL code
appropriately as a one-time setup exercise. The main process then reads
that configuration file as a work list, pulling in the constants and SQL
code to process the dozen input tables into a master table.

A last issue to deal with is creating an automated process to refresh the
interposing local image tables I had to create (manually) due to issues with
field type-casting of the linked Excel input files. Any thoughts on a
clean, simple way to do so, preserving the desired field type-casting?

Thanks,
George