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?

Answer 2

This might sound stupid. But...

Is the path to actual database & connection string (DSN) point to same file location?

Answer 3

Do you have the same problem with other ODBC tools, for example Query Tool? You can also turn on ODBC tracing in ODBC Connection Manager. I don't have access and don't know whether its sql commands will be traced but sometimes it helps me to solve ODBC problems.

Answer 4

Are the fields indexed? If so, maybe one of the indexes is corrupted and you need to compact the MDB file. If an index is corrupt, it can lead to major issues. You could lose existing relationships (if the corrupt index is the PK), or you could lose data. So you need to have a backup before you do this. If there is a corrupt index, I think the interactive Access compact operation will tell you, but if not, you can look for the MSysCompactErrors table which will tell you what errors occurred during the compact.

This happens only very rarely and can indicate one of two things:

1. bad application design, including obsolete Jet versions (Jet 4 before service pack 6 was very susceptible to this, and that's where I encountered it).

2. unreliable operating environment (networking/hardware/software).

Of course, this suggestion is a real long shot, but it is definitely one cause of different results (the most common would be to ORDER BY on the corrupt index and you'll end up with a different record count than with another ORDER BY).

Answer 5

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.

Answer 6

I guess the problem may be that you did not commit the query. PYODBC starts with autocommit = False and therefore every query like select,insert,update etc will start a transaction that in order to get effect you have to commit. Either call connection.autocommit = True or call cursor.execute("commit") after the query and then fetchall.