SQL Query from two different access databases help required

[hugobotha]

Hi all,

Help will be much apreciated here.

How can I query two different access databases that has same table and fields.

This is how i access the one database but I want to be able to do it for both at the same time so that I have the rsult of available countries in both databases.

database1.mdb
database2.mdb

Set con = Server.CreateObject("ADODB.Connection")
con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:\inetpub\wwwroot\database1.mdb"
sql_check = "select Country, count(*) as CountryCount from Property"
sql_check = sql_check & " GROUP BY Country"
Set rs = con.Execute(sql_check)

Now I can loop through the record set and show each Country name for this Database but I would like this loop to include both database answers

Any help will be much apreciated.

Hugo

[JamieHowarth0]

Hi there,

There are a couple of ways you could do this:

1. Create a second connection and recordset for your second Access DB, then merge the two recordsets together into a third recordset and use this third recordset as your resulting output;
2. Create a single query in DB1 that imports the necessary data from DB2 and then joins the results of DB1 and DB2 together, then in ASP, retrieve the query from DB1 and output results.

I would say the second method is better in terms of performance, as it only involves one recordset being created on the server instead of 3.

If you want, PM/email me with links to your DBs and I can produce code for you.

medicineworker

[hugobotha]

Hi there,

There are a couple of ways you could do this:

1. Create a second connection and recordset for your second Access DB, then merge the two recordsets together into a third recordset and use this third recordset as your resulting output;
2. Create a single query in DB1 that imports the necessary data from DB2 and then joins the results of DB1 and DB2 together, then in ASP, retrieve the query from DB1 and output results.

I would say the second method is better in terms of performance, as it only involves one recordset being created on the server instead of 3.

If you want, PM/email me with links to your DBs and I can produce code for you.

medicineworker

Thanks for your reply !

I would like to try the first option first.

If I have made my conection one and use RS1 and second connection RS2 how do I merge the two ?

Never done this Merge so have no Idea.

Could you help me ?

Thanks

Hugo

[thaboloko]

why don't you want to use the second option? That option should work fine, even if you are not allowed to add queries to both MDB's, you can create a third MDB file, link both foreign tables, create an union query on that third mdb, and aim your recordset to the new third MDB.

to merge recordsets, I don't have an idea either, but I guess you should create a third table and populate it with both tables manually to achieve this.

Good Luck, Gustavo.

[hugobotha]

why don't you want to use the second option? That option should work fine, even if you are not allowed to add queries to both MDB's, you can create a third MDB file, link both foreign tables, create an union query on that third mdb, and aim your recordset to the new third MDB.

to merge recordsets, I don't have an idea either, but I guess you should create a third table and populate it with both tables manually to achieve this.

Good Luck, Gustavo.

THANKS DUDE !

I will give it a go !

Thanks

Hugo

[JamieHowarth0]

THANKS DUDE !

I will give it a go !

Thanks

Hugo

Quick follow-up - I provided Hugo with code on how to join the recordsets together by:

1. retrieving then closing both recordsets (performance);
2. using the GetRows method to convert both recordsets to arrays,;
3. looping through both arrays and populating a third array;
4. outputting results.

Which (in a general sense) looks a lot like this (presuming you have connected to your DB already and retrieved your two recordsets as ADODB.Recordset objects):

Dim arrRs1() As Array
Dim arrRs2() As Array
Dim arrJoinRecordsets() As  Array
arrRs1 = myFirstRecordset.GetRows()
arrRs2 =  mySecondRecordset.GetRows()
 
‘Destroy recordsets – a performance  enhancement to free up additional  memory
myFirstRecordset.Close
mySecondRecordset.Close
 
ReDim  arrJoinRecordsets(UBound(arrRs1,1), (UBound(arrRs1,2) + UBound(arrRs2,2)))
‘  This particular line of code sets the upper boundary of your X-dimension (your  field columns), followed by the Y dimension (the sum of rows of your two  recordsets).
‘ The reason for doing this is so that we can iterate through  results of array 1, then array 2, and populate array 3, before destroying arrays  1 and 2.
 
‘ Populate from array 1
For X = 0 To UBound(arrRs1,  1)
            For Y = 0 To UBound(arrRs1, 2)
                         arrJoinRecordsets(X,Y) = arrRs1(X,Y)
            Next
Next
 
For X =  0 To UBound(arrRs2,1)
            For Y = (UBound(arrRs1,2) + 1) To  UBound(arrRs2,2)
                        arrJoinRecordsets(X,Y) =  arrRs2(X,(Y-UBound(arrRs2,2)))
            Next
Next
 
Set arrRs1 =  Nothing
Set arrRs2 = Nothing
 
‘Now print the entire array in tabular  form
%>
<table>
<%
For Y = 0 To  UBound(arrJoinRecordsets,2) %>
<tr>
<% For X = 0 To  UBound(arrJoinRecordsets,1)  %>
<td><%=arrJoinRecordsets(X,Y)%></td>
<% Next  %>
</tr>
<% Next %>
</table>
 

Hope other people find it useful. Any bugs/errors, let me know (I wrote it in about 5mins before I had to run out the front door a couple of mornings ago).

medicineworker