Database crashes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys,
I can't be very specific I'm afraid about what's causing the crashes but I
have a database that is used by two (or occasionally three) people and is on
a network server and it falls over from time to time throughout the day. The
main form is complex and has quite a bit of code within the 'On Current'
event (as well as on other events) and it appears that the crashes tend to
occur when scrolling through the records. My suspicion is that the network
can't keep up with the user but that's just a pure guess. One of the users
is inclined to rush at everything and I've asked her to slow down a little
but that's not the proper solution!
Are there any things that are known to be troublesome to Access (2000)
databases on a network? Any clues as to what might help would be really
welcome!
Thanks,
Lee
 
The only thing I would say here is this,

"when scrolling through the records" with code within the 'On Current'
event,

I am guessing here that you have a form with little nav thingy at the bottom
of the screen and the user is scrolling through that way, I personally try
to avoid this practice as this what I see happening.

The form always starts at the first record, how often did the user want the
first record, the next thing the user does is filter to try and find what
they are after. I my view the better way is you don't goto this form first,
you have a search screen that allows you to narrow your search down
producing results to a listbox for the user to select the specific record
and then goto the form for that specific record, in doing this you are
reducing your processing trying to get what you want on the screen.

Just my thoughts.....
 
Lee,

At first glance, you may be right. Access is very demanding and chatty. Only
the most optimized network should handle it. Hardware should be of
reasonably good quality i.e. network cables, hub/switches, NIC etc.

You can do some experiment by trying to reproduce the problem using your
local drive. If you suspect that this *fast* user might have contributed to
this problem, you could test whether or not it is a network issue by moving
all your app to your local drive and let this *fast* user loose and see if
the app crashes.

Also, are you using the split FE/BE setup?


HTH,
Immanuel Sibero
 
I suspect your configuration is incorrect. Let me ask a couple of questions,
then I can be specific.

Is your database split? That is, is your data in one .mdb and the forms,
reports, queries, etc. in another .mdb with linked tables to the data?

Is the .mdb you open on the server or on your desktop?

Are the multiple users all sharing the same .mdb file?

What version of Windows are you running?

Do you have Office Service Pack 3 installed?

Please answer these questions, and I can describe to you how to correct
these problems.
 
Hi, thanks for your kind reply.
The database isn't split into a front end/back end. Because this is a new
database, I didn't want to install the front end on the user's C Drives as
changes/bugs were likely in the first few months - I'm trying to make it
easier to load changes just to the server.
The .mdb file is on a server and each user has a shortcut to it on their
desktop.
We run Windows XP and have Office XP SP3 installed.
We have a proper (large) network of users across different sites with a
number of different servers. I don't actually work in the IT dept but have
got into developing Access Databases for our local processes that aren't
covered by our mainstream systems.
Should I bite the bullet and split the DBase do you reckon?
Regards,
Lee
 
Thanks for the tip.
Certainly at present the database is centered round a main form which
displays all the records. I think the users like to be able to see
everything at once but if that's at the expense of stability then I might
need to think again.
If I can't get over the problem any other way I'll do as you suggest - thank
you.
Regards,
Lee
 
Thanks for your reply.
The hardware is good in that I work for a fairly large business that has
proper networking/server/mainframe capabilities.
I can't replicate the problem on a local drive - which answers the question
I guess!
Regards,
Lee
 
This is the problem, just as I suspected. You need to split the database,
and install the front end on the users' computers. Be sure your sharing and
locking is set up correctly. Not only are you doubling the network traffic
this way, it is easy for Access to get confused when multiple users are
opening and using the same .mdb. I understand your deployment issues, but it
is much easier to replace the .mdb files on the users' computers than it is
to keep repairing the database.

Set your Default Open Mode to Shared
Set your Default Record Locking to No Locks
Check compact on close in both FE and BE
 
OK thanks for this, I'll do as you suggest.
With regard to the 'compact on close' issue, I thought that this wouldn't
work with the BE where the tables are open via different users on a shared
network.
Best wishes,
Lee
 
It does work, that is the beauty of it. The BE opens when the first user
linked to it opens their FE. It will stay open until the last connected user
closes their FE.

One other thing, be sure you do your links using networking address instead
of drive letters. Not all users will have the same drive mapping.

Not E:\MyData\Folder
But \\MyServerName\MyData\Folder

I would also suggest you either acquire or write a relink routine so if the
users loose connection they can relink. I use a routine that traps an error
if the linked tables are not found, present a common dialog box to allow them
to point to the BE, and relink their tables.

I also assume you are distributing mde files for the front end.

Happy Trails
 
Back
Top