Excel VBA Query Across Databases

I'm trying to populate a recordset with 2 tables from seperate databases. So
far, I get no error when I run the query, but it's also not populating the
field from the second database (which happens to be the one I'm actively in).

The query is below:

With comboPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "select strName from db1_name..TblAnalysisOptions AO join
db2_name..TblResultHeader RH on AO.guidAnalysisOptions=RH.guidAnalysisOptions"

' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset comboPubs

' Tidy up
.Close
End With

Is there anyway to do this? Both our on the same machine.

James_Thomlinson
Thu May 08 14:09:00 CDT 2008

You can have as many connections open as you want but only one connection can
be active at a time. The open statement deals with the active connection. So
your query is not going to work as far as I know.
--
HTH...

Jim Thomlinson


"Chris C" wrote:

> I'm trying to populate a recordset with 2 tables from seperate databases. So
> far, I get no error when I run the query, but it's also not populating the
> field from the second database (which happens to be the one I'm actively in).
>
> The query is below:
>
> With comboPubs
> ' Assign the Connection object.
> .ActiveConnection = cnPubs
> ' Extract the required records.
> .Open "select strName from db1_name..TblAnalysisOptions AO join
> db2_name..TblResultHeader RH on AO.guidAnalysisOptions=RH.guidAnalysisOptions"
>
> ' Copy the records into cell A1 on Sheet1.
> Sheet1.Range("A1").CopyFromRecordset comboPubs
>
> ' Tidy up
> .Close
> End With
>
> Is there anyway to do this? Both our on the same machine.

Tim
Thu May 08 23:02:11 CDT 2008

You'll need to either create a linked table in one of your db's or
"manually" perform the join from 2 separate recordsets.

Tim

"Chris C" <ChrisC@discussions.microsoft.com> wrote in message
news:4FAD8794-BD4B-47EE-866E-D078BA8A4FF1@microsoft.com...
> I'm trying to populate a recordset with 2 tables from seperate databases.
> So
> far, I get no error when I run the query, but it's also not populating the
> field from the second database (which happens to be the one I'm actively
> in).
>
> The query is below:
>
> With comboPubs
> ' Assign the Connection object.
> .ActiveConnection = cnPubs
> ' Extract the required records.
> .Open "select strName from db1_name..TblAnalysisOptions AO join
> db2_name..TblResultHeader RH on
> AO.guidAnalysisOptions=RH.guidAnalysisOptions"
>
> ' Copy the records into cell A1 on Sheet1.
> Sheet1.Range("A1").CopyFromRecordset comboPubs
>
> ' Tidy up
> .Close
> End With
>
> Is there anyway to do this? Both our on the same machine.

ChrisC
Fri May 09 09:18:06 CDT 2008

Within the same sub/procedure/function, can I open one database, close it,
and open the other?

Alternative: Is it possible to have one function, connecting to DB1, be
called from a wrapper function that is connecting to DB2?

"Tim Williams" wrote:

> You'll need to either create a linked table in one of your db's or
> "manually" perform the join from 2 separate recordsets.
>
> Tim
>
> "Chris C" <ChrisC@discussions.microsoft.com> wrote in message
> news:4FAD8794-BD4B-47EE-866E-D078BA8A4FF1@microsoft.com...
> > I'm trying to populate a recordset with 2 tables from seperate databases.
> > So
> > far, I get no error when I run the query, but it's also not populating the
> > field from the second database (which happens to be the one I'm actively
> > in).
> >
> > The query is below:
> >
> > With comboPubs
> > ' Assign the Connection object.
> > .ActiveConnection = cnPubs
> > ' Extract the required records.
> > .Open "select strName from db1_name..TblAnalysisOptions AO join
> > db2_name..TblResultHeader RH on
> > AO.guidAnalysisOptions=RH.guidAnalysisOptions"
> >
> > ' Copy the records into cell A1 on Sheet1.
> > Sheet1.Range("A1").CopyFromRecordset comboPubs
> >
> > ' Tidy up
> > .Close
> > End With
> >
> > Is there anyway to do this? Both our on the same machine.
>
>
>

Tim
Fri May 09 15:06:49 CDT 2008

On May 9, 7:18=A0am, Chris C <Chr...@discussions.microsoft.com> wrote:
> Within the same sub/procedure/function, can I open one database, close it,=

> and open the other?
>
Yes. You could query the first set of data, cache the results in an
array (using getRows()), then run the second query on the other DB.

> Alternative: Is it possible to have one function, connecting to DB1, be
> called from a wrapper function that is connecting to DB2?
>
Don't see why not.

Tim

>
>
> "Tim Williams" wrote:
> > You'll need to either create a linked table in one of your db's or
> > "manually" perform the join from 2 separate recordsets.
>
> > Tim
>
> > "Chris C" <Chr...@discussions.microsoft.com> wrote in message
> >news:4FAD8794-BD4B-47EE-866E-D078BA8A4FF1@microsoft.com...
> > > I'm trying to populate a recordset with 2 tables from seperate databas=
es.
> > > So
> > > far, I get no error when I run the query, but it's also not populating=
the
> > > field from the second database (which happens to be the one I'm active=
ly
> > > in).
>
> > > The query is below:
>
> > > With comboPubs
> > > =A0 =A0' Assign the Connection object.
> > > =A0 =A0.ActiveConnection =3D cnPubs
> > > =A0 =A0' Extract the required records.
> > > =A0 =A0.Open "select strName from db1_name..TblAnalysisOptions AO join=

> > > db2_name..TblResultHeader RH on
> > > AO.guidAnalysisOptions=3DRH.guidAnalysisOptions"
>
> > > =A0 =A0' Copy the records into cell A1 on Sheet1.
> > > =A0 =A0Sheet1.Range("A1").CopyFromRecordset comboPubs
>
> > > =A0 =A0' Tidy up
> > > =A0 =A0.Close
> > > End With
>
> > > Is there anyway to do this? Both our on the same machine.- Hide quoted=
text -
>
> - Show quoted text -