Select query on two databases

[parminder]

Hi to all,

Who can we run VB.NET's SELECT query on two MS-Access database. For example :

There are two databases: Database1 and Database2
There are two tables Table1 and Table2 from Database1 and Database2 respectively.
Table1's field "Field1" and Table2's field "Field2" are for to store common object (datatype)

Now how can I run the query like this:

Select * from Table1,Table2 where Table1.Field1=Table2.Field2


How it is possible in VB.net (ADO.NET)

Thanks in Advance

[powdir]

SQL is just SQL. Its not the preserve of VB.NET so anything you can query using SQL can be embedded in VB.NET code and executed.

However you mention you will be querying from 2 databases in one SQL statement - make sure MS Access supports this concept. Table linking across domains is easy in Oracle (and i would imagine SQL Server)..not sure about Access

Cheers...

[parminder]

Thanks powdir

In one of thread "kleinma" told it is possible, but not mentioned how ? (His means was it is possible from two different data sources)

I am interested in MS-Access.


Thanks again

[powdir]

yup - i completely forgot how easy it actually is to link tables in MS Access.

FILE>GET EXTERNAL DATA>LINK TABLES

you get a file chooser dialog from where you can choose the datasource to link to (*.mdb, ODBC etc)

Access then creates a linking table (which you can browse like a local table) you can then query using standard SQL syntax

Hope that helps - cheers

[parminder]

Thanks powdir again


I have tried this type of solution in MS-Access and VB6. But it works only if these (MS-Access) files are not password protected.

I will try it in MS-Access 2002 and in VB.net


Thanks

[parminder]

in following thread "kleinma" mention this thing


http://www.vbforums.com/showthread.p...hreadid=261644

[kleinma]

I don't work much with .NET right now because at my current job we haven't even thought about deploying the .NET framework out to our systems. But they did send us to a seminar on it when .NET was being released and I remember them talking about it there... The below link is a reference for you to use

http://msdn.microsoft.com/library/de...ngdatasets.asp

DataSets are what is used to accomplish this.. they are sort of the successor to ADO Recordsets

from this link

The DataSet is independent of any specific data source and therefore can be populated from multiple and differing data sources including relational databases and XML, or can be populated with data local to the application.

[parminder]

Thanks kleinma


I was thinking it is without DataSet, but the link give me what I wanted.

Thanks

[kleinma]

no problem....

once I get into my new job next month, I will have .NET, so I will be working with it a lot more as well