Link to home
Start Free TrialLog in
Avatar of Lennie2000
Lennie2000

asked on

Multi-users on Access 97

Hi, I'm running a multi-user Access 97 database on a network.  I've placed all the files onto the network with user-level security.  I have a front and back end. There's about 7 people with permission to write and about 10 people with read permission.  

My problem is that my front end, which has everything except the tables, keeps crashing at least once a day.  I run regular maitenance on it by compacting and sometimes decompiling and compiling.  The file is only about 1.5 megs.  It's a very simple database.  Now, I've heard that there shouldn't be more than 10 people using it at the same time or I may run into may problems, but I only usualy have about 7 logged on at one time.  

I'm getting errors like, unrecognizable database format, or "reserved error(-3034)", "Unexpected error (35012)", "Records can't be read; no read permission" (this one happens when i try to compact and I am logged on as the administrator).  If anyone knows how to fix or prevent any of these problems, please let me know.

I'm wondering what are some possiblities of why my program would be crashing.  Would it have anything to do with my code?  Has anyone experienced problems when two or more people running different queries or appending to tables at the same time?  Basically, I want to know how I can proof and test my program a little better.  
ASKER CERTIFIED SOLUTION
Avatar of KMAN
KMAN

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 Lennie2000
Lennie2000

ASKER

KMAN,

Yes, my front end is on the network.  I just thought it made it easier when it crashed to replace the program as well as updating the program.  

Ok, the batch file sounds like a good idea, but there's one problem.  First, I've set user-level security using a seperate MDW file for just this database.  So I've created a special shortcut on everyone's machine to access the program with the MDW file so that it doesn't affect other access databases.  I guess I could just replace the shortcut.  But the problem is that not everyone reboots their computer everyday, they just usually lock it.  I do that alot myself.  So would there be another way to distribute the program?  

Do you have any ideas about those errors I've been getting?  Thanks.

LENNIE
You could use the built-in Windows task scheduler to execute the same batch file every night at a specific time.  That is what I have done for the "lockers" but they need to make sure the front-end app is not open when they leave.

If you aren't familier with that resource:
Win2000/XP: Open Control Panel and navigate to the Scheduled Tasks object. It is fairly easy to understand from there.
WinNT/98: Locate Scheduled Tasks in Explorer's Folder list, near its bottom.

The crashing is probably due to the use of one front-end by 7 users.  Lots temp data being written, record locking... Re-Deploy and you will probably lose the crashing.

K
KMAN,  while I agree with you IN PRINCIPLE, I have an example, which has been running WITHOUT PROBLEM for almost 2 YEARS, where both the Front-end and the Back-end (both Access 97 MDBs) are on the Network drive, accessed (pardon the pun) via a short-cut, on each users desktop.  There are more than 15 potential users, though how many are actively using the Front-end at any one time is totally random.  As I said, there have been NO examples of corruption or crashes in more than 2 YEARS !!!!

Arthur Wood
Could you send me that batch file you have, or explain to me how to build it.  Thanks.

LENNIE
Could depend on the speed of the network (mine was only 10MB) File server (Mine was Novell Netware 4.11) and the compelexity of the app, design of the app, etc..

My front-end (mildly complex, over 100 objects total) was better off when executed locally because I pre-opened a bunch of tables and forms on startup and therefore speed improved.  I've only had a couple of instances where locking became an issue, but never a crash or corruption in 5 years.

Arthur - Try testing the local front-end approach, perhaps there could be benefit.

Lennie - Do the crashes occur during a specific event or just randomly?   Try tracking what the users are doing when a crash occurs.

GL, K
It just crashes randomly.  There isn't one specific function that will crash it.  It's quite difficult to track what is going on with the other users when it crashes.  And when I do find out what has happened, it's different every time for the same error.  

As Arthur has pointed out, he's ran it on a network with no problems and so if I try doing it locally and it still crashes, what else can a look at or try to do?

LENNIE
Also sometimes when I get errors, most of the other functions still work, so most users that are still in the program can still most functions, but users entering can not.  So it's hard to track who it may have been that cause the crash.
Hard to say when it is so random.

The first thing is to is make sure you have the latest MS Access program and Jet Engine updates.  If you are using ODBC, then updated drivers, etc..

Aanother thing, obviously, is to try to narrow down for what/when crashes happen.  You could add logging to the app to record events which may help determining your trouble's source. Improved error trapping... Make sure DAO and ADO instances are destroyed...


K
I'm fairly new to access and VB so I'm not quite sure how to go about doing what you've suggested.  Can you elaborate for me.

BTW, can you hook me up with that batch file.  Thanks.

LENNIE
I'll have to get the batch file tonight.

As far as the software updates, go to http://office.microsoft.com/ProductUpdates/default.aspx and work you wat through to get the latest updates.  Make it a practice to keep your software up to speed.

Logging can be done in many ways, within the database in a table, or externally, to a text file.  The main idea is to right to the log in such a way that records the date/time stamp of the event, values where appropriate and a description of what the event is.

To write to a text file, use the Open File for Update method.  For adding to a table, execute an Append query (Insert Into SQL).
Access help has decent examples of these.

Another good site for examples is http://www.mvps.org/access/index.html

K
KMAN,

Ok, I'll try what you've suggested.

Also, I don't know if you know anything about count functions, but I got another question open that someone had started to answer but he's dissappeared and I haven't had anyone else answer my question for 3 days.  So if you could help, here's the link to the question:

https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msaccess&qid=20326387

I'll still double the points, as I've said in the question, if I can get what I need by the end of tomorrow.
Thanks!

LENNIE
Avatar of Jim Dettman (EE MVE)
Lennie,

<<My problem is that my front end, which has everything except the tables, keeps crashing at least once a day.  I run regular maitenance on it by compacting and sometimes decompiling and compiling.  The file is only about 1.5 megs.  It's a very simple database.>>

  More then likely, your FE has become corrupt for some reason.  You should build a new FE by creating a new MDB file and importing all objects into it.

  Also, unless your trying to fix a *specific* compile problem, you should not be decompiling on a regular basis.  There are many reasons for that but leave it at that the switch was included for a very specific purpose.  

<<  Now, I've heard that there shouldn't be more than 10 people using it at the same time or I may run into may problems, but I only usualy have about 7 logged on at one time.  
>>

  This is not true.  A well designed Access DB can handle 30 users without significant problems.


<<KMAN,  while I agree with you IN PRINCIPLE, I have an example, which has been running WITHOUT PROBLEM for almost 2 YEARS, where both the Front-end and the Back-end (both Access 97 MDBs) are on the Network drive, accessed (pardon the pun) via a short-cut, on each users desktop.  There are more than 15 potential users, though how many are actively using the Front-end at any one time is totally random.  As I said, there have been NO examples of corruption or crashes in more than 2 YEARS !!!!
>>

  That suggests that something has changed.  New stations added? Change to the server? New software loaded?  New hub or router added?

HTH,
Jim.
to JDettman,  I have NEVER experienced any of the problems that have been described here.  My comments were to that effect, and not meant to indicate that suddenly I am now having such problems.  So you comment, in your last message, is irrelevant.

As for not having seen any such errors, I am eiother VERY LUCKY, or my setup is completely different from the setup that others (who do have such corruption problems).  Either way, I do not plan to change anything about how my apps are accesed or how the network is configured.  Why look a gift horse in the mouth?

Arthur Wood
Arthur,

 Sorry, wasn't paying enough attention to who was replying to what.  I thought that was Lennie's comment.  Clearly I made a mistake.

  I would certainly agree that you can run a single FE copy from the server for all users and not have any problems.

  However you do suffer a performance penalty for doing so.  If you have a fast network, few users, or a simple app (or some combination there of), it may be quite acceptable.  But I myself have never gone that route as it's a simple matter to setup a system where by FE's can be downloaded to the client easily.  In my mind, the less network traffic the better.

Sorry once again for the confusion...
Jim.
KMAN,

Were you able to find that batch file?

Lennie