Changing the properties of an Append Query in VBA

[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!

 

[Guest]

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

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to do this. All
the queries in the samples are Select queries, but the process is the same.
The SQL is just different.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L