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

SQL Query from two different access databases help required

P: 5
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
Aug 22 '07 #1
Share this Question
Share on Google+
5 Replies


codegecko
Expert 100+
P: 533
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
Aug 23 '07 #2

P: 5
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
Aug 23 '07 #3

P: 2
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.
Aug 26 '07 #4

P: 5
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
Aug 27 '07 #5

codegecko
Expert 100+
P: 533
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):

Expand|Select|Wrap|Line Numbers
  1. Dim arrRs1() As Array
  2. Dim arrRs2() As Array
  3. Dim arrJoinRecordsets() As  Array
  4. arrRs1 = myFirstRecordset.GetRows()
  5. arrRs2 =  mySecondRecordset.GetRows()
  6.  
  7. ‘Destroy recordsets – a performance  enhancement to free up additional  memory
  8. myFirstRecordset.Close
  9. mySecondRecordset.Close
  10.  
  11. ReDim  arrJoinRecordsets(UBound(arrRs1,1), (UBound(arrRs1,2) + UBound(arrRs2,2)))
  12. ‘  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).
  13. ‘ 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.
  14.  
  15. ‘ Populate from array 1
  16. For X = 0 To UBound(arrRs1,  1)
  17.             For Y = 0 To UBound(arrRs1, 2)
  18.                          arrJoinRecordsets(X,Y) = arrRs1(X,Y)
  19.             Next
  20. Next
  21.  
  22. For X =  0 To UBound(arrRs2,1)
  23.             For Y = (UBound(arrRs1,2) + 1) To  UBound(arrRs2,2)
  24.                         arrJoinRecordsets(X,Y) =  arrRs2(X,(Y-UBound(arrRs2,2)))
  25.             Next
  26. Next
  27.  
  28. Set arrRs1 =  Nothing
  29. Set arrRs2 = Nothing
  30.  
  31. ‘Now print the entire array in tabular  form
  32. %>
  33. <table>
  34. <%
  35. For Y = 0 To  UBound(arrJoinRecordsets,2) %>
  36. <tr>
  37. <% For X = 0 To  UBound(arrJoinRecordsets,1)  %>
  38. <td><%=arrJoinRecordsets(X,Y)%></td>
  39. <% Next  %>
  40. </tr>
  41. <% Next %>
  42. </table>
  43.  
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
Aug 27 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.