Query reference to table

G

Guest

I don't know if this is the best answer but it will work,
assuming that you know VBA or are willing to learn...

So long as your query isn't too big, you can create a form,
like you described. The form would have a text box for the
user to input the table name and a button that would
execute the change. You can then write code in VBA that
builds a string with the proper SQL statement. This can be
done by taking the SQL from the query itself while looking
at it in design, SQL view. Assign this to a string
variable but substitute in the user inputed table name.
Next, you need to set the query's SQL statement using code.


So, "SELECT first_name, last_name, phone FROM customer;"
would then become

sub UpdateTableName (NewTableName as string)

Dim db as database
dim qry as querydef
Dim strSQL as string

strSQL = "SELECT first_name, last_name, phone FROM " &
NewTableName & ";"
set db = currentdb
set qry = db.QueryDefs("YourQueryNameHere")
qry.sql = strSQL
db.close

end sub

In order to work with database variables in VBA, you need
to make sure that the reference to utility.mda is activated.

If all of this is new to you and you are interested in
begining to experiment with VBA, I would recommend a book:
Beginning Access 2002 VBA (Programmer to Programmer)
by Robert Smith, Dave Sussman, Ian Blackburn, John Colby,
Mark Horner, Martin Reid, Paul Turley, Helmut Watson. I
have the previous version of this book (Begining Access
2000 VBA) and it has been very helpful. They have an
example in that book about how to change the underlying SQL
for a query that could help you.

Good luck!

Andrea
 
Top