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
