query many database

[sms23]

Hello,

I have 10 databases . ALL identical and used for different vendors.
All have same table structure ( invoice, price, etc , etc tables)
I want to run a query where I type in the invoice number and it should query all the database and display the appropriate information that I need.

Would appreciate any ideas or suggestions. Thanks


ALSO databases are all on one server.I would need to connect using ODBC

 

[Pat Hartman]

You have a design problem. You should have one database rather than 10. To search all 10 copies of a table at once, you'll need to have all the tables linked to a SINGLE database. Then you'll have to make union queries and search the queries rather than the underlying tables. If the tables are large, this could be slow. Your best bet is to merge the databases into one database.

 

[sms23]

Sorry but that is not an option. Database are all on one server. The are for different clients. ALL in MS access. I can move them to SQL server.

 

[Pat Hartman]

Moving them to SQL server doesn't solve the problem. You will have to take the union query route.