I have an invoicing system that uses a VB6 program to post new invoice entries to a centralized Vendor management system (also in VB6), via a Web server and some ASP code. The system has been in production for some time now, but one pesky little problem keeps surfacing periodically.
System Description:
1. For each new line on an invoice, a specially formatted query line is appended to an Access database along with a blank time/date field.
2. A polling program running as a service periodically queries the database to see if there are any lines with blank time/date fields. When one is found, it is posted to a text file on the Web Server, and the the time/date field is updated in the database.
3. On the centralized system, another VB6 program (also running as a service) polls the text file on the Web Server to see if there are any new invoice lines. When one is found, it is added to the centralized database and deleted from the text file.
Very occasionally (about once every 3 months), an invoice line will be posted to the central database twice. My first thought was that it was a timing problem on the server, but after analyzing the log files involved, it now appears the problem is in the updating of the Access database (step 2 above).
09:46:58 - MPOLL from Manta database and posted to text file
09:47:01 - QREAD from text file and added to Manco database
09:47:01 - Web Server instructed to delete line from text file
09:47:11 - QREAD nothing found to process in text file
09:47:18 - MPOLL from Manta database and posted to text file
09:47:21 - QREAD from text file and added again to Manco database
09:47:21 - Web Server instructed to delete line from text file
09:47:31 - QREAD nothing found to process in text file
09:47:38 - MPOLL from Manta database and posted second line to text file
09:47:41 - QREAD from text file and added second line to Manco database
09:47:41 - Web Server instructed to delete line from text file
09:47:51 - QREAD nothing found to process in text file
The Access database (Access Jet 4.0) shows the time/date field as 09:47:18. This means that the update did not occur (nothing in the error log), or the data in the field was overwritten by the second update (seems to be the case). Is it possible that the Jet Engine held the record in cache until the second update query, and was overwritten? If so, how do I force the Jet Engine to write the data to the database before it queries for a blank timedate field again? My impression is that the requery should force the write cache to be emptied.
J.A. Coutts