Automated Live Backup Help

[willknapp]

I could use some advice...

I'm developing an Access application that's integrated with Outlook. It's a custom CRM solution that automates trouble tickets based on incoming emails. As such, both Outlook and Access will be running perpetually.

This is a multi-user product - about 40 users total - so I've split the front and back ends, with the BE stored on a network server. The client, though, has asked for the data to be backed up every 30 minutes.

Given the number of concurrent users, there's no easy way to ensure that at a particular moment, no records are locked for editing (or is there a function I could write to test for this?).

Can I use CopyDatabaseFile method to save the back end as an .mdf file that can be restored in the event of a crash? If so, will there be a problem caused by any locked records?

Any and all advice would be greatly appreciated!

 

[pbaldy]

Personally, with that requirement and that many users, I'd lean towards SQL Server for the back end. There is a free version. It will handle higher numbers of users more comfortably, and the backups can be done without regard to users in the system.

 

[willknapp]

Thanks Paul - that thought has occurred to me. Unfortunately, I don't have a ton of SQL Server experience, and I'm just contracted for a short time to accomplish this.

I suppose, after finalizing the table design in Access, I could then transfer it to SQL Server; I'm guess maintaining it will not be that difficult. I have about a half dozen primary tables with another dozen or so support tables.

Normally, I'd jump at the chance to use this as an opportunity to expand my skillset, but I'm on a short contract, and I'm worried taking it on will slow my progress.

I'll put it to my manager - see what she thinks. I'm fairly certain she's not comfortable with a daily back up, so SQL Server may be the only logical solution.

 

[pbaldy]

I think your fear about not being able to copy the whole back end when users are active is valid. I haven't used CopyDatabaseFile, so not sure if it would work for you. You can play with DoCmd.CopyObject to see if you can copy tables while users are still in. The brute force method would be to run append queries to copy data from the live back end to a backup back end.

 

[willknapp]

Ugh! I hate those, "Why didn't I think of that?" moments...

The brute force method would be to run append queries to copy data from the live back end to a backup back end.

This is a great idea, especially given that I was just informed that SQL server is not an option. To be fair, this project is little more than a short term solution to help them figure out what they're going to want in an enterprise-wide solution, so they're looking to keep things relatively simple for the time being.