• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Auto Temp Database


Currently I have a POS system that runs of 2 access databases, one for sales and one for configurations (price, printer, vendor etc).  Some of the POS applications has index errors or corrupt databases once in a while.  I need some assistance with the following:

1. I want the database.mdb to create a database.mdb_temp every hour or so in a different location on the computer.

2. As the file create temp database every allotted hour, I will want the oldest database to be deleted so storage space doesn't become an issue.

Anyone can help with this?
2 Solutions
Jacques Bourgeois (James Burger)PresidentCommented:
Create a small application that copies the .mdb and does the cleanup as you want it.

Make sure to have some error trapping in there. In my experience, you can usually copy a .mdb file no matter if it is being used or not, but it fails from time to time, possibly if a write or a transaction is occuring at the same time as the copy.

Use the Task Scheduler on the computer where the .mdb resides to run your application at the interval that suits you.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< In my experience, you can usually copy a .mdb file no matter if it is being used or not, >>

 While often you can, it's a very dangerous practice.  While it's rare to end up with a corrupt DB, you can easily end up with a DB that's in an inconsistent state.

 There is no safe way to make a copy of a JET DB that is in use other then one method:

 You build something into the app to establish a quiet point (freeze all operations) for all users, copy all the data out, then let things get going again.

  You can with backup software which has an open file option establish a quiet point at the I/O level, but you still can end up with a DB in a inconsistent state.

  Example, you have a user that is updating a large table.  If the backup runs in the middle of that operation, you may end up with a table that is partially updated.

  A file copy though is not going to cut it because locking is never done on the DB file itself at the file level.

  File copy is fine if all the users are out and the DB closed, but not while it's in operation.

I would investigate why the BE is corrupting.  Despite all the bad press, Jet/ACE are actually pretty stable when used correctly.

1. For starters, the app MUST be split.  There is simply no getting around this.  The BE should contain ONLY tables and the FE contains everything else.
2. Each user should have his own personal copy of the FE that is installed locally on his C: drive.  You could run it from a network drive but that adds unnecessary overhead by forcing Access to keep bringing objects across the network.
3. Compact and Repair on a regular basis.  The frequency will depend on how much add/delete activity happens each day.  I have clients who compact during lunch as well as in the off hours.
4. Access is the canary in the coalmine when it comes to network issues.  If anyone on the network has a bad nic card, Access will have intermittent failures and that can corrupt the data depending on what is happening when the blip occurs.
5. Use a wired network.  Wireless are much more susceptible to disruptions.

If you have an unstable network and can't fix it, you should upsize to SQL Server which is far less sensitive to network interruptions.  Depending on how your application is designed, you may have little to no work to convert or you could have to change all your forms and queries.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
If you have an unstable network and can't fix it, you should upsize to SQL Server which is far less sensitive to network interruptions.  

 I think I would just upsize anyway.  In this day and age, there's very little reason not to.

cddlAuthor Commented:

That was my thought but the owner of the POS software is not upgrading anymore.  I can use a migration tool but I have 10 databases that reference each other.

What do you think?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now