How to get a table from two databases?

Question

Using Access 2003

I want to get a table value from the two databases

Database – 1

Emp_Table

Database – 2

Customer_Table

Select * from Database-1.Emp_Table, Database-2.Customer_Table

The above query is showing error in the Access. I tried a Join query also, it showing error.

Can any one to solve this problem?

Need Query Help.

Answer 1

First, assuming the query is running in Database 1, you will need to create a "linked table" to link to Database 2's table in Database 1.

Once you do that, you can write it simply as:

 Select * from Emp_Table, Customer_Table

Since you are "in" database 1, you won't have to qualify Emp_Table, and since you have Database 2's Customer_Table linked in, you won't have to qualify it either.

Answer 2

I don't have Access 2003, but in Access 2007 you can do this:

• Click on the "External Data" tab.
• Click on the "Access" icon.
• Choose the location of your second Access database.
• Select "Link to a data source by creating a linked table".

This should add the tables in your second database linked in your original one. You can then write queries to query data from either one or both like you normally would. I'm sure the same functionality is available in Access 2003, just a slighty different visual route to achieve the same thing.

Answer 3

Try using square brackets -

SELECT * FROM [Database-1].[Emp_Table], [Database-2].[Customer_Table]

Or, try this.

Answer 4

Is there some relationship between the tables, or do you want just a dump of the whole table? Also, post the join you tried and the error you got, it would help in the troubleshooting...

If you want all records from both tables, you would need to use a UNION query like this:

Select * from Database-1.Emp_Table;
UNION Select * from Database-2.Customer_Table;

This assumes that there are the same number of columns in both tables. If not change the * to the specific columns you want to list from each table.