Solved

Auto Temp Database

Posted on 2014-02-24
5
383 Views
Last Modified: 2014-02-26
Hello,

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?
0
Comment
Question by:cddl
5 Comments
 
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.
0
 
LVL 57

Accepted Solution

by:
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.

Jim.
0
 
LVL 35

Expert Comment

by:PatHartman
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.
0
 
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.

Jim.
0
 

Author Comment

by:cddl
ID: 39890442
JDettman,

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?
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

810 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