Strange Query Behavior (Repost)

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

Sorry for the cross post, but this behavior is strange enough that I need a
broad cross section of experts to take a look.

I'm running into a strange problem with an Access 2K query. I forgot to
mention during my earlier post that this query is against 2 Oracle tables
that have been linked via and ODBC connection to Access.

The query I'm running essentially does a comparision between the two tables
with the same structure to identify records as being in Table A only, Table
B only, or in both tables with differences in data. If the record is in
both tables and does not have any differences, then it is not returned. It
does this by UNIONing three subqueries (one for each criteria), and seems to
be working properly.

However, when I write code to dump the results of this query into a
temporary table, it doesn't return the same result set as when I run the
query. When the query is run by itself, it might return 2 or 3 records, but
when run using the code below, it might generate 20 or 30 records. When I
look at the tables, the extra records in the temporary table fall into the
"in both tables with no data differences" category (which means they
shouldn't be returned at all). In fact they show up as belonging to one or
the other of the tables, but not both. What is even weirder is that they
are not the only ones that fall into that category.

I'd really appreciate hearing from anyone who has encountered this behavior
before

if TableExists("tbl_New") then
docmd.deleteobj acTable, "tbl_new "
endif
strSQL = "SELECT * INTO tbl_New FROM qry_my_query"
currentd.execute strSQL
 
(1) Nothing wrong with judicious crossposting IMO.

(2) By currentd.execute, do you mean currentd>b<.execute? If so, be aware
that it is normally best practice to save the value of currentdb() in a
variable, then use that variable. For example, currentdb.recordsaffected
would not return the # of records processed by the Execute statement. The
code below, would:

dim db as database
set db = currentdb()
....
db.execute ...
msgbox db.recordsaffected
....
set db = nothing

(3) You need to add the dbFailOnError constant to the Execute statement.
Otherwise, failed additions will be ignored silently. With dbFailOnError,
they will cause a runtime error (which is what you need, to show you that
something has failed).

HTH,
TC
 
I don't know how you are doing your sorting and grouping: I
don't know how you are identifying target records.

Queries based on aggregate queries occasionally fail with this
kind of error in Access.

ODBC queries occasionally fail with type conversion errors.

Comparison of single/double/currency/float numbers occasionally
fails with this kind of error.

Criteria run against ODBC links are sometimes evaluated
locally, sometimes pushed back to the server.

(david)
 
TC,

I've replaced all the references to currentdb with the
appropriate reference to db. I've also added all the
dbFailOnError options to the execute methods, but I'm
still getting the same behavior. No error message, just
Access returning an invalid recordset.

I can cut the SQL out of the debug window and paste it
into a query and get zero records. When I let my code
continue and execute the query using db.Execute it returns
10 records. This is a major headache.
 
David,

Thanks for your feedback, not much help though other than
to make me think twice about using ODBC connectivity
between Access and Oracle.
 
Dale Fye said:
Sorry for the cross post, but this behavior is strange enough that I
need a broad cross section of experts to take a look.

I'm running into a strange problem with an Access 2K query. I forgot
to mention during my earlier post that this query is against 2 Oracle
tables that have been linked via and ODBC connection to Access.

The query I'm running essentially does a comparision between the two
tables with the same structure to identify records as being in Table
A only, Table B only, or in both tables with differences in data. If
the record is in both tables and does not have any differences, then
it is not returned. It does this by UNIONing three subqueries (one
for each criteria), and seems to be working properly.

However, when I write code to dump the results of this query into a
temporary table, it doesn't return the same result set as when I run
the query. When the query is run by itself, it might return 2 or 3
records, but when run using the code below, it might generate 20 or
30 records. When I look at the tables, the extra records in the
temporary table fall into the "in both tables with no data
differences" category (which means they shouldn't be returned at
all). In fact they show up as belonging to one or the other of the
tables, but not both. What is even weirder is that they are not the
only ones that fall into that category.

I'd really appreciate hearing from anyone who has encountered this
behavior before

if TableExists("tbl_New") then
docmd.deleteobj acTable, "tbl_new "
endif
strSQL = "SELECT * INTO tbl_New FROM qry_my_query"
currentd.execute strSQL
Click to expand...

Dale -

I don't know exactly what's going on and why, but have you considered
using pass-through queries to let the Oracle database do the work of
selecting the records you want? It seems to me that if this is due to
sort of inconsistency in client-side/server-side processing, using
pass-through queries should eliminate the problem.
 
So, let me be sure that I understand this. The exact same SQL produces 0
records when run from a stored query, but 10 records when run from code
using db.execute dbfailonerror?

Is there any chance that the query is calling a function that is causing a
side-effect of some kind? Do you get reproducible results if you run the SQL
several times in a row< from a stored query, then >several times in a row<
Click to expand...
from code, then several times from a query again, then several times from
code again?

And the obvious question: are you >absolutely certain< that the SQL is the
same?

Apart from that, I'm out of ideas. Show us the SQL, maybe that will prompt
some thoughts.

HTH,
TC
 
Back
Top