Working with multiple tables from different databases

mhsueh001
06-03-2002, 02:59 PM
How does one design a select statement using three tables all on different databases?

One is an MS SQL server table on a database that is not the default database. The table is called "CHOOSEN" and the database is called "WORK". (Field called id is linked to a field called clientid below)

There is a MS Access table that is shared on our network drive called "ADDRESSES" and it is in the database "S:\Clients\CLIENTINFO.mdb"
(Field called clientid is linked to id in CHOOSEN above, field called zipcode is linked to uszips.dbf to a field called zips below)

Finally I've got a local table called uszips.dbf in my App.path where I'm designing the VB Project. (Has a field called zips and is linked to ADDRESSES above through the field called zipcode)

Can I design an SQL statement in VB so I can get data from all three tables? I can do it in Access if I simply link all the tables into the access database but I would like to do this with code.

Memnoch1207
06-03-2002, 03:08 PM
3 databases would need 3 separate connections. (This is my understanding)
Therefore, each database connection would need an individual SQL statement.

mhsueh001
06-03-2002, 03:10 PM
How do you get the tables to link together?

Memnoch1207
06-03-2002, 03:16 PM
This (http://www.ais.msu.edu/odbcinstall/LinktoTables.htm) might help...

mhsueh001
06-03-2002, 03:19 PM
That's how I'm doing it now!

I was wondering if there's anyway to code this so I can do a select statement from VB.

I've got a few apps that I've got this local uszips.dbf file that I want to combine with selections done with tables on the server. I however don't want to keep this uszips.dbf table on the server because it will be changing often enough that I want it maintained on the client machine.

Thinker
06-03-2002, 03:27 PM
The only way is to link all the tables from different sources into
one source. This would be easiest using linked tables in Access.
The links can be built in VB code. I made a post about it here
sometime in the last few months. Maybe I can find it...

Look here http://www.visualbasicforum.com/showthread.php?s=&threadid=22453

mhsueh001
06-03-2002, 04:49 PM
Thanks Thinker.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum