Variables in SQL-TJJ

A

ambushsinger

I'm trying to append data from one table to another, however the table names
are constantly changing. Is there a way to write, in VBA, some SQL string to
accomplish this?
 
J

John Spencer

Not really. Unless you have some way of determining the new name(s) and
introducing them into an SQL string that you will build in the VBA routine.

Do the field names change, too?

If (BIG IF) the only thing that changes is the table name(s) then you can
build your query string using aliases and only need to enter the table names
once in the query string

Dim strTargetTable as string, strSource as String

'Some code here to get the values for strTargetTable and strSource

StrSQL = "INSERT INTO {" & strTargetTable & "] as T " & _
"(PrimaryKeyField, FieldA, FieldB, FieldC)" & _
" SELECT S.PrimaryKeyField, S.Fieldzz, S.Fieldxy, S.FieldJC " & _
" FROM [" & strSource & "] as S LEFT JOIN [strTargetTable] as Tx"
" ON S.PrimaryKeyField = Tx.PrimaryKeyField"
" WHERE Tx.PrimaryKeyField is Null"

Currentdb().Execute strSQL, dbFailOnError

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top