sql query from 2 database files

Posted on 2003-02-22
Medium Priority
Last Modified: 2010-04-07
i want to make sql query depending on 3 tables in 3 different files let us say that Table1 located in file1.mdb and Table2 located in file2.mdb and Table3 located in file3.mdb

when i tried to use a statment like this :
select Table1.Code , Table2.Ammount , Table3.Name From Table1 , Table2 , Table3 where Table1.Code = Table2.Code and Table1.Code = Table3.Code

i got error , anyone can help , Thanx
Question by:ashraf_t
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2

Expert Comment

ID: 7998692
A simple solution with Access:

You can make a new database, in which you LINK the three tables you need.
To link a table:
Database - New - link table (not import!)

Then you create a query with the Query wizard, defining every details about relationship between tables.

About the error in your query: maybe type of unique identifier are not the same ?

Hope this helps!


Author Comment

ID: 7998733
sorry but i want more information , for example how to like the Table2 in file2.mdb with file1.mdb , Thanx

Expert Comment

ID: 8000722
Rquaglia's suggestion is the correct way to go.  Select one of the MDB files to be the "master", we'll use file1.mdb for example.  Open file1.mdb and create links for Table2 from file2.mdb and Table3.mdb.  File1.mdb will contain Table1 and references to Table2 and Table3.  From File1.mdb you will be able to execute your query.

If you need further explanation or help with the mouse-clicks to create the links, use Access' online help and search for "Linked Tables."
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 8002309
Hello ashraf_t!

To link differents tables in one DB try this (it's simpler doing it than explaining it!)

- Create a new database
now you have to add table:instead of design each table, you can import a table from other database. In Access97 (but it's quite the same in next Access editions) you have to press "New button", then "Link Table" (not "Import"),
then you choose the database (I.E. file2.mdb) where are the table you need,
then you choose the table (i.e. Table2) you need,
then press OK.

note: I don't have English edition (Italian one!), so command buttons may differ slightly, sorry!

Repeat this for every table you need to add.

Now you have a database with three different tables, that you can use inside a query. In the query, you need to define relation between tables, I.E. drag table2.code on table1.code and drag table 3.code on table1.code.

I hope this helps!


Author Comment

ID: 8005105
oh both of you talking about linking tables inside the access application , sorry i want to make it inside th visualbasic , what statement i must write !!

Expert Comment

ID: 8010443
Ooh, ick... :(

I believe you'll have to execute three seperate queries, one for each table, and merge the result sets in code.

If you could create the linked tables in Access you would save yourself a lot of trouble.

Accepted Solution

rquaglia earned 75 total points
ID: 8010645
... :-(
I agree with Hilapdatus, the better way is to link tables with Access!

Just an hint:
In VB6, use ADO through DataEnvironment (in View Environment under tools menu), and patch your MDAC with latest Service Pack from: http://www.microsoft.com/data/


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question