Query reference to table

[glind]

I have a query that references a table that is imported daily from a
AS-400. On each project, this table name is different

Currently for each project we just go into each query and change th
source table name to match the imported table name. However, this i
very time consuming

Is it possible to just enter the desired table name once into a for
text box or a separate table field, and then just use a variable fo
this table name in the queries

 

[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

 

[Larry Daugherty]

Yes. create a new table for the source table names. On the form
where you're about to run the query, create a combobox based on that
table. Have your query refer back to this combobox on this form for
its parameter.

HTH