Queries

[Steven]

Can I create a query in the query tab and use a variable
for the name of the table? I can have 30 tables and do
not want to create a query for each one. I want one query
and pass variables.

Example:

Dim CurrentBatch, CurrentBatchImport as String
CurrentBatch = "Batch05"
CurrentBatchImport = "Batch05Import"

I want to use the CurrentBatch and CurrentBatchImport
variables for my tables in the SQL View on the Query
Design. In this example I would get
normally with actual table names in the SQL View on the
Query Design:

INSERT INTO Batch05
SELECT Batch05Import.*
FROM Batch05Import
WITH OWNERACCESS OPTION;

.....How can I use CurrentBatch in place of the Batch05 and
CurrentBatchImport in place of the Batch05Import in this
SQL VIEW so I can pass the name of different files...I
cannot get it to work like a normal string or
concatenation in the SQL View in the Query Tab Design.
Can this be done? I have used in the past
DoCmd.RunSQL "sqlString" where the "sqlString" was the
correct INSERT string giving you total control over the
table names by using variables.

But I cannot use the DoCmd.RunSQL in this case. It has to
be done with a Query created in the query tab design so I
can set Permissions as Owners in the query.

Thank you for your help.

Steven.

 

[Jeff Boyce]

Steven

If you have 30 tables that are similar enough that you could use "the same"
query on each, just changing the table name, your database may be in need of
further normalization. Embedding "meaning" in the table names, and
duplicating table structure, may be what you need to do in a spreadsheet,
but Access is a relational database.

For instance, if you created a single table, with the same fields you have
now in all 30 tables, plus one more field that held whatever info you have
embedded in table names (say, for example, Batch#), you could have ALL your
data in one table, and run a single query, grouped by Batch#.

 

[Steven]

That was a good answer that I have never considered. This
would create a major change in my database but I think it
is something I need to seriously consider. Thank you for
your advise.

 

[Jeff Boyce]

Steven

Consider posting a description of your table structure to the tablesdbdesign
'group with a request for additional ideas, if you get stuck.