I have 2 applications that use the same Access 2000 database. Process 1 writes inserts records to the database and Process 2 does searches into the database interactively by the user. Process 1 inserts records to the database using straight ODBC API calls - this works fine. Process 2 uses a CRecordset class to retrieve these records with user defined search criteria.
Records are getting inserted into the database by Process 1 fine and Process 2 can view this data just fine. The problem lies within Process 2 when new data is inserted by Process 1. The new data is not returned in the query and thus isn't reflected in the results. I know the data is in the database, because I can open it and see it in the tables, but the recordset isn't returning it. That is unless I exit Process 2 down and restart it, then the new data is reflected in the results.
I'm using the same recordset object over and over and call Requery() to fetch any new results once I've opened it the first time. I've also tried closing it and reopening it with the same results.
Its as if the ODBC driver is caching the old results and not returning anything new. Anyone experience this before or have any ideas?
After wasting an entire day scratching my head, I finally found the problem...it was a bug in my searching code...
I wrote another small tester app that basically acted like Process2 and executed queries on a defined interval and would update a listbox with the results. When this small app didn't elicit the same behavior, I could rule out any problems caused by Process1 or some ODBC issue.