Share problems in networked multiuser FE/BE database

[Steve Kendrot]

I have a split database on a peer to peer network(6 XP
computers). I have copied the front end to all 6 machines
and linked to the tables in the backend stored on a single
machine. I am having problems with multiple users
simultaneously entering data through forms. It seems to
work fine from any machine as long as it is the only user
with the database open. If two or more have the database
open at the same time, it opens other copies in read only
mode and will not allow new records or edits. I have
checked the form properties and both options for the
dataentry property. When set to yes, on anything but the
host computer, no records are shown in the form and new
records aren't allowed. I have tried importing the
required tables to the front end to make sure its not a
problem with my front end, and it works fine. Problem
arises when trying to access linked tables in the backend.
Help function hasn't given me any clues. Does anyone have
any suggestions. I had an access 97 version of this same
database with the same frontend/backend configuration set
up on a different network (Win 98) and it worked no
problem. Why would I have problems with Access 2002?
.

 

[Pat Hartman]

Check the network file share settings. That is what is probably causing the problem. Assuming that no one is opening the db in exclusive mode, Jet is not what is locking the db.

 

[jaydwest]

You might also check the default record locking (Tools | Options | Advanced). Make sure it is not set to All Records

 

[Steve Kendrot]

No luck

Pat,

Thanks for the advice. I checked the shared folders and unset the read only parameter. Did not, however, solve my sharing problem. I read an old post that suggested that if user level security is not set up, all users will open the database as ADMIN and thus the database will be opened exclusively on a first-come- first-serve basis. I'm thinking this may be my problem, as all of my record lock properties are set to allow sharing. Is there a way around this without setting up user level security (I've never done that and am a little gunshy). Is this a difference between Access 2002 and 97? Did not have this problem with 97 version of the database.

Thanks for your feedback.

Steve

 

[Pat Hartman]

One thing that is different from A97 is that the newer versions of Access allow only a single user to make changes to objects. So, if your users modify forms/reports, etc or your code does, subsequent users will be locked out. This increased level of security is actually good since it prevents corruption and you shouldn't be allowing your users to change objects in a shared db anyway. If you need to have multiple developers working on a shared db, you need to install SourceSafe to manage source changes. This will allow multiple users in the db to be making changes at the same time.

If your users or your code modify objects, you can split the db and give each person their own private copy of the fe. They can then just share the be which contains the data tables only.

 

[jaydwest]

Do you have one copy of the front end database that everyone users or does every user have their own front end database?

 

[Steve Kendrot]

Jay,

Each computer on the network has its own copy of the FE. I have 15 employees who share 6 computers. Based on yours and Pat's feedback, its sounds like I definately ned to incorporate User level security. Is it possible to set up just one user account that multiple people can share or will doing so create the same problem I am currently having with multiple users sharing the ADMIN logon.

Thanks for all your feedback. Any tricks I need to know or consider before implementing user level security?

Steve

 

[Pat Hartman]

BACK UP YOUR DATABASE. THEN, BACK IT UP AGAIN.. I can't tell you how many posts we get from desperate people who have locked themselves out of their db. Download the Security FAQ from the Microsoft Knowledgebase and study it. Then follow its step by step instructions. If you are using an older version of Access and don't have the security wizard installed, get that from the Microsoft Download site. Since security isn't really an issue, all you really need to do is to define the user accounts and their passwords.

Don't forget that once you "secure" the db, you'll need to start the db from a shortcut so you can reference the correct workgroup file. If you start the db by first joining a workgroup and then opening the db, you will change the default workgroup in the registry and that will make ALL your databases appear to be secured. This is also covered in the FAQ but I wanted to emphasize it.

Each user will need his own ID or you'll have the same problem you have with all of them being the user "admin".

 

[Steve Kendrot]

Back to business

Pat,

Thanks for your past feedback. I'm getting back to my database after a couple weeks of fieldwork. I have made some changes to my database and backed it up as you suggested. I copied it to my host computer on the Peer to Peer (where the BE is stored). I opened the database, ran the security wizard, and then ran the database splitter, thinking it would save me having to run the security wizard on both the FE and BE. My intention was to simply copy the FE to the other desktops on the P2P, but when I did, I ran into problems with the tables and forms opening in read only format. I have set full admin permissions for my admin workgroup "NuteBoss" but I still can't enter new data using the forms. Have I overlooked something simple?

I have a few questions I'd like to ask.

What is the most efficient way to get copies of the FE to the various workstations on the P2P?

Should each workstation have a copy of the workgroup file on its C drive, or is it acceptable to link to the workgroup file on the machine that hosts the back end?

If the FE is copied to each workstation, does the security wizard have to be run on each copy?

Does security wizard have to be run on the backend once the database is split even if it was run before the split?

Thanks in advance for your feedback.

Steve

 

[Steve Kendrot]

Back to business

Pat,

Thanks for your past feedback. I'm getting back to my database after a couple weeks of fieldwork. I have made some changes to my database and backed it up as you suggested. I copied it to my host computer on the Peer to Peer (where the BE is stored). I opened the database, ran the security wizard, and then ran the database splitter, thinking it would save me having to run the security wizard on both the FE and BE. My intention was to simply copy the FE to the other desktops on the P2P, but when I did, I ran into problems with the tables and forms opening in read only format. I have set full admin permissions for my admin workgroup "NuteBoss" but I still can't enter new data using the forms. Have I overlooked something simple?

I have a few questions I'd like to ask.

What is the most efficient way to get copies of the FE to the various workstations on the P2P?

Should each workstation have a copy of the workgroup file on its C drive, or is it acceptable to link to the workgroup file on the machine that hosts the back end?

If the FE is copied to each workstation, does the security wizard have to be run on each copy?

Does security wizard have to be run on the backend once the database is split even if it was run before the split?

Thanks in advance for your feedback.

Steve

 

[Pat Hartman]

I am not an expert on security and in fact, a client would need to hold my hands in the fire to make me implement Access security. I much prefer to rely on network security to keep unauthorized users out of the database directory. Also, most of the be's I create are not actually Access, they are usually something else such as Oracle or SQL Server so I don't have to worry about data security. So with that said, I'll do my best to answer your questions.

What is the most efficient way to get copies of the FE to the various workstations on the P2P?

I put a copy in a shared directory and notify the user when he needs to download a new copy. The database itself checks its version as it opens and if there is a newer version available on the server, it notifies the user and refuses to open. You can get more sophisticated and do the download for them. Search for posts on the topic to find code. If your shop has software that "pushes" updates to the client PCs, you can add your app to the "push" list.

Should each workstation have a copy of the workgroup file on its C drive, or is it acceptable to link to the workgroup file on the machine that hosts the back end?

If you insist on Access (actually Jet) security, I believe the accepted method is to install the workgroup file locally.

If the FE is copied to each workstation, does the security wizard have to be run on each copy?

The master copy of the db should be secured - I cannot emphasize how carefully you need to follow the directons. Then create an .mde and distribute the .mde. You also need to secure the be separately. You can use the same workgroup file.

Does security wizard have to be run on the backend once the database is split even if it was run before the split?

I'm pretty sure you need to secure the be separately.

 

[Steve Kendrot]

Pat,

Your feedback has been invaluable. I have spent the last couple days rereading the FAQ and browsing the AWF archives. You have the patience of a saint in answering questions that have been asked over and over. I apologize for asking them again.

Part of my problem is that I am VB illiterate so many of the solutions and work arounds are greek to me. I am having a hard time creating shortcuts that reference the appropriate workgroup file for the secured database. (My using workgrp admin to join the workgroup has resulted in all others appearing secured). I understand the concept, but I don't know how to force the shortcut to reference the appropriate mdw. The SecFAQ provides syntax (c:\msoffice\access\msaccess.exe /wrkgrp c:\myapp\secacc.mdw) to enter in the command line for the shortcut icon, but when I enter the syntax on the target window (by right clicking properties for the shortcut), it doesn't work. Maybe that's the wrong place.

I'm getting a farily good grasp of the whys, its the hows that have me flummoxed.

Steve

 

[Pat Hartman]

You need to specify your db name also.

c:\msoffice\access\msaccess.exe c:\yourpath\yourdb.mdb /wrkgrp c:\myapp\secacc.mdw