ACCESS NETWORK PROBLEM 3

[marchristensen]

I have the following problem with an Access database which is on a network shared drive. Occasionally the user looses the network connection in the middle of doing Access data entry. The Access file becomes wrecked, with a message saying it needs to be repaired. Repair does not work. Deleting the .ldb file does not work. Only thing that works is restoring from backup, which causes loss of recently entered data.

Any advise on how the database should be set up to minimize this problem would be appreciated.

 

[tlbroadbent]

Check the one of the following depending on which version of Access you use. The article tell how to prevent damage or corruption.

ACC2000: How to Troubleshoot/Repair a Damaged Jet 4.0 Database

http://support.microsoft.com/support/kb/articles/Q209/1/37.ASP

ACC97: How to Repair a Damaged Jet 3.5 Database

http://support.microsoft.com/support/kb/articles/Q279/3/34.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[marchristensen]

Many thanks for the quick reply. I did have this info and have tried the compact and repair - does not work.

I was not asking for a method to fix the file, but rather a scheme to minimize the "damage" - I was thinking of temporary working files on the local drive that are appended to the shared database file. I'm sure there are other methods to deal with this common problem. So the question really is - what is the best way to set up an Access database in a networked, multi-user environment.

 

[marchristensen]

Any other info on Access in a multi user network environment would be appreciated.

 

[maverick]

I have found that 2000 is not as stable as 97...

files from 2000 seem to corrupt faster! easyer!

If I were you, I would work on the network stability first,
then create a front-end and back-end database from what you currently have, it seems to be more stable if front-end is local and a crash happens, network or PC...

Hope this helps


"Hacker by Heart"
saenzinc@email.com

 

[tlbroadbent]

I concur with Maverick. A stable network and workstations should be high priority. In my position, I can't control the network but I can help stabilize the client environment. Make sure all users have the latest MDAC and Jet for the version of Access you use. Bring all PCs up to the same service packs for Office.

Splitting the database is critical. Create a back-end for data and front-end for forms, reports, modules, macros, etc. If the back-end database is large and can be easily split, consider multiple back-ends.

Compact back-end databases regularly. I schedule runs for after hour (2:00AM for example) or weekend compacting.

The articles I referenced previously contain a section titled, "Steps that can be taken to prevent corruption." A search of Microsoft found these additional links.

ACC2000: How to Minimize Database Corruption When Using Microsoft Jet 4.0

http://support.microsoft.com/support/kb/articles/Q300/2/16.ASP

ACC97: How to Minimize Database Corruption When Using Microsoft Jet 3.x

http://support.microsoft.com/support/kb/articles/Q303/5/19.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[marchristensen]

Many thanks for your thoughtful answers. I will implement the kind of splitting you suggest and also look into desktop updates. Thanks again.

 

[ErikZ]

Ok, just to clear this up (Since I have never split a database). Splitting a database will make the back-end immune to problems with teh front end?

So if you're logged in and your coputer crashes, you shouldn't have a problem? The problem I'm seeing here is that the database is kept on a network drive. This drive goes "Down" at least once a week. Although there hasn't been any corruption problems, I'm sure it's just a matter of time.

 

[marchristensen]

You've been very lucky. My experience is that if the user is in the database and either the network connection fails or the user turns off the PC without exiting out of Access the database becomes corrupt 100% of the time. User of course is very unhappy and so am I. Will report back if I am able to fix.

 

[tlbroadbent]

Splitting doesn't make the back-end immune from corruption but does reduce the probability. In our experience, splitting seems to have significant impact on the back-end corruption rate. We've gone months without corruption on most databases.

Of course, we compact regularly and have a very stable network which also reduces corruption problems. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JUANCARLOS]

Since I improve my database with the SendObject function I have tree colapses in a month.
This function hangs up the front end, what can I do? replace this function, with what?, eliminates this options?, what can I do?. help.... Thanks in advance

 

[Guest_imported]

We are having the same problem. We keep loosing the database in a multiuser enviroment. We have split the backend and are still having the same problem. However, we do not have the frontend loaded on each individual machine, we are using it in a shared file.

 

[tlbroadbent]

Running the front-end off the local PC should help eliminate some corruption and improve performance. However, it is not a cure-all. We run several systems with a shared fron-end and don't experience much corruption. In my experience, network and PC instability are major contributors to Access DB corruption. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JUANCARLOS]

When the server hang ups when the user is loading a form data entry, will colapse the database, I determinate that the function send object is the cause.

Please help, any have a function, software or anything can replace this function. HELP!!!!!

jchr

 

[Paul7905]

Thought i'd toss this in here.

For shops that can afford it, putting in a Citrix server and running the entire access app on the citrix server solves all the network issues in my experience. since access is running on the server and only keyboard strokes and screen paints are crossing over the network, the network can experience all sorts of problems, even crash, but when it comes back up, the screen you see on your terminal is precisely where you left it when the problem transpired (unless the Citrix server crashes of course, which has not happened to us in over two years). In other words, in two years, i have never experienced a corrupted .mdb running access 2000 on citrix metaframe.

paul

 

[Quehay]

Marc,

I developed and support over 30 distinct Access db's on a network. We use Access 2000, and there have rarely been any corruption problems (2X in 18 months). However, once is too many times if you've got critical data and users that count on you to manage things. Unfortunately with Access there's always the possibility that an .mdb file will be corrupted at any given moment of the day. The few corruption cases I've experienced haven't been solved by compact & repair (*There's a KB article about the significant differences between C & R from within the db versus doing it via external code.)

Here's my strategy:

1. Put the backends in one common folder on the network.

2. Use a VB executable that I wrote (using the Windows.Scripting FileSystemObject) and the Windows Task Scheduler I do a folder copy of the back end folder 3X daily, just copying the entire folder and renaming the folder with a datetime tag. (Our network is backed up, but to retrieve a restored file is a hassle, and, if the tapes have been shipped, it's a significant expense.) You could also just write a VB script file and do this. (I don't have the code on hand here, or else I'd put it in--if you'd like it just post
to that effect and I'll submit it from work).

This way no one can lose more than 2-3 hours work at a time and you can easily restore a file without a network engineer's help. If users are in files there's no problem copying the .mdb files--you'll have .LDB's in the backup folder, but these can be deleted once the files are copied.

If you have just a few users & db's working on PC you can also create a code routine within the db itself, using a reference to the Windows.Scripting library, that will copy the BE out to a backup folder on that PC (again I don't have the code here--ask and I'll post tomorrow).

Hope this is helpful!

 

[Paul7905]

Quehay,

please post that code and details for implememting it. (i haven't a clue how to use the scheduler to run a script)

thanks

 

[marchristensen]

Quehay -
Yes please post code to make periodic copies of the database file. Also tell exactly how to do this for few users. How do you put the routine within the database itself. I am so impressed with your having such few problems. My db has only two users and it becomes corrupt on a regular basis. Compacting and db repair will not make it right. I appreciate any help you can provide.
Thanks.