Copy of database not showing current data

[krisplus5]

Hi folks,

I've got a web application that lets the user download a copy of the back-end Access database. The application is written in ColdFusion, writes to the Access database via an ODBC connection. We're using a CFFILE copy of the Access database to a subdirectory and renaming the file with a unique timestamp to avoid filename conflicts. It's been working fine for over a year.

Today client called and says she's not seeing the data she entered into the application in the database she's just downloaded. So I check it, and sure enough, the data I can see in the application is not showing in the database. 10 minutes later I download another new copy of the database and the records are updated correctly. Even better, we store a modified date on the record, and the modification date for the records in question is anywhere from 1-5 days in the past.

What's up with this?!? Is this a case of Access being held open and not caching data to disk? Any suggestions are most appreciated.

Cheers,
Kris

 

[DanJR]

Under tools > options > advanced tab, check out what the odbc refresh interval is (in seconds). See if this helps you with your problem. I dont use odbc with access, only other dbs, so i cant provide much help sorry

cheers,
dan

 

[krisplus5]

Okay, this is interesting, but strange...

I've checked the ODBC refresh interval, and it is set as per every Access database I've looked at with an ODBC refresh interval of 1500. Yes, that's 25 minutes! Would this actually lock database writes from a web-app?? If so, I'd think I'd have run into before now.

Any more info would be great... please...

Cheers,
Kris

 

[DanJR]

yeah, i agree... i think that setting is for the client end and thus shouldn't affect your web app.

Can the user immediately see the data after data entry, via the web application? Have to tried using DOS (or linux) copy commands to rule out CFFILE?

 

[krisplus5]

Yes, the user can see the data immediately after entry via the web application. But I've done some more research and have found that the ODBC DSN, has its own buffer size, which in this case (and other applications I've written as well) has been changed from the default 2048 to 65000. I believe this has to do with how the DSN is created.

In this scenario, the production box was previously a ColdFusion 5 server. Then it was upgraded to ColdFusion MX. The Access DSNs that were on the server when it was upgraded are fine. Any that were created afterward, through the ColdFusion Administrator have the extreme buffer size. I've also found that Macromedia (developers of CF) recommend setting Access ODBC DSNs to a buffer size of 0 (zero).

So I've changed the ODBC DSNs appropriately, but would still like to know what events force the DSN to flush the buffer, so that I can figure out why, in this case, it stayed open. Also still curious about how the webapp would be able to show the updated data when it hadn't been written to disk yet (but maybe it's pulling the entire recordset from memory, given that the buffer was set to 64MB!!!)

Cheers,
Kris