database query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

is it possible to query two tables in different .mdb files in a similar fashion as when i query two tables in the same .mdb file

select * from table1 where field1 = field
UNIO
Select * from table2 where field1 = field

This is basically how i join tables in the same database. Is there something similar that will join two tables in different databases?
 
Not in Access. SQL Server can do distributed queries quite nicely.

--
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

gandy said:
is it possible to query two tables in different .mdb files in a similar
Click to expand...
fashion as when i query two tables in the same .mdb file?
select * from table1 where field1 = field1
UNION
Select * from table2 where field1 = field1

This is basically how i join tables in the same database. Is there
Click to expand...
something similar that will join two tables in different databases?
 
Yes, you can query tables in two different Access databases, however you can
not join them.

The following is based on using a System DSN and a Global.asa file which has
both database connection defined.

<%
Dim Acct
Dim DSN_Name1
Dim DSN_Name2

DSN_Name1 = Application("db1_ConnectionString")
DSN_Name2 = Application("db2_ConnectionString")
Acct = Request.QueryString("AcctNo")

set Conn1 = Server.CreateObject("ADODB.Connection")
Conn1.Open DSN_Name1

set Conn2 = Server.CreateObject("ADODB.Connection")
Conn2.Open DSN_Name2

set table1RS = server.CreateObject("ADODB.Recordset")
table1SQL = "SELECT * FROM table1 WHERE AcctNo ='" & Acct & "'"
table1RS.Open table1SQL, Conn1

set table2RS = server.CreateObject("ADODB.Recordset")
table2SQL = "SELECT * FROM table2 WHERE AcctNo ='" & Acct & "'"
table2RS.Open table2SQL, Conn2

or you could do this

set table1RS = server.CreateObject("ADODB.Recordset")
table1SQL = "SELECT * FROM table1 WHERE AcctNo ='" & Acct & "'"
table1RS.Open table1SQL, Conn1

PropertyID = table1RS("AcctNo")

set table2RS = server.CreateObject("ADODB.Recordset")
table2SQL = "SELECT * FROM table2 WHERE PropertyID ='" & PropertyID & "'"
table2RS.Open table2SQL, Conn2

%>

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================


gandy said:
is it possible to query two tables in different .mdb files in a similar
Click to expand...
fashion as when i query two tables in the same .mdb file?
select * from table1 where field1 = field1
UNION
Select * from table2 where field1 = field1

This is basically how i join tables in the same database. Is there
Click to expand...
something similar that will join two tables in different databases?
 
Back
Top