UserName show in Multi-User Environment. 1

[MSWhat]

I'm trying to integrate my Access database into a multi-user environment, by making it accessable (pardon the pun) through a shared network drive.

Whenever, for example, someone tries to open a Word or Excel document on the shared drive that another user has open, an error message pops up with something to the affect of, "I'm sorry, user 'J.Smith' has this document open. You can only view it in read-only format."

I know about record-level locking, but for some reason (maybe when the first person to open the document has started editing it in design view) sometimes when a second person tries to open it, s/he is locked out and cannot open it. Other times a second person can open it, just not make changes to it (with an accompanying warning message).

Is there a way, as it automatically does in Word and Excel, to have Access pop up a window indicating that user "x" has the file open and so you can't edit it, etc?

Thanks for your help, all.

 

[lameid]

It sounds like you have your application objects (everything but tables) or Front end on the server. This is not recommended.

You should have all your tables (and only your tables) in one file, this is referred to as the backend. Then you link to these tables in the front end. The front end should be on each user's local computer (check the FAQ's for update methods).

That said it also sounds like someone may be opening the database for exclusive access. You can change the default by selecting tools and options on each access installation and on the Advanced tab change the default open mode to shared.

A more effective method is to use Access security (a large undertaking to understand) to remove the default admin user's permission on the database object to open exclusive.

Another reason this can happen is if some users have read only file permission to the folder containg the database while other have change.


You can see who is in the file by using a probram called LDBVIEW.EXE (available for download from microsoft) or you could open the file with the LDB extension with the same name and location as the MDB in notepad. It will show you the Access user (again access security and everyone is Admin if there is none) and Computer name of the person with the database open.

The only other thing I can add is that unless you are already familiar with Access security, it probably is not worth bothering with that aspect unless you have some compelling reason to.

 

[MSWhat]

I downloaded the LDBView.exe from MS but when I try to execute it, the program tells me "no LDB file was found" even when I open up the database. Any other suggestions? It's not really practical to ask everyone in my company to register throug the Access security program. However, the default security setting are set for shared, not exclusive. As an interesting sidenote, sometimes it says (when another person has opened up hte file in designview before I try to open it) that "This file has been placed in Admin mode--or something like that--by user w/the computer ID XXXXXXX." It seems like if the program can tell the ID# of the computer it should be able to give their name. Afterall, MSWord and Excel can!

Any other thoughts?

 

[lameid]

The no ldb file was found message should be an indicator that noone has the file open and therefore there is no LDB file.

The message probably has said something about user Admin locking the object on computer <Computer Name>. That is about all the detail the LDBview tool will give you except it tells you every computer and Access user in the file.

It still sounds like you have not split your database into a backend and frontend with the frontend on the local computers. You should do this. It will solve at least half your trouble. And is a lot easier than trying to learn Access security.

As for security, you can assume noone joins your workgroup and just give the users group and/or the admin user the permissions you want everyone to have and remove their permission to open the database exclusively (admin is common to every workgroup file and users is common at least in the same version of Access). If you are taking this approach, you may want to introduce your IT people to the Jetcomp.exe utility that will allow even secured databases to be compacted and repaired (Exclusive access is required to compact and repair).

 

[MSWhat]

lameid, you've been incredible!

You were right about the ldbview showing a "no ldb found...". This is really shaping up, thanks to you. As for a front end-back end split, I'm not quite sure what and when it makes sense to put things on the front end.

It sounds like your advice was to put JUST the tables on the back-end. Does that mean that the form and all queries/reports should go on the front end? You suggest putting the front end on each individual local machine, and not the shared drive. But if someone recognizes a flaw in a report, or creates a new report which is necessary, does that mean that it won't be updated on everyone else's machine? If not, then should just the form be put on the front end? Or maybe, should the front end just be left on the shared drive? If I'm still making changes to the design of the form, does it make sense to split up front/back -end yet? If I edit it will those changes apply to everyone?

Thanks, thanks, thanks.

 

[lameid]

The idea behind the frontend and back end is to first and foremost lower network utilization which is one of the bottlenecks to multi-user access.

It also allows you to make changes to the application and test them before releasing them. And since Access 2000 with the must have exclusive rights to modify requirement, it allows you to make changes to your copy without being concerned with not having time to Access it.

Yes, backend houses data or tables. Everything else is in the Frontend with links to the backend.

There is a FAQ that addresses rolling out new versions. Bascially Access detects if it has the current version and if not copies it from the network. It is more involved than that but you get the idea. If you do this it doesn't matter if it is done. Just put up new versions as you get them done.

It does however mean that everyones changes either get lost when you do an upgrade or they do upgrades themselves. But in this scenario, you are racing against the other guy for updates. Generally it is better for one person to make all updates or to communicate with each other. If you share an office this is easy, otherwise it gets clumsy.

 

[MSWhat]

Think I got it. Only question is whether or not your comments apply to forms as well? Such that if one person wants to edit/CREATE a form, that change only occurs on her computer? Whereas if I edit the design of the form, you said Access will automatically update everyone's copy, is that true of other people editing reports as well? What about if it's ME editing/creating reports?

 

[lameid]

Every copy of the front end (everything but tables) is independent. The Faq just provides a way to push a Front end out and overwrite the local copy.