Query data from 2 MDB

[Lurc]

Hi all,

I need to query data from at least 2 MDB files that have a common CardNo
field. The tables that hold those info are in different MDBs, how can I
run select SQL statement under VB.NET environment?

For example:
In A.MDB, table CardInfo has fields CardNo and Name
In B.MDB, table Record has fields CardNo, Date, Controller

So what is the select phrase like,
Select
[a.cardinfo.cardno],[a.cardinfo.name],[b.record.date],[b.record.controller]
From [a.cardinfo],[b.record]
where [a.cardinfo.cardno]=[b.record.cardno]

Thanks.

--
Best regards,
RL

[Cor Ligthert]

RL,

This depends, when you use two tables, you are doing it with simple selects.
If you want to join a table you have to use more the SQL language. Than you
can as well not use standards as the designer to update using the
dataadapter.

In my opinion in the last situation is this a newsgroup where people are
active who like these things.

Adonet
news://msnews.microsoft.com/microsof...amework.adonet

Web interface:
http://communities2.microsoft.com/co...amework.adonet

I hope this helps something,

Cor

[Paul Clement]

On Tue, 31 May 2005 10:01:23 +0800, "Lurc" <lu*****@spam.hotmail.com> wrote:

¤ Hi all,
¤
¤ I need to query data from at least 2 MDB files that have a common CardNo
¤ field. The tables that hold those info are in different MDBs, how can I
¤ run select SQL statement under VB.NET environment?
¤
¤ For example:
¤ In A.MDB, table CardInfo has fields CardNo and Name
¤ In B.MDB, table Record has fields CardNo, Date, Controller
¤
¤ So what is the select phrase like,
¤ Select
¤ [a.cardinfo.cardno],[a.cardinfo.name],[b.record.date],[b.record.controller]
¤ From [a.cardinfo],[b.record]
¤ where [a.cardinfo.cardno]=[b.record.cardno]
¤
¤ Thanks.

See if the below works for you. Make sure to connect to database A.MDB and change the DATABASE path
in the SQL statement to the location of database B.MDB.

"SELECT CardInfo.[CardNo], CardInfo.[Name], Record.[Date], Record.Controller FROM CardInfo INNER
JOIN [MS Access;DATABASE=C:\My Databases\B.mdb;].[Record] ON (CardInfo.[CardNo]=Record.[CardNo])"
Paul
~~~~
Microsoft MVP (Visual Basic)