MS ACCESS Working in a multi-user environment -- Save Message

I have a multi-user access 97 database running on a Novell network (Windows 95 machines) that has been running fine for a year now other than routine maitenance (archiving data, repairing and compacting). Within the last few days, my users have frequently been reporting cases where they get the message "Form <form name> has been changed by another user. Do you wish to save your changes and overwrite the other user's changes" (this is probably not the exact message, but close). Anyhow, I've repaired and compacted the database several times, I've performed a decompile on the database, I've copied the database to another server and renamed it thinking there might be a corruption, and I've totally rewrote the code in the form they are getting the save message in-----When I do the repair/compile the database seems fine for about a half hour -------Any Ideas?---I know I could probably save this as an .mde file, but I don't want to do that at this time---Thanks For Any Help--Mike
mts9Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
JimMorganConnect With a Mentor Commented:
Mike,

I'll bet there is either a new user for this database or an old user who has been 'educated' from a new book or comments seen on a WEB site.

One problem could be nothing more than when a user is finished entering or changing data in the form, they click the "Save" icon.  This 'saves' the current form.  It doesn't require going into 'Design' mode to do this.  If someone else is using the same form concurrently, they get a message that the form has been changed - usually when they close the form.  All Access is doing in comparing the date and time stamp for the form in the DB with the memory copy on the user's workstation.

I feel that it is time to split your DB into a backend data only DB which resides on the server and the rest DB which is on each user's local workstation or home folder.  The workstation would be better.  The data DB is linked into each user's local DB.  You should notice an improvement in speed for everyone if you do this.

If you need any help doing this, just ask me.

Jim  
0
 
brewdogCommented:
what about creating a new blank database and importing everything? you may have some corruption in a system table, and importing into a new db lets access start with a fresh set of system tables.
0
 
mts9Author Commented:
Adjusted points from 100 to 200
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
mts9Author Commented:
Edited text of question.
0
 
mts9Author Commented:
I've tried to import everything into a new database--still no luck--Thanks Mike
0
 
mts9Author Commented:
I've tried to import everything into a new database--still no luck--Thanks Mike
0
 
mts9Author Commented:
Edited text of question.
0
 
mgrattanCommented:
A few possibilities:

1.  It's possible that the form itself didn't change but another user has a record open in the same 64k page.  In that case, the entire page of records is locked and the user who saves last will get the error message.  Possible solution:  change record locking to pessimistic (edited record is locked) in the form and the user will get an error message that the record is locked and to try again later.

2.  Novell option for number of open files allowed is set too low.  Default, I think, is 1000.  Ask your network administrator to bump it up to 6,000 or 8,000.  This will also fix another glitch when running on networks--if the setting is too low you will get errors in trying to run large delete, append, or update queries.

3.  A user in the database somehow opened the form in design mode and actually DID make changes to it.  This is most likely NOT the case, but thought I'd mention the possibility.

4.  Access is getting confused by network traffic.  I've had this error message crop up before when I was the ONLY user in the database but the MDB was stored on another server that was at max capacity.
0
 
BelieverCommented:
If you want to see a very recent question about Novell record locking, go to: http://www1.experts-exchange.com/bin/Q.10327491

It'll only cost you 25 pts...
0
 
paulinakCommented:
I've had this kind of problem before when I was doing clever tricks with filters and such.  Appears you are sharing the forms over the network, so could this be a possibility?
0
 
carbinsCommented:
Paulinak is on the right track.

The problem is not related to locking, nor is it related to Novell. This problem can be replicated on a SMB (M$ or SAMBA) Network by opening two copies of the form, setting a filter on each client, and then exiting the form.

The solution is to use
     docmd.close ,me.name,acSaveNo

under the 'close/exit/get out of here' button, and disable the 'close window' button and control box so that users MUST execute the 'don't save me' code.

Cheers,

Brenton Carbins


0
 
CLoprinzoCommented:
Carbins has the quick fix, but you should separate the data from everything else and create a front-end shell.  This shell should be placed on each PC or somewhere where only one person can use it.  The shell will have linked tables to the data and that way any changes to a form will not affect anyone else.
0
 
BelieverCommented:
CLoprinzo: Welcome to EE's Access forum! We have an informal policy here of posting *comments* instead of answers in order that the question remains open for discussion until the person who asked the question decides they have the answer they are looking for.
Would you please consider changing your proposed answer to a comment? Thanks!
0
 
mts9Author Commented:
I agree with the shell option and also with Carbin's acsaveno "quick fix". I have considered using acsaveno before, but it still doesn't explain why this has happened after a year of being in production. Maybe I'm wrong, but I think these options would just be avoiding the true problem.  If there is a way I can give you each some points let me know, but I still want to leave this open for a true answer as to why this is happening. Thanks for the help so far---Mike
0
 
mgrattanCommented:
Did you check with your Network administrator yet?  It's very possible that it is a network issue--either the File Locks setting is too low or there is too much traffic on the server where the database resides.
0
 
mts9Author Commented:
I agree with the shell option and also with Carbin's acsaveno "quick fix". I have considered using acsaveno before, but it still doesn't explain why this has happened after a year of being in production. Maybe I'm wrong, but I think these options would just be avoiding the true problem.  If there is a way I can give you each some points let me know, but I still want to leave this open for a true answer as to why this is happening. Thanks for the help so far---Mike
0
 
paulinakCommented:
Hi, Mike

If you have determined that your users are stepping on each other's forms and this is causing the problem, you have three approaches I can see.  
1) Split the database as JimMorgan and CLoprinzo have suggested.
2) Prevent or handle the situation in code in the shared database.
3) Explain to the user(s) what they are doing and why they might see this message.  Tell them how you want them to behave in the application, and trust them to do it. Ha. Ha. ;)
0
 
mts9Author Commented:
Thanks to all that have helped. I'm going to try all of your suggestions (save = no, the Novell record locking increase and the splitting of the DB. Heck, one of them is bound to work and if I can give some of the others some points, will someone let me know how.--Thanks again Mike
0
 
JimMorganCommented:
Mike,  If you ever want to split points, you should ask Customer Service to answer the question.  They will refund your points.  Then you ask the particular expert(s) a question and refer to this question #.  Award them the points that you want.  Be sure to add the question number or better yet, a link to this question - http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10328619 

Jim
0
 
CAMELBUCKCommented:
Mike,

Jim is right. It is time to split the data base. You may even want to do some editing to your menu bar. When you split the data base, you will have a back end and a front end. The back end will be your tables, and the front end will include all your queries, macros, reports, and forms.

Befor you do this, it would be wise to make some changes in the Set Up properties, to lock out any possiblity for editing or design mode. This is what I have done in my own work. Of course, you will want to be able to access the design mode, so, what I have have done, is created a PGM command button in the Administration screen for all my programs.

This button is password protected and opens the "PGM" form, where I have my programming tools. One of the command buttons there runs a command which can be set up either by a macro or code. The following code is an example of this click procedure:

Sub tools_Click()
On Error GoTo tools_click_Err

    DoCmd.RunCommand acCmdStartupProperties


tools_click_Exit:
    Exit Sub

tools_click_Err:
    MsgBox Error$
    Resume tools_click_Exit

End Sub


When this command takes place, it opens the Start Up menu options. I check everything in the start up menu options. Then exit the program and restart it. I now have design mode available.

Remember, when you split the data base, one you should not move the back end. Two, you may do a simple copy of the front end and place it onto the client's desktop. You will need make a copy of the front end for all clients.

Any changes made to this front end will need to be redistributed.

I have basic modules designed for this. If you have any questions or would like a sample please let me know.

Jesse

jwalter@valleyip.net
0
 
JimMorganCommented:
Jesse:  Welcome to EE.  Glad to see another Access/VB expert in the topic.

Jim
0
 
CAMELBUCKCommented:
Jim,

Thank you. I would not exactly say that I am an expert, butI learn quickly and have a wide range of skills. I do have enough knowledge and have been developing marketed items for my local area.

I just know how difficult it seems to be when your still learning the basics. So, I want to help where I can, plus, I may learn something new in the process.

Thanks again.
Jesse
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.