Link to home
Start Free TrialLog in
Avatar of dpalyca
dpalyca

asked on

Table Corruption

What causes table Corruption?  I have a network mdb file which 23 people use.  It is being corrupt and the repair utility won't repair it.    I am at a loss.  If I split the database, and make the front end an mde file, will the back end still become corrupt.   help
Avatar of joekendall
joekendall
Flag of United States of America image

If you have the tables and the forms stored in one database file, you should split them into a front end and a back end.

Table corruption could definitely happen if the database is on a network and someone closes down there computer without closing Access first or something similar. The network could go down and cause the problem also.
ASKER CERTIFIED SOLUTION
Avatar of IDAnderson
IDAnderson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IDAnderson
IDAnderson

By the way, try the method that I have detailed on this link to repair your database.

Make sure that you have backed up your existing database before you do it.

https://www.experts-exchange.com/questions/21225623/Security-set-and-password-forgotten-Access-2003.html

If it doesn't work, open a fresh database and import all of your tables, queries, forms etc.. to re-establish the database.

regards,

Ian..
IDAnderson is correct-- make it a split DB-- but keep the front end an .mdb.  There are many ways using code to control toolbars so that security against people changing the front-end will be greatly enhanced without having to deal with Access Security.  Control it all with VBA.

Then, what I do, is create a shared folder on everyone's root C: drive.  I call it KFRONT for instance, if the company's name starts with a K.  Everyone's folder is named the same, but when it is shared, it is named KFRONT_BOB (or _USERNAME, for each user).  Then, I create a location on the server where a clean, "compact & repaired" version sits ready to go.  I write a batch file (DOS commands) that goes to each user, deletes the file, then one that copies the fresh front end mdb file to their location.  So, for each user you have a delete line and a copy line (del \\KFRONT_BOB\dbfront.mdb /y) followed by (xcopy \\SERVER\Database\FreshFront\dbfront.mdb \\KFRONT_BOB\dbfront.mdb /y)

Now, you give that batch file a "green-arrow" icon so it's easier to spot, then have the office manager run that batch file every morning.  That way, everybody gets a fresh program every single day.  This method works wonders for me and it cuts down on individuals' front-ends getting corrupted, especially if you have local tables in your front-end database.  Depending on your business rules, there are times when having local tables can't be helped.  If you have a growing database and you know that you are going to upsize to SQL Server down the road, eliminate all LOCAL tables and put in the extra time to code the front-end in a way that will work with a backend database where all data resides there.  You'll be glad you did later.

 23 users is a lot to keep track of-- I hope this helps