Access 2007 query results different than vba sql results

williecg
williecg used Ask the Experts™
on
Hello experts this one really has me stumped.

Access 2007 query results are different than vba sql results.

Query produces one record.  This is the correct result.

VBA SQL produces 3 records.  There are 3 records in the table.

There are 4 fields in the table.
  T_ID is autonumber, indexed, no dups
  D_ID is number, indexed, dups ok
  The other 2 are text
When the D_ID field in NOT indexed the vba sql produces 1 record, the same as the query.

When the D_ID field IS indexed, the vba sql produces 3 records, the query produces 1 record.

Here is the sql that I ran from the query
============================================
SELECT [00_test2].T_ID
FROM 00_test2
WHERE ((([00_test2].LR_Eye)='Left') AND (([00_test2].Tissue_Type)='Whole Globe') AND (([00_test2].D_ID)=479300912));
============================================

Here is the VBA sql
============================================
strsql = _
"SELECT [00_test2].T_ID FROM 00_test2 WHERE ( (LR_Eye) = 'Left' AND ((Tissue_Type)= 'Whole Globe') AND ((D_ID)= 479300912)) "

MsgBox ("/.............  " & strsql)

Set rst = db.OpenRecordset(strsql)

MsgBox (rst.RecordCount)
rst.Close
============================================

Thanks this is making me crazy.

Cliff
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try this  sql

strsql = _
"SELECT [00_test2].T_ID FROM 00_test2 WHERE (((LR_Eye) = 'Left') AND ((Tissue_Type)= 'Whole Globe') AND ((D_ID)= 479300912)) "


or this

strsql = _
"SELECT [00_test2].T_ID FROM 00_test2 WHERE LR_Eye = 'Left' AND Tissue_Type= 'Whole Globe' AND D_ID= 479300912"

Top Expert 2010

Commented:
Yes, but since the criteria are all using 'And' then the parentheses or lack thereof should mean nothing...

Author

Commented:
thanks, but did not work.

The issue seems to be associated with the present or absence of indexing, dups ok.

if it is not indexed, my sql works
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2016

Commented:
can you upload a db with the table
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Try this:

Set rst = db.OpenRecordset(strsql)
rst.MoveLast
MsgBox (rst.RecordCount)

/gustav
Commented:
The RecordCount property is not reliable with a linked table until you have 'moved' through the entire table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial