Solved

Auto Temp Database

Posted on 2014-02-24
5
375 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 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now