Solved

Multi-users on Access 97

Posted on 2002-07-25
16
331 Views
Last Modified: 2008-02-01
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.  
0
Comment
Question by:Lennie2000
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 5

Accepted Solution

by:
KMAN earned 200 total points
Comment Utility
Do you have the front-end on the network as well?  I highly recommend the each user have their own copy of the front-end on their operating system, and NOT use a shared copy.  It reduces lock issues and will improve overall performance on the desktop.

I created a simple batch file program that I execute on all the desktops from the Start-Up folder, that automatically renames the existing front-end file and replaces it with a 'fresh' copy from a network location.  This helps avoiding: the need to compact (since the copy on the network is not bloating), and allows the development of the front-end to be distributed 'automatically' each time the PC is started.

GL, K
0
 

Author Comment

by:Lennie2000
Comment Utility
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
0
 
LVL 5

Expert Comment

by:KMAN
Comment Utility
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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
0
 

Author Comment

by:Lennie2000
Comment Utility
Could you send me that batch file you have, or explain to me how to build it.  Thanks.

LENNIE
0
 
LVL 5

Expert Comment

by:KMAN
Comment Utility
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
0
 

Author Comment

by:Lennie2000
Comment Utility
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
0
 

Author Comment

by:Lennie2000
Comment Utility
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 5

Expert Comment

by:KMAN
Comment Utility
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
0
 

Author Comment

by:Lennie2000
Comment Utility
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
0
 
LVL 5

Expert Comment

by:KMAN
Comment Utility
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
0
 

Author Comment

by:Lennie2000
Comment Utility
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:

http://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
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
0
 

Author Comment

by:Lennie2000
Comment Utility
KMAN,

Were you able to find that batch file?

Lennie
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now