Using two recordsets from different sources 1

[LinuxKommy]

I need to use two recordsets from different sources. specifically, I query our main DB (ODBC) to get updated price information. In an access DB, i have some extended fields for the products, like length, width, and height. I need to have it update the pricing in the existing table and also add any new items that aren't already in the access DB. I guess my question is, how would i perform a join on two tables in different DB's? right now, i use access to do the query, b/c it lets me link the ODBC table.

thanks,
DHG

 

[ArtieChoke]

You can't join tables across sources in ADO (at least in my experience). You'll need to get recordsets from both tables, then iterate through one recordset, updating the other one - one record at a time.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[johnwm]

I think you're looking for the IN keyword.

SELECT CustomerID
FROM Customers
IN OtherDB.mdb
WHERE CustomerID Like "A*";

This example is from JetSql4 helpfile, which should be here:

"C:\Program Files\Common Files\Microsoft Shared\Office10\1033\jetsql40.chm"

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[LinuxKommy]

thank you for your replies.

Artichoke,
Does looping through the recordset significantly increase query time? The problem is that the query (as it is now) takes two hours. Part of this is because it has to go over a T1 line. The other part is that the DBA doesn't re-index the table very frequently, and the table also has around 2 million records.

johnwm,
Will that work even if the query i'm using is not JetSQL? I believe it's SQL-92 (or whatever the designation is )
Unfortunately, we have Office 2003, and i can't find jetsql40.chm....apparently they don't want you to know it anymore

thanks again,
DHG

 

[cjac]

If you would like to query 2 seperate db's and hold the results in just the one recordset you could ask your DBA to set up a link from one db to the other. I use this method when querying on more than one db - it also saves on connections as I only need to establish one connection to hit both db's. ie. in oracle you can execute a query against 2 tables both in seperate db's (with the db link set up from one db to another) something like this:
select a.customer, b.addr1 from customer a, address@dblink b
where a.ref1 = b.ref1

Access also provides the facility to link into another db so this should work although you'd have to find the correct sql to access a db link in Access.

 

[LinuxKommy]

Cjac,

thanks for the info! i had never even thought of that.

So you're saying that the current db i have now with the linked tables will suffice? the only thing i'm worried about is now it should run even slower, as i'm using OLEDB to connect to access, and then through the already slow link. Is there any way to speed this up? (if you need more detailed info about the query, i'll give it)

thanks,
DHG