[python-win32] ADODB.Recordset returning incorrect results

[Tim Roberts]

Matthew Perry wrote:
>   I'm using Access 2000 .mdb as a backend to my python script and am
> seeing some inconsistencies in the results.
>
> If I run the script below, I get 15 rows.
>
> If I run the SAME EXACT query directly in MS Access, I get 12 rows.
>   

Can you see any pattern to the rows that are missing?  The query seems
simple enough.

> Perhaps relevant to the issue - the table referenced in the query
> below is not actually a table but another query. Can you use rs.Open()
> on a chained query?

Yes, this should work fine.

> Is there some sort of caching going on?

No, there's no caching.  Remember this is file based: when you close the
file, all memory is gone.

>  Why is there a discrepency?
>   

Don't know.  As an experiment, you might try doing this with DAO instead
of ADODB.  DAO is the native API for the Jet engine; it's quicker than
ADODB, but much less general.

import win32com.client
engine = win32com.client.Dispatch("DAO.DBEngine.36")
dao = engine.OpenDatabase('c:\\test1.mdb')
sql = "SELECT [STUDY AREA] AS area FROM [qry base dataset - SS copcs
only] GROUP BY [STUDY AREA]"
rs = cmd.OpenRecordset( sql )
while not rs.EOF:
    print rs.Fields("area").Value
    rs.MoveNext()

-- 
Tim Roberts, timr at probo.com
Providenza & Boekelheide, Inc.