By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Subselect from 2 tables from 2 different databases

P: n/a
Hi Folks,

I've been doing some research in this group and on the web but can't
seem to find the one clue that I'm looking for.

I have an ASP Page and need to join 2 tables from 2 different Access
databases. I can explain why 2 databases but it would just make this
a longer post so please just trust me.

I've gotten the following code to work fine:

-----------------------------
<%
'INTERNAL DB CONNECTION
dim strWWWRoot, strDBRoot, strDBName
strWWWRoot = server.mappath("/")
strDBRoot = strWWWRoot & "\..\"
strDBName = "wwwroot/devel/customer-internal.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strDBRoot & strDBName
Set internalconn = Server.CreateObject("ADODB.Connection")
internalconn.ConnectionString = strConnectionString
internalconn.Open
'EXTERNAL DATABASE CONNECTION Note: Althought I intialized this
connetion and opened it - I haven't actually figured out how to use
it.
strDBName2 = "wwwroot/devel/customer-external.mdb"
strConnectionString2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strDBRoot & strDBName2
Set externalconn = Server.CreateObject("ADODB.Connection")
externalconn.ConnectionString = strConnectionString2
externalconn.Open
'Select Record
p = "SELECT A.CUST_NAME FROM CUST_TABLE A WHERE A.CUST_NAME IN (SELECT
B.NAME FROM [C:\inetpub\wwwroot\devel\customer-external.mdb].ACCOUNT
B)"

Set rs = internalconn.Execute(p)

%>
---------------------------

My question is this: How can I get this to work by referencing the
two connections (internalconn & externalconn) instead of having to
reference the physical path of the 2nd database
(C:\inetpub\wwwroot\devel\customer-external.mdb)? I'd rather not
reference the physical path in case I change the name or something
down the road.

Thanks in advance for any help.
Mike G
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 2 Jun 2004 15:41:47 -0700, mg****@hotmail.com (Mike Smith) wrote:

I don't think that's possible. One statement uses one connection.

One thing to try is to link tables from db2 into db1. That way you can
get away with only using conn1. Of course if db2 moves, the links are
invalid and need to be refreshed.

Alternatively write a thorough error handler, and notify the
Administrator via email if db2 is not found in its usual location.

-Tom.
Hi Folks,

I've been doing some research in this group and on the web but can't
seem to find the one clue that I'm looking for.

I have an ASP Page and need to join 2 tables from 2 different Access
databases. I can explain why 2 databases but it would just make this
a longer post so please just trust me.

I've gotten the following code to work fine:

-----------------------------
<%
'INTERNAL DB CONNECTION
dim strWWWRoot, strDBRoot, strDBName
strWWWRoot = server.mappath("/")
strDBRoot = strWWWRoot & "\..\"
strDBName = "wwwroot/devel/customer-internal.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strDBRoot & strDBName
Set internalconn = Server.CreateObject("ADODB.Connection")
internalconn.ConnectionString = strConnectionString
internalconn.Open
'EXTERNAL DATABASE CONNECTION Note: Althought I intialized this
connetion and opened it - I haven't actually figured out how to use
it.
strDBName2 = "wwwroot/devel/customer-external.mdb"
strConnectionString2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strDBRoot & strDBName2
Set externalconn = Server.CreateObject("ADODB.Connection")
externalconn.ConnectionString = strConnectionString2
externalconn.Open
'Select Record
p = "SELECT A.CUST_NAME FROM CUST_TABLE A WHERE A.CUST_NAME IN (SELECT
B.NAME FROM [C:\inetpub\wwwroot\devel\customer-external.mdb].ACCOUNT
B)"

Set rs = internalconn.Execute(p)

%>
---------------------------

My question is this: How can I get this to work by referencing the
two connections (internalconn & externalconn) instead of having to
reference the physical path of the 2nd database
(C:\inetpub\wwwroot\devel\customer-external.mdb)? I'd rather not
reference the physical path in case I change the name or something
down the road.

Thanks in advance for any help.
Mike G


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.