How do you attach a table from another Access database when....

[Collin]

The tables have the same name, for example my database saves it's self each fysical year so you have a bunch of databases like MyDB99, MyDb98 etc... the table names and structure for each is the same. I want to be able to query a table from each of the databases, let me call it MyTable, so MyDB99 and MyDb98 both have a table called MyTable. My solution was to use the SELECT INTO statement to create a dublicate of MyTable in MyDatabase98 called MyTable98 and then attache it into MyDb99 and then do my query, but this is slow and error prone , does anybody know a better way?<br>
Thanks

 

[bitbrain]

Hate to say it but I think you need to rethink the design. Databases & Tables should be implemented at a high level of abstraction. One database should contain all data (Archive if you need to). "Year" should be an attribute of MyTable, not part of the name.<br>
<br>
However, assuming that you must keep the current design. Attach the tables from the various db's to a single db. They will have unique names. Then, Create a UNION query that combines all the tables. It will look like this:<br>
<br>
SELECT * FROM MyTable95<br>
UNION<br>
SELECT * FROM MyTable96<br>
UNION...<br>
<br>
Then, run all of your queries against the UNION query rather than the tables.<br>
<br>
To create a UNION query in access: create a new query, close the Show Table window, from the toolbar select Query/SQL Specific/Union.<br>
<br>