Variables in SQL-TJJ

  • Thread starter Thread starter ambushsinger
  • Start date Start date
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?
 
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
 
Back
Top