Dynamially modify Table Name in a query in VBA

[Guest]

I need to dynamially change the name of the table in a generalized Query with a name supplied by a source external to the Query.... can anyone help??

 

[John Vinson]

I need to dynamially change the name of the table in a generalized Query with a name supplied by a source external to the Query.... can anyone help??

The only way to do this is to build the Query SQL in code. Table names
cannot be parameterized.

If you have multiple identically structured tables, you may want to
consider the structure of your database - this is very rarely a good
idea!

 

[John Vinson]

Thanks John, I came to the same conclusion over night after I had time to think about the alternatives and the complexity of the first solution.

Very good move... one that does not come naturally especially to
people familiar with Excel, but it's clearly the way to go!

I can easily combine the multiple tables and make a simple column unique. If anyone has a solutiion to the "column limits" restriction in sub-form datasheets I'd like to know.

You may want to repost this as a separate thread - I'm leaving on
vacation very shortly, and I'm not at all sure what "column limits
restriction" you mean! Any Table is limited to 255 fields (an absurdly
wide table IMHO); and I tend to avoid datasheets entirely, finding
that Continuous Forms allow more control and flexibility.

If you could post this as a new question, with some more explanation
of the "limits" problem, I'm sure one of the other volunteers will be
able to help!

 

[david epsom dot com dot au]

If the table names are fixed, you may be able to create a union query from
the tables.
If you create a 'source' field, then you can use a parameter to select which
records
(which tables) you want to see.

(david)

Thanks John, I came to the same conclusion over night after I had time to

think about the alternatives and the complexity of the first solution. I
can easily combine the multiple tables and make a simple column unique. If
anyone has a solutiion to the "column limits" restriction in sub-form
datasheets I'd like to know.

Sorry about the spelling...Dynamically is much more correct....

Query with a name supplied by a source external to the Query.... can anyone
help??