Auto Temp Database

Posted on 2014-02-24
Last Modified: 2014-02-26

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?
Question by:cddl
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 250 total points
ID: 39884186
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.
LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39885483
<< 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.

LVL 36

Expert Comment

ID: 39887282
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.
LVL 57
ID: 39887323
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.


Author Comment

ID: 39890442

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?

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question