advanced ADO problem with SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Mark S. said:
I have an advanced problem that I am encountering and don't know how to
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
can't do anything with the recordset, because it won't even load
 
Back
Top