Database Time and Unsynchronized Client Clocks

[hatman]

I inheritted admin of a lab-wide scheduling system that involves read-write activities performed by local instances of Excel on various client machines against a central Access database stored on a network drive. Ideally, this should have been done on a server, with a server side manipulation of the database, but instead, the previous developer chose to do everything client-side.

The crux of my problem is that in order to avoid pulling all records in the database, what they decided to do was to timetag each data write using the Now function of the local client machine. Then each client performs a periodic data pull, the query is based on timetags that numerically occur AFTER the previous data pull. It sounds like a great way to save resources, however, this method does NOT take into account the variation in the clock settings of each client machine. Users are complaining about "lost" data. In fact, teh data is moving to teh database, but depending upon the clock synch between clients, data can actually be written AFTER a data pull, but numerically have a timetag prior... which means that data is not retrieved in teh next data pull.

OOPS

I have considered several cludges, short of going to a server architecture (which I don;t know if I will be allowed to do) but in each case, before I start coding, I come up with huge conceptual hole that is no better than what is currently in place. So before I start trying to re-invent this wheel, does anyone have some clever solution that will fix this problem? I'll entertain anything, though I admit I have already considered, critiqued, and eventually dismissed at least a half-dozen of my own ideas.

 

[GlennUK]

Can you create a dummy file ( very small ) on the network drive automatically? Via an automated process, say, overwriting the file every second, and use the date/time stamp of this file as a clock for your Excel processes on the various client machines?

 

[hatman]

yeah, I thought about that. I probably would have been able to make it work... somehow. Turns out, once I started talking to some people around here, I managed to get access to teh SQL Server... sort of. So instead of migrating my access database and re-writing code, I simply added a query to teh SQL server to retrive the time. So now all of teh clients ping the SQL databse to pull teh time for use in all transactions with teh Access database.

 

[GlennUK]

Sounds good to me. Good luck