PyODBC and Microsoft Access: Inconsistent results from simple query

Question

I am using pyodbc, via Microsoft Jet, to access the data in a Microsoft Access 2003 database from a Python program.

The Microsoft Access database comes from a third-party; I am only reading the data.

I have generally been having success in extracting the data I need, but I recently noticed some discrepancies.

I have boiled it down to a simple query, of the form:

SELECT field1 FROM table WHERE field1 = 601 AND field2 = 9067

I've obfuscated the field names and values but really, it doesn't get much more trivial than that! When I run the query in Access, it returns one record.

Then I run it over pyodbc, with code that looks like this:

connection = pyodbc.connect(connectionString)
rows = connection.execute(queryString).fetchall()

(Again, it doesn't get much more trivial than that!)

The value of queryString is cut-and-pasted from the working query in Access, but it returns no records. I expected it to return the same record.

When I change the query to search for a different value for field2, bingo, it works. It is only some values it rejects.

So, please help me out. Where should I be looking next to explain this discrepancy? If I can't trust the results of trivial queries, I don't have a chance on this project!

Update: It gets even simpler! The following query gives different numbers...

SELECT COUNT(*) FROM table

I ponder if it is related to some form of caching and/or improper transaction management by another application that occasionally to populates the data.

Answer 1

can you give us an obfuscated database that shows this problem? I've never experienced this. At least give the table definitions -- are any of the columns floats or decimal?