A question of to run your daily procedures

[AUGuy]

I'm curious as to where the best place to run your daily procedures, whether it be the front-end or back-end or separate 'Control' database, is.

We have several databases deployed in several departments that track all manner of things. Each of these databases update differently and using different protocols. Right now, must of them run update protocols when the first person logs into the database each day. Unfortunately, this puts the data integrity (and them not forceclosing the database or doing something else stupid while updating) in their hands for that short period of time.

One proposed solution is moving the update VBA code to the back end and having it run through a scheduled task on-open from a workstation in our office each day. This would allow us complete control of the update and keep errors visible to only us (and more importantly not rely on them to report unforeseen errors).

I'm looking for the downside to this idea, what risks are there with having oru update procedures on the back end?

 

[boblarson]

Do not put code into your backend. That is a surefire way to greatly increase your odds of corruption. Data ONLY in the backend.

If you need to run something each day, a separate control database would be the better way to do it.

 

[AUGuy]

Do not put code into your backend. That is a surefire way to greatly increase your odds of corruption. Data ONLY in the backend.

If you need to run something each day, a separate control database would be the better way to do it.

Thanks, Bob. Do you have any articles related to how the code can corrupt the data? I've not run across this but would like to know exactly what happens and why not to do it without making you write the article for me

 

[boblarson]

I don't have a lot handy at the moment, but a quick Bing search yielded this from FMS which is a highly respected company by the Microsoft Access Development Team at Microsoft. In fact, the owner was recently named an MVP.

http://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/Index.html

Without splitting a database, multiple users running the same database on the network increase the chance of database corruption. The split database design minimizes this problem and avoids code corruption from impacting data corruption.

 

[AUGuy]

I don't have a lot handy at the moment, but a quick Bing search yielded this from FMS which is a highly respected company by the Microsoft Access Development Team at Microsoft. In fact, the owner was recently named an MVP.

http://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/Index.html

Thanks again, Bob. I don't doubt you or this individual whatsoever. However, to sign off on the proposal with my boss I still would like to see some detailed reasoning as to why and how code can cause issues with data. Time for some good ole fashioned research. I'll report back with anything conclusive (should it be findable).