Set Append Query target file with VBA?

[Guest]

I have an append query that I would like to be targeted towards
different MDB files, depending on the value of a list box in my form.
How can I use VBA to set the target file for the Append Query?

Nathan

 

[Michael Keating]

Hi,

Here's one possible solution.

'Append queries' are ones where the SQL string start "Insert Into
TableName", so all you need to do is copy the original SQL from your query,
remove the "Insert Into TableName" from the start of the string and in the
OnClick event for the combo have something like:
(using DAO)

Dim db as DAO.Database
Dim strOriginalSQL As String

strOriginalSQL = "Whatever is left of the SQL"
Set db = CurrentDB
db.QueryDefs("MyQuery").SQL = "Insert Into " & Me.Combo.Text &
strOriginalSQL
Set db = Nothing

This will have an impact on the speed of execution of the query, but if it's
not handling a large amount of data that should be ok.

HTH

MFK.

"NoSpamTakeSquareRootOfNumber"