Multiple Users Issue

[leerlap]

I'm having some trouble getting a small database up and running in a multi-user environment. I've read many of the posts here about setting up a fe and be, but this seems to make sense to me only if a lot of folks will be using the database. We will have, at the most, 4 people working on this database at the same - usually no more than 2. So, i've put the database in centralized location on a shared network drive and set the permissions in Access under Tools | Options | Advanced on each of the computers so that "shared" is the default open mode, "edited record" is the default record locking and open database using record-level locking is checked. I've tried variations of this, but nothing has worked (and this one seems to make the most sense to me). What happens is when the the database is open in two places and one person tried to put in some data in a form and save, the error msg "You do not have exclusive access to the database at this time. Your design changes will not be saved." Strangely enough the data actually seems to get saved, but the error msg is making folks nervous. Any ideas??? Thanks for your help...lee

 

[GumbyD]

I work on similiar database setup and here is how it is setup. Try it and see if it works for you:

 

[leerlap]

GumbyD - yeah, i've tried that - with the same result. I'm really baffled...So, with that configuration you're able to have multiple users work on the same database and save new data into different records?

 

[GumbyD]

Yes - If you are working on Access 2000 or later than you may have passive locking problems, but they should only be an issue if someone has a form, report, macro(?), or module open in design mode. You may want to disable Opportunistic Locking on the server - I have not seen it cause the error you are getting (it usually causes database corruptions), but it should not hurt you and may save you some headaches later on. Here is the info:

Microsoft has discovered an issue where opportunistic locking can increase the possibility of Jet database corruption when the file is shared by two or more clients on a network file server. This issue applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, (we have yet to confirm that this is an issue with Microsoft Windows XP), and Novell file servers that support opportunistic locking. This issue is still under investigation.

Opportunistic locking is enabled by default on Windows NT and 2000 file servers. To disable opportunistic locking on a Windows NT or 2000 file server, you have to use regedit to edit the Windows Registry.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

1. From the Windows Start menu click Run.

2. Type Regedit and click OK.

3. Locate the following registry key on the network file server HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/LanmanServer/Parameters.

4. From the Edit menu, point to New and click DWord Value.

5. Name the new key EnableOplocks and set its value to 0 (to disable opportunistic locking).

6. Reboot the network file server to ensure that the setting is accepted.

Let me know if that has any effect.

GumbyD

 

[Pat Hartman]

Sounds like you have some code that does a save rather than a SaveRecord.

DoCmd.RunCommand acCmdSave (saves the currently selected object-probably the form)
DoCmd.RunCommand acCmdSaveRecord (saves the current record)

 

[leerlap]

GumbyD - that's interesting, but I'm not in charge of the server, nor do I have any access to it. I'm a part of a large university system and the file is sitting on a very large server. I can ask about this, but I'm fairly certain my question will go unheard.

Pat - i'm actually just using the save button in access (or hitting ctrl-s) - no programming.

Any more thoughts? Really appreciate the input.

 

[Pat Hartman]

cntl-s saves an object. shift-enter saves a record.

 

[GumbyD]

The Save button (disk icon) and Control S are both short cuts to save the objects design. If the users want to save the record only they should be clicking on the record selector bar or pressing Shift + enter on the key board. Sorry - I should have given you this answer in the first place instead of going the wrong direction.

Good Luck!

GumbyD

 

[leerlap]

GumbyD - this feels like a stupid question, but what's the "record selector bar or pressing Shift + enter on the key board" ? Should I build in a "save" button into the form? This issue sounds like the right answer however - look forward to your response.

 

[Pat Hartman]

Maybe a picture will help.

 

[leerlap]

Hmm - ok - so is there a way to turn off ctrl-s and the save and save as buttons so users won't use them? Also, is it best to create a button on the form to do the shift-enter save?

 

[Pat Hartman]

Technically you don't need a save button at all since Access automatically saves the current record as soon as you leave it.

However, if it makes you and your users feel better, you can add a save button to your form. Let the wizard build it if you like. If you want to build it yourself, the save record command is:

DoCmd.RunCommand acCmdSaveRecord

I have a rather unique position on the use of keyboard commands. I use dozens of applications, each of which has their own set of keyboard commands. Some of those commands are duplicated in other applications but do different things. Some of those things are DANGEROUS. I would need to remember hundreds of commands that operate differently in different applications. I'm too old and feeble-minded to do that so I use ONLY the standard windows copy, paste, and delete commands. For all other activity, I use the menus and toolbars.

Call me narrow minded, but unless a user takes his shoe off and pounds it on the table, I do not include any application defined keystrokes in any of my applications.