Just wondering... If I write a query using MINUS or INTERSECT, can ADO
execute it if I'm running it within Access?

I have two union-compatible sets and I want to return this result:

(A union B) minus (A intersect B)

(if you do a Venn diagram, it's the parts of A and B that do not
overlap.)

Here's the test:

Public Sub TestADOUnmatched()

Dim strSQL As String
Dim rs As ADODB.Recordset

strSQL = "(SELECT t1.PatientID, t1.Toxicity, t1.Cycle, t1.Grade
FROM t1 UNION ALL "
strSQL = strSQL & "SELECT t2.PatientID, t2.Toxicity, t2.Cycle,
t2.Grade FROM t2) MINUS "
strSQL = strSQL & "(SELECT t1.PatientID, t1.Toxicity, t1.Cycle,
t1.Grade FROM t1 INTERSECT "
stsrql = strSQL & "SELECT t2.PatientID, t2.Toxicity, t2.Cycle,
t2.Grade FROM t2);"

Debug.Print strSQL

Set rs = New ADODB.Recordset

rs.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly

Do Until rs.EOF
Debug.Print rs.Fields("PatientID"), rs.Fields("Toxicity"),
rs.Fields("Cycle"), rs.Fields("Grade")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing


End Sub


When I run it, I get "Syntax error in union query" -214746259
(80004005)... so I guess ADO doesn't support it...

I'm writing the query this way because any of the following can be
true;
1. a record is in one of the tables but not the other.
2. either cycle or grade can be wrong.

I guess I'll try it in SQL Server later and see if it works, and see
if it's the SQL that Access doesn't like that SQL Server does...

any thoughts? (Oh, and if you're reading this Aaron, I don't need the
standard rant. I'm just trying to get my head around some of the
differences between the Access query engine and the SQL Server query
engine.)

Thanks,

Pieter

Thanks
Pieter

Each DBMS seems to have its own specific dialect of SQL.

Access does not support either MINUS or INTERSECT. It doesn't matter whether
you're using DAO or ADO.

Afraid I can't remember whether SQL Server supports either. If it does.
you'll be able to use them via a pass-through query. (In other words, you
will not be able to create linked tables pointing to the SQL Server tables
and create a query on those linked tables)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



<(E-Mail Removed)> wrote in message
news:6a088550-1b3e-454a-a3a9-(E-Mail Removed)...
> Just wondering... If I write a query using MINUS or INTERSECT, can ADO
> execute it if I'm running it within Access?
>
> I have two union-compatible sets and I want to return this result:
>
> (A union B) minus (A intersect B)
>
> (if you do a Venn diagram, it's the parts of A and B that do not
> overlap.)
>
> Here's the test:
>
> Public Sub TestADOUnmatched()
>
> Dim strSQL As String
> Dim rs As ADODB.Recordset
>
> strSQL = "(SELECT t1.PatientID, t1.Toxicity, t1.Cycle, t1.Grade
> FROM t1 UNION ALL "
> strSQL = strSQL & "SELECT t2.PatientID, t2.Toxicity, t2.Cycle,
> t2.Grade FROM t2) MINUS "
> strSQL = strSQL & "(SELECT t1.PatientID, t1.Toxicity, t1.Cycle,
> t1.Grade FROM t1 INTERSECT "
> stsrql = strSQL & "SELECT t2.PatientID, t2.Toxicity, t2.Cycle,
> t2.Grade FROM t2);"
>
> Debug.Print strSQL
>
> Set rs = New ADODB.Recordset
>
> rs.Open strSQL, CurrentProject.Connection, adOpenStatic,
> adLockReadOnly
>
> Do Until rs.EOF
> Debug.Print rs.Fields("PatientID"), rs.Fields("Toxicity"),
> rs.Fields("Cycle"), rs.Fields("Grade")
> rs.MoveNext
> Loop
>
> rs.Close
> Set rs = Nothing
>
>
> End Sub
>
>
> When I run it, I get "Syntax error in union query" -214746259
> (80004005)... so I guess ADO doesn't support it...
>
> I'm writing the query this way because any of the following can be
> true;
> 1. a record is in one of the tables but not the other.
> 2. either cycle or grade can be wrong.
>
> I guess I'll try it in SQL Server later and see if it works, and see
> if it's the SQL that Access doesn't like that SQL Server does...
>
> any thoughts? (Oh, and if you're reading this Aaron, I don't need the
> standard rant. I'm just trying to get my head around some of the
> differences between the Access query engine and the SQL Server query
> engine.)
>
> Thanks,
>
> Pieter
>
> Thanks
> Pieter
>

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
news:(E-Mail Removed):

> Access does not support either MINUS or INTERSECT.

Sure it does, but only if you're using a BACK END DATABASE that
supports.

Remember, Access has no SQL dialect of its own because Access is not
a database engine -- it's only a front-end database development
environment.

Jet has its own SQL dialect and it lacks MINUS and INTERSECT, as you
say, but using "Access" when you mean "Jet" is something that I
think it's best to avoid, particularly in situations like this where
your statement makes it sound like there's no way to use it even
with a data store that supports it in its SQL dialect.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thanks for the feedback... I was afraid of that.