Query heterogeneous tables (dbf/mdb)

[billybobk]

I want to do a query that has the same results as this:
SELECT name FROM tblDBF WHERE name NOT IN (SELECT name FROM tblMDB) but I don't think that it can work, the first is dBase, the second is Access. I've tried with two connections, but no go. I'm thinking that the second tblMDB recordset could be assigned to an array and somehow the NOT IN criteria could check against that, but I'm not sure if it would work even if I DID know how to do it. Any sugggestions or experience with this sort of scenario?
Thanks duuuudes.

--Bill
Beeeeer....Mmmmmmmmmm.
-Homer Simpson

 

[KCI]

Hey,

That is the first thing that came to my mind ... you may have to do some manually cunkin to figure out the "NOT IN" clause.

good luck,
KCI

 

[CCLINT]

See the "IN" clause in the JET SQL help file (JETSQL40.CHM):

SELECT name FROM tblMDB IN 'somepath'

SELECT name FROM tblMDB IN '' [SomeProvider]

 

[billybobk]

Yes, cclint, you're on the right track. I've never seen the IN clause used this way before, but this is the thing that worked:
-------------------------------
dim sQ as string
sQ = chr$(39) 'single quote

strSQL = "SELECT Username FROM tblUsers " & _
"WHERE Username NOT IN (SELECT Username2 FROM tblUsers2 IN " & sQ & App.Path & "\myData.mdb" & sQ & &quot"
------------------------------------
The second "IN" is the one that's new to me. The ConnectionString points to the first database. Thanks!

 

[CCLINT]

>I've never seen the IN clause used this way before
>The second "IN" is the one that's new to me.

The first use of "IN" is NOT the same as the second one.

This first one is an "IN" operator.
The second one is a "IN" clause.

Just to possibly clear things up...

 

[billybobk]

Operator and clause. You're right. Checked out the JETSQL40.CHM file, and as you said, it's very informative. Thanks again!