Changing the properties of an Append Query in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible in an Append Query, to change the table that is being
Appended, using VBA? Can someone give me a quick idea of how to program that
snippet of code? Thanks!
 
There are more than one way to do this. Here is a simple solution:

Dim strSQL as String
Dim dbf as Database
Dim tdfs as TableDefs
Dim tdf as TableDef

'Set up object variables
Set dbf = CurrentDb
Set tdfs = dbf.TableDefs
Set tdf = tdfs("MyQueryName")

'Get the SQL string for the query
strSQL = tdf.SQL
'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Runs the append query with the selected table name
dbf.Execute(strSQL)

'Note, we don't change the name in the original saved query so we don't know
what it is next time we need it. The Execute method above is the same as
running the query any other way and is about 5 times faster than then RunSql
method.

'Destroy object variables
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing
 
Back
Top