Corrupted MS Access db

Question

Yesterday I received an error message with an ok only form indicating the db was in an inconsistent state and that a recovery would be attempted but after clicking ok and I received another error indicating the db couldn't be repaired or was not a Microsoft Access Database file.  I tried using the JetCompact 4.0 utility but was unsuccessful.  I also tried to transfer the tables, forms, macros and other objects to a new db but was unsuccesful.  I then was able to work with my IT team to get a back up copy of the database from monday.  We worked in the database yesterday and everything seemed fine.  This morning when we opened the database we experienced the same issues as yesterday.  The db is automatically set to do a compact and repair when the last single user exits the db.  I am very concerned because I do not want my co workers to lose work but I am unsure what to do to prevent this from happening again.  My IT team has been able to restore the db from monday again but I need to come up with a plan to try to prevent this from happening again.  Just some additional background info:

1.  My company recently migrated to Windows 7.  

2.  The db is about 260 MB with atleast 1.3 million records.  Is size potentially an issue here? 

3.  I do not have the front end of the database separated from the back end.  We operate in a thin client environment.  How would I split the db into front end and back end components in a thin client environment? 

I was just wondering what your thoughts are on this and waht steps I should take to prevent this from happening again? 

Thanks,

Terry

Answer 1

How would I accomplish this in a thin client server based environment?  By definition there is no local hard drive to store the front end. 

 

We run with Citrix so this is how we do it. 

1. Backend resides on a File Server
2. Frontend resides in a folder on the same file server.
3. When someone clicks on the icon in Citrix to start the database, it

    a. Runs a batch file which makes a copy of the frontend and places it in a temp folder on the particular Citrix Server. 

    b. If the folder doesn't exist the batch file creates it first.

    c. The batch file also runs a registry file to register the location as a trusted location so the database will open with code enabled.

    d. The batch file then opens the database file.

Here's the contents of one of our batch files and then below that a copy of the contents of the registry file (.reg) that runs:

BATCH FILE:

@echo Please wait while the version of the database you requested is updated.

@echo off

reg import e:\Access2007TrustedLocation.reg

MD E:\BKY_temp%username%

copy R:\FE\Current\Consolidated_Reports.mdb E:\BKY_temp%username% /y

Start /max "E:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "E:\BKY_temp%username%\Consolidated_Reports.mdb"

exit

REG FILE:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location0]

"Path"="E:\bky_temp\"

"AllowSubfolders"=dword:00000001

"Description"=""

"Date"="9/25/2012 1:00 PM"

Answer 2

Do I store individual copies of the front end for each user out on the server?

Answer 3

How would I accomplish this in a thin client server based environment?  By definition there is no local hard drive to store the front end.

Answer 4

Yes, it NEEDS to be split.  And a copy of the frontend on each user's computer.  If you need to update you can use a frontend auto updater like the tool on my website I provide for free.  See my profile for the link to my website.

Do a restore from a backup again.  This time create a brand new database shell and import (don't copy and paste, but IMPORT) ALL of the tables into it.

Then make a copy of it and then delete all of the tables from one copy and all but the tables from the other copy.  Then link the tables from the backend to the frontend from within the frontend using the External Data tab. 

You might want to read this too:

http://www.kallal.ca/Articles/split/index.htm