Backups in MS Access 2007 vs SQL Server 2008

we have an application built around Ms Access 2007 with a lot of VB code in place. Multiple users are in the system all day and night long. Intermittently.

We have a backup process that normally takes place at 5 am in the morning. We have instructed our staff to be out of the system during the hours 5 am to 5:30 sometimes they are sometimes not. When they are not out of the system it causes the backup to fail. Something we run on Task Scheduler.
Would one or all of the following work......?
1. Switch the Access 2007 database to a SQL Server DB....? We want to keep the Access user interface though.
2. Create code that informs the users he must get out( log out) ....finish his work etc so the system can be off for that 5 to 5:30 period........if so where do we find the code.for that. Even a backup email might be good too.

How could we solve this problem. Your support is greatly appreciated.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If you want to go for  #2 ... you can build a Forced Shutdown module around this:

And/or this:

I used Peter's FSD app to build a full blown FSD module which we use nightly.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Moving to SQL Server would eliminate the need to force users out, since SQL Server doesn't require this the way Access does. However, moving to SQL Server generally requires that you do some rework of your application. How much reworkd depends on many things, so it's impossible to say what the impact would be. IN general most report that moving to SQL Server is a good thing.

You could keep your existing Access interface is you do this - Access integrates very nicely with SQL Server.
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
If you decide to move the back-end to SQL Server, which does sound like something that should be considered in your environment, then there are some things that you need to change in the Access application to make it work and perform well.  We've used this architecture on many projects and it works very well if done correctly.

I've written a PowerPoint presentation on this.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Armen Stein
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Forcing users to stop work just to do a daily backup is unnecessary, won't scale and obviously could be expensive. Are you sure one backup per day is really sufficient for your needs? If you have N users in the system for 24 hours a day then by only taking daily backups you are effectively saying that you will throw away N x 24 hours work if there is serious failure. That's a lot of hours lost and a lot of overtime and disruption incurred if you need to re-enter/recover the data.

I suggest you consider upgrading, e.g. to SQL Server, Oracle or any other of the popular DBMSs so that you can take more frequent backups without making users unproductive. You need to bear in mind that a database design made for a Jet/ACE database won't necessarily be the best one for a SQL DBMS, so a review and significant rewrite may be necessary in that case.
I've written a PowerPoint presentation on this.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

"Use NOLOCK" = tick the box for: "I want my data to be wrong!". RCSI has been a feature of SQL Server for 7 years you know! :)
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi dportas,

Thanks for your comment.  Certainly RCSI is another option if you are using a recent version of SQL Server, and I will update my slide deck to include it.  But I want to make a couple of points about NOLOCK (aka READUNCOMMITTED) vs RCSI.

1.  NOLOCK does not result in "wrong" data.  It allows the reading of uncommitted data, which is merely data that may be in the process of being updated at that exact moment.  In most business applications this is not very important, especially since we are using NOLOCK to populate read-only forms and dropdown lists.  This is even more true when Identity keys are used (as we do), since the key values themselves will not change.  Also, a well-designed database will also have referential integrity enforced, which will prevent required lookup and parent records from being deleted in the first place, so we don't have to worry about them disappearing after reading uncommitted data.

2.  There are both benefits and costs with implementing RCSI at the database level, including additional overhead and the need to provision the tempdb correctly.

3.  RCSI requires knowledge and cooperation of the SQL database administrator, while NOLOCK is done from the application side.  Sometimes Access applications use SQL Server databases that are administered by others, and in those cases an approach that can improve performance with the least overhead and cooperation may be the best approach.

So, the bottom line in my opinion is that the decision to use RCSI vs NOLOCK isn't automatic, it's something that should be considered for the scenario at hand.

Armen Stein
Anthony PerkinsCommented:
>>NOLOCK does not result in "wrong" data.  <<
The correct term is "dirty" data.  It is named that way for a good reason.

>>It allows the reading of uncommitted data, which is merely data that may be in the process of being updated at that exact moment. <<
You should also know that th NOLOCK hint is deprecated when used with a DELETE or UPDATE statement.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Thank you very much. That was helpful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.