advanced ADO problem with SQL

[Guest]

I have an advanced problem that I am encountering and don't know how to solve. Much thanks for ANY help

SETUP
Access 2002, ADO 2.1, linking to Oracle 7.3 ver 2.5

EXECUTION
In code, I have the following:

'create rec set with crosstab query's SQL
Set cn = CurrentProject.Connection
rstShell.Open (getSQLfromQry("qryName")), cn, adOpenStatic, adLockPessimistic
...

It returns an ODBC error.
The getSQLfromQry function is a common procedure, used often. I verified in debugging that it returns the appropriate SQL. In this case (the error) it returns:

"TRANSFORM Count(scrub.PO_NUMBER) AS CountOfPO_NUMBER
SELECT scrub.Variances, Count(scrub.PO_NUMBER) AS [Total Of PO_NUMBER]
FROM scrub
GROUP BY scrub.Variances
PIVOT scrub.Month;"

RESEARCH
It turns out in this one case that returns error, there are no records returned by my SQL.
Also - this is IMPORTANT - when tables (data) are local, this error does not occur, even though there are still no records returned by SQL

QUESTIONS
How can I successfully test for this condition of no records? Remember, I can't do anything with the recordset, because it won't even load
Why is it only an error when linking to data?

 

[[MVP] S. Clark]

To test for the condition of zero records, you would need to run a query,
but if a no record query causes a problem, then you're back where you
started.

I have never worked with Oracle data, but would think that the problem lies
in the translation between the two, or Access is choking on something during
the crosstab.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I have an advanced problem that I am encountering and don't know how to

solve. Much thanks for ANY help

SETUP
Access 2002, ADO 2.1, linking to Oracle 7.3 ver 2.5

EXECUTION
In code, I have the following:

'create rec set with crosstab query's SQL
Set cn = CurrentProject.Connection
rstShell.Open (getSQLfromQry("qryName")), cn, adOpenStatic, adLockPessimistic
...

It returns an ODBC error.
The getSQLfromQry function is a common procedure, used often. I verified

in debugging that it returns the appropriate SQL. In this case (the error)
it returns:

"TRANSFORM Count(scrub.PO_NUMBER) AS CountOfPO_NUMBER
SELECT scrub.Variances, Count(scrub.PO_NUMBER) AS [Total Of PO_NUMBER]
FROM scrub
GROUP BY scrub.Variances
PIVOT scrub.Month;"

RESEARCH
It turns out in this one case that returns error, there are no records returned by my SQL.
Also - this is IMPORTANT - when tables (data) are local, this error does

not occur, even though there are still no records returned by SQL

QUESTIONS
How can I successfully test for this condition of no records? Remember, I

can't do anything with the recordset, because it won't even load