[python-win32] Re: ADODB.Recordset returning incorrect results

[Roger Upole]

"Matthew Perry" <perrygeo at gmail.com> wrote in message news:5383fa5e0706202114m7b308675k2bc2e3b5661b7995 at mail.gmail.com...
> Hi all,
> 
>  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.
> 
> 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? Is there some sort of caching going on? Why is
> there a discrepency?
> 
> Thanks for any tips!
> 
> 
> =========================
> import win32com.client
> 
> conn = win32com.client.Dispatch('ADODB.Connection')
> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/test1.mdb;'
> conn.Open(DSN)
> 
> rs = win32com.client.Dispatch('ADODB.Recordset')
> 
> sql = "SELECT [STUDY AREA] AS area FROM [qry base dataset - SS copcs
> only] GROUP BY [STUDY AREA]"
> rs.Open(sql, conn, 1, 3)
> 
> numrows = 0
> if rs.RecordCount > 0:
>    rs.MoveFirst()
>    while not rs.EOF:
>        print str(rs.Fields("area"))
>        rs.MoveNext()
> 
> rs.Close()
> conn.Close()
> del conn
> =======================
> 
> 

What does your subquery look like ?  If it contains anything that's affected by
the SQL dialect in use, this might explain it:
http://office.microsoft.com/en-us/access/HA010345621033.aspx

           Roger