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.
Problem was resolved somewhere between an upgrade to Access 2007 and downloading a fresh copy of the database from the source. Still don't know what the root cause was, but suspect some form of index corruption.