Querying two recordset objects or Joining tables from different datastores 
Author Message
 Querying two recordset objects or Joining tables from different datastores

Hi:

Note: there was a answer to a similar post that I simply cannot find.
It mentioned something about fully qualifying the object names, needing
only one connection, and included text about <Server> and .Client. I think
it had "VB and SQL" or "RDO and SQL" in the subject line. If anyone can
point me to that, and elaborate, that may be all I need.

if not, then...

I'm trying to join two recordsets created by queyring two tables in
two different datastores.

Query 1 from Table 1 Datastore 1
--------------------------------
Recordset name is 'objRSJet', based on a connection to an Access 2000
database and using the following SQL

Select Serial_No, AccountNo from Register Where
((register.Serial_No='04067') And ((register.Superceded=false) or
(Register.Superceded is null)))

The result of Query1 (Serial_No AccountNo):
04067 98090339666&{8L8+cha

In this case there in only one response, but there potentially could be
many (for instance using "((register.Serial_No Like '040%')". So, I'd like
to find all the contact information for each of the resulting account
numbers, mainly to allow sorting the data by the values in the different
fields. The contact information is in a separate datastore (SQL Server),
tens of thousands of records.

So, I open a connection to the SQL datastore, and create a recordset
object (objRS) and use the following SQL:

Query 2 from Table 2 Datastore 2
--------------------------------
Select AccountNo, Contact1.Contact, Contact1.Company, Contact1.LastName,
Contact1.City, Contact1.State, Contact1.Country from Contact1
Record Count: 47381

So now I have two Recordsets that I'd like to relate by the AccountNo
field using a third recordset object.

SQL for the third recordset object (strSelectJoin, which is 'connectionless',
if you will)

Select objRSJet.SerialNo, objRS.Contact From objRSJet
Inner Join objRS on objRSJet.AccountNo = objRS.AccountNo

objRSJoin is an object! (note: response from an IsObject test)

But I get the following error message:
=======================================

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Open'

/test1.asp, line 64
=======================================

The line in question is, objRSJoin.Open strSelectJoin with or without a valid
connection object specified.

A better answer: create only the first two recordset objects, and use the
second to do the joining:

Select Contact, Company, LastName, City, State, Country from Contact1
Inner Join objRSJet Where ((Contact1.AccountNo = objRSJet.AccountNo))

Any ideas?

There are other ways to do this, create a temporary table in one database
or another and use that in the join, or arrays and loops, etc.

But, I keep hearing the "Universal Data Access" mantra and it makes me
believe what I'm trying to do is possible and not have to use those less
elegant solutions.

Thanks for your time. Enjoy the weekend!

JK



Wed, 03 Sep 2003 07:36:33 GMT  
 Querying two recordset objects or Joining tables from different datastores
hi John
Depends how do you connect to the database, via ADO or RDO, etc.
If you can elaborate,  maybe you will get a response.

SUkesh



Quote:

> Hi:

> Note: there was a answer to a similar post that I simply cannot find.
> It mentioned something about fully qualifying the object names, needing
> only one connection, and included text about <Server> and .Client. I think
> it had "VB and SQL" or "RDO and SQL" in the subject line. If anyone can
> point me to that, and elaborate, that may be all I need.

> if not, then...

> I'm trying to join two recordsets created by queyring two tables in
> two different datastores.

> Query 1 from Table 1 Datastore 1
> --------------------------------
> Recordset name is 'objRSJet', based on a connection to an Access 2000
> database and using the following SQL

> Select Serial_No, AccountNo from Register Where
> ((register.Serial_No='04067') And ((register.Superceded=false) or
> (Register.Superceded is null)))

> The result of Query1 (Serial_No AccountNo):
> 04067 98090339666&{8L8+cha

> In this case there in only one response, but there potentially could be
> many (for instance using "((register.Serial_No Like '040%')". So, I'd like
> to find all the contact information for each of the resulting account
> numbers, mainly to allow sorting the data by the values in the different
> fields. The contact information is in a separate datastore (SQL Server),
> tens of thousands of records.

> So, I open a connection to the SQL datastore, and create a recordset
> object (objRS) and use the following SQL:

> Query 2 from Table 2 Datastore 2
> --------------------------------
> Select AccountNo, Contact1.Contact, Contact1.Company, Contact1.LastName,
> Contact1.City, Contact1.State, Contact1.Country from Contact1
> Record Count: 47381

> So now I have two Recordsets that I'd like to relate by the AccountNo
> field using a third recordset object.

> SQL for the third recordset object (strSelectJoin, which is
'connectionless',
> if you will)

> Select objRSJet.SerialNo, objRS.Contact From objRSJet
> Inner Join objRS on objRSJet.AccountNo = objRS.AccountNo

> objRSJoin is an object! (note: response from an IsObject test)

> But I get the following error message:
> =======================================

> Microsoft VBScript runtime error '800a01b6'

> Object doesn't support this property or method: 'Open'

> /test1.asp, line 64
> =======================================

> The line in question is, objRSJoin.Open strSelectJoin with or without a
valid
> connection object specified.

> A better answer: create only the first two recordset objects, and use the
> second to do the joining:

> Select Contact, Company, LastName, City, State, Country from Contact1
> Inner Join objRSJet Where ((Contact1.AccountNo = objRSJet.AccountNo))

> Any ideas?

> There are other ways to do this, create a temporary table in one database
> or another and use that in the join, or arrays and loops, etc.

> But, I keep hearing the "Universal Data Access" mantra and it makes me
> believe what I'm trying to do is possible and not have to use those less
> elegant solutions.

> Thanks for your time. Enjoy the weekend!

> JK



Wed, 03 Sep 2003 08:39:02 GMT  
 Querying two recordset objects or Joining tables from different datastores
You could probably come up with some code using arrays or disconnected
ADO recordsets, but since one of the data stored is Jet, you'll
probably get better performance if you use a Jet table. Create the
shell table in Jet, defining the fields that the two recordsets will
be returning. Dump the data from SQL Server into the table, and then
join that to the Jet table. When you're done, clean out the Jet table.
The downside to this is that you'll need to compact the mdb a lot
since Jet bloats. Another option is to set up a linked server in SQL
Server to the Jet mdb. You could write a stored procedure that queries
the linked server and joins to the native SQL Server table.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Fri, 16 Mar 2001 15:36:33 -0800, "John K. King"

Quote:

>Hi:

>Note: there was a answer to a similar post that I simply cannot find.
>It mentioned something about fully qualifying the object names, needing
>only one connection, and included text about <Server> and .Client. I think
>it had "VB and SQL" or "RDO and SQL" in the subject line. If anyone can
>point me to that, and elaborate, that may be all I need.

>if not, then...

>I'm trying to join two recordsets created by queyring two tables in
>two different datastores.

>Query 1 from Table 1 Datastore 1
>--------------------------------
>Recordset name is 'objRSJet', based on a connection to an Access 2000
>database and using the following SQL

>Select Serial_No, AccountNo from Register Where
>((register.Serial_No='04067') And ((register.Superceded=false) or
>(Register.Superceded is null)))

>The result of Query1 (Serial_No AccountNo):
>04067 98090339666&{8L8+cha

>In this case there in only one response, but there potentially could be
>many (for instance using "((register.Serial_No Like '040%')". So, I'd like
>to find all the contact information for each of the resulting account
>numbers, mainly to allow sorting the data by the values in the different
>fields. The contact information is in a separate datastore (SQL Server),
>tens of thousands of records.

>So, I open a connection to the SQL datastore, and create a recordset
>object (objRS) and use the following SQL:

>Query 2 from Table 2 Datastore 2
>--------------------------------
>Select AccountNo, Contact1.Contact, Contact1.Company, Contact1.LastName,
>Contact1.City, Contact1.State, Contact1.Country from Contact1
>Record Count: 47381

>So now I have two Recordsets that I'd like to relate by the AccountNo
>field using a third recordset object.

>SQL for the third recordset object (strSelectJoin, which is 'connectionless',
>if you will)

>Select objRSJet.SerialNo, objRS.Contact From objRSJet
>Inner Join objRS on objRSJet.AccountNo = objRS.AccountNo

>objRSJoin is an object! (note: response from an IsObject test)

>But I get the following error message:
>=======================================

>Microsoft VBScript runtime error '800a01b6'

>Object doesn't support this property or method: 'Open'

>/test1.asp, line 64
>=======================================

>The line in question is, objRSJoin.Open strSelectJoin with or without a valid
>connection object specified.

>A better answer: create only the first two recordset objects, and use the
>second to do the joining:

>Select Contact, Company, LastName, City, State, Country from Contact1
>Inner Join objRSJet Where ((Contact1.AccountNo = objRSJet.AccountNo))

>Any ideas?

>There are other ways to do this, create a temporary table in one database
>or another and use that in the join, or arrays and loops, etc.

>But, I keep hearing the "Universal Data Access" mantra and it makes me
>believe what I'm trying to do is possible and not have to use those less
>elegant solutions.

>Thanks for your time. Enjoy the weekend!

>JK



Wed, 03 Sep 2003 22:05:18 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Table join between two tables in different databases

2. Joining two tables from different databases

3. Creating a join on tables in two different MSSQL 6.5 DB's

4. Joining two tables on different length fields

5. Table join between two different databases (MSSQL 6.5)

6. joining two tables from different dbs

7. Joining two tables from different dbs

8. Join on two tables from different data sources?

9. Trying to bind data from two different tables to two different controls

10. Query Builder: Join table from different MDB?

11. Query Builder: Join table from different MDB?

12. Query data from tables in two different databases

 

 
Powered by phpBB® Forum Software