Go Premium for a chance to win a PS4. Enter to Win


Auto Temp Database

Posted on 2014-02-24
Medium Priority
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 1000 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 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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 40

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 58
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

782 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