Solved

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

Posted on 2000-04-14
22
331 Views
Last Modified: 2008-03-03
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
0
Comment
Question by:mts9
  • 8
  • 3
  • 2
  • +6
22 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 2717689
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
 

Author Comment

by:mts9
ID: 2717690
Adjusted points from 100 to 200
0
 

Author Comment

by:mts9
ID: 2717696
Edited text of question.
0
 

Author Comment

by:mts9
ID: 2717702
I've tried to import everything into a new database--still no luck--Thanks Mike
0
 

Author Comment

by:mts9
ID: 2717706
I've tried to import everything into a new database--still no luck--Thanks Mike
0
 

Author Comment

by:mts9
ID: 2717708
Edited text of question.
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 2717816
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
 
LVL 7

Expert Comment

by:Believer
ID: 2717884
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
 
LVL 2

Expert Comment

by:paulinak
ID: 2718287
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
 

Expert Comment

by:carbins
ID: 2719958
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
 
LVL 1

Expert Comment

by:CLoprinzo
ID: 2723062
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Expert Comment

by:Believer
ID: 2723100
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
 

Author Comment

by:mts9
ID: 2723240
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
 
LVL 14

Expert Comment

by:mgrattan
ID: 2723395
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
 

Author Comment

by:mts9
ID: 2723414
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
 
LVL 7

Accepted Solution

by:
JimMorgan earned 200 total points
ID: 2724202
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
 
LVL 2

Expert Comment

by:paulinak
ID: 2724239
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
 

Author Comment

by:mts9
ID: 2724286
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
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2724691
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
 

Expert Comment

by:CAMELBUCK
ID: 2731983
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
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2732837
Jesse:  Welcome to EE.  Glad to see another Access/VB expert in the topic.

Jim
0
 

Expert Comment

by:CAMELBUCK
ID: 2732959
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

12 Experts available now in Live!

Get 1:1 Help Now