Select From choice of tables

[merry_fay]

Hi,

I'm trying to set up an append query to select/append data from a whichever
table has been specified in a box on a form.
The column/field names will be identical in all these tables, so it's just
definig the table name in the From section of the SQL. Is there a bit of code
to do this?

Thanks
merry_fay

 

[Stefan Hoffmann]

hi Merry,

I'm trying to set up an append query to select/append data from a whichever
table has been specified in a box on a form.
The column/field names will be identical in all these tables, so it's just
definig the table name in the From section of the SQL. Is there a bit of code
to do this?

You need to manipulate the entire query SQL statement, e.g.:

Dim qd AS DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("yourQuery")
qd.Statement = "SELECT * FROM

;"



mfG
--> stefan <--

 

[Douglas J. Steele]

hi Merry,

I'm trying to set up an append query to select/append data from a
whichever table has been specified in a box on a form.
The column/field names will be identical in all these tables, so it's
just definig the table name in the From section of the SQL. Is there a
bit of code to do this?

You need to manipulate the entire query SQL statement, e.g.:

Dim qd AS DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("yourQuery")
qd.Statement = "SELECT * FROM

;"

I think Stefan meant to say something like:

Dim qd AS DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("yourQuery")
qd.SQL = "SELECT * FROM [" & Me!TextboxOnForm & "]"

merry_fay: realistically, this is an unusual request, and is usually
indicative of a poorly design database in which you have, say, one table for
each customer, rather than a single table containing the information for all
customers.

 

[S.Clark]

In VBA:

Function cmdRunQuery_OnClick()
'Assumptions:
' 1. There exists a button called cmdRunQuery
' 2. There exists a textbox called txtTable

dim strSQL As String

strSQL = "Select * FROM [" & txtTable & "] where ... "

docmd.runsql strSQL

End Function