Get single ADO Recordset from multiple Access Databases

Get single ADO Recordset from multiple Access Databases

Post by UG9udGlmaW » Wed, 29 Nov 2006 06:22:02


Hi:

I'm currently using an embedded MS Query to populate a temporary
spreadsheet, then using the resulting rows to populate other spreadsheets,
then removing the temp sheet. I would rather use an ADO recordset to
accomplish the same thing and not have to manipulate a temp sheet.

Problem: The embedded query links two separate Access databases and uses
tables from each in a single Join SQL statement. I've not been able to set
this up using ADO connections, and have found nothing on the forum addressing
this specific situation. Can it be done?

Thanks in advance!

Greg
 
 
 

Get single ADO Recordset from multiple Access Databases

Post by Dave Patri » Wed, 29 Nov 2006 11:47:29

The example by Steve Brown should get you pointed in the right direction.

http://www.yqcomputer.com/ +tables+grom+different+databases.html

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.yqcomputer.com/

 
 
 

Get single ADO Recordset from multiple Access Databases

Post by UG9udGlmaW » Thu, 30 Nov 2006 22:31:01

Thanks for your quick response, Dave. I've been able to construct the SQL,
now what's hindering is the Connection string. The code below shows my
quandary. Note that there are two different databases in the SQL. How do I
show two different connections in a single connection string? I want the
resulting records in a single recordset.

Please ignore syntax, it's the double connection I'm trying to pin down.

Greg

~~~~~~~~~~~~~~~~~~~~~~~~

Dim SQLcmd As String

SQLcmd = "SELECT MRP.Item, MRP.Desc,
INMAST.Height , INMAST.Notes
FROM `P:\Chicago\Production\Masterdb`.INMAST INMAST,
`P:\Chicago\Production\Plant2`.MRP MRP
WHERE INMAST.Item = MRP.Item"

Set rs = New ADODB.Recordset

rs.Open Source:=SQLcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=P:\Chicago\Production\db2;
User Id=admin; Password="

(I can only put one Data Source in the above string!?)
~~~~~~~~~~~~~~~~~~~~~
 
 
 

Get single ADO Recordset from multiple Access Databases

Post by Dave Patri » Sat, 02 Dec 2006 12:43:20

I don't see enough to know for sure and I don't have time to test tonight
but try something like this. I might have your DB's and or columns mixed up.


Select MRP.Item, MRP.Desc
FROM MRP
JOIN IMAST IN 'P:\Chicago\Production\Masterdb.mdb' ON
(MRP.Item = INMAST.Item)

The connection string opens Plant2.mdb

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.yqcomputer.com/