sql query from 2 database files

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!

ashraf_tAuthor Commented:
sorry but i want more information , for example how to like the Table2 in file2.mdb with file1.mdb , Thanx
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."
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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!

ashraf_tAuthor Commented:
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 !!
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.
... :-(
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/


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.