Using a database with multiple users (1 Viewer)

[Tim L]

I done a quick search and found a few useful pointers but still have some questions:

1) If you use a FE/BE split give each user their own copy of the FE.
2) Make sure that users do not open the dB Exclusively.
3) Give records their unique id just before the record is saved (using the beforeUpdate event).
4) User access priviledges can cause problems.

Questions on the above:

2) How do you do this?
3) I presume that this could be achieved by having text boxes that correspond to field, then, on chaning to a new record or clicking on a save button, have a new record created (thus assigning an autonumber field) and apply the contents of the text boxes to their respective fields, however this seems a bit cludgy and I suspect that someone could suggest/provide a method with more finess
4) What needs to be set?

And some general questions:

a) Record Locks, I suspect that this should this be set to Edited Record, correct?

b) With an Access 2k compatible database, is there a limit on the number of users that can be accessing the BE at a time, if so, what is it?

c) If you've distributed the FE and have the BE located on a server somewhere, how does the FE locate the BE? Will a dialogue box be automatically generated by the FE or does this have to be programmed?

d) Are there any other considerations that need to be dealt with?

Tim

 

[Pat Hartman]

2) You need to use security that locks the database and prevents it from being opened at all. The startup code in your fe database should provide the userID and password so that you application can access the db.
3) If you use autonumbers, which is the recommended method, you don't need to worrry about this. If you assign your own sequential numbers, do it in the BeforeUpdate event. This will minimize, but not eliminate, the potential for generating duplicates. If you assign the sequential number in the BeforeInsert event (an ok choice) or in the on Current event (very bad choice), you will be much more at risk of generating a duplicate if multiple users are adding records at the same time so make sure that you include the approprate error trapping code.
4) Users need create and delete permissions to the directory that contains the be in addition to update permissions. If the user doesn't have create permission and he is the first to open the db during the day, the db will open in exclusive mode. When an Access db is opened, Jet creates an .ldb file in the same directory as the db which it uses to manage record locks. If it cannot create this file, it will open the db in exclusive mode. This is the most common cause of #2 by the way. It is rare that a user actually intentionally opens the db in exclusive mode. Normally the problem is that he doesn't have the correct permissions for the directory so Jet opens the db exclusively for him.
a) I generally use optimistic locking which is the default. Should two people try to update the same record, the second one will get a message giving him three choices. Copy the changes to the clipboard, discard the changes, or apply the changes. You can trap this error message and give the users different options.
b) The official limit is 255. In reality, the maximum number of concurrent users is closer to 30-50.
c) This is your responsibility. You link the fe to the be and that link remains static. Nothing needs to be done unless you move the be. If you think that you will have need to move the be, you must write your own procedure for finding the new location and relinking the tables.
d) Don't create applications that rely on user modifications to objects. For example, don't let them add columns to tables. You should also minimize the use of make-table queries since they cause data base bloat and you'll need to frequently compact the db.

 

[Tim L]

Thanks for your considered reply Pat.

With regards to:

#2
Will creating a standard database password then splitting the datbase into FE/BE achieve this automatically or will additional programming be required?

3#
If users A and B start to create a record at about the same time, then A cancels (undoes) and C starts to create a record, will the autonumber be set correctly? I.e, is it possible that the records created by B and C will have the same number, or does Access take care of this in the background? (If it does, will there be any gaps between numbers in the autonumber field due to cancelled record generation?)

#4 - Ok. Thanks.

a)
How is optomistic locking achieved? (I can only find settings for No Locks, All Records and Edited Records.)

b-d) - Ok. Thanks again. (although I may be back... )

 

[Pat Hartman]

2) A database password will keep out most people so it should be sufficient for this purpose.
3) Access assigns the autonumber as soon as the first character is typed in the form. Notice that when you go to a new record, the autonumber says "autonumber" but watch it as you type something in another field. Once an autonumber is assigned, it cannot be reassigned even if the record it is assigned to was never saved or was saved and later deleted. So, yes, autonumbers invariably have gaps. This is not a problem. The ONLY purpose of the autonumber is to provide a unique identifier.
a) No Locks is optimistic locking. This means that the record being changed is locked only for the length of time it takes to update it. You can find a more detailed description in help of what the various lock types do.

 

[jsjaedon]

Hi guys, i got a question on this as well.

when setting user permissions and access rights, whatnot, do i do that in the front end or backend?

thanks
jerry

 

[aaronmichael1]

Confused and Frustrated

I am trying to set up sharing and I am really frustrated.

I've set all the security settings I just don't know how to set how other users are logged on to the database. I only have admin and user and I want everyone else signed on as user. I am assuming I have to change something to each of their computers but I am not sure what. Any help would be greatly appreciated.

 

[The_Doc_Man]

If they are coming in without joining the workgroup, they come in by default as ADMIN and have Admins group's rights. This is, as we say, DNG.... definitely not good.

So what you do is create one more account for yourself. Make it a member of the Admins group. THEN make the Admin account (no "s" on the end) no longer a member of the Admins (note the "s") group. It can still be a member of the Users group.

To do this RIGHT, create another group for users. Define the rights you want your users to have for this group. Give your users individual accounts that are members of this new group. Or if they have individual accounts already, make them group members. Then whittle down the USERS group to the minimum rights possible. And if the Admin account is a member of this group, folks will slowly get the hint.

Then you don't care if they refuse to join the right workgroup first.

 

[Pat Hartman]

To properly secure the database, you MUST follow the setup steps precisely. If you don't, you'll end up with a db that acts like it is secured on your db but when copied elsewhere is wide open. Look at the Security FAQ document and read it carefully. It outlines the steps to follow and gives you solutions for problems. http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp#_Toc493299666 Before you start to secure the db, make absolutely certain to have one or more backup copies so you can recover from any problems.

 

[pafcpilgrim]

Unique identifier

Pat something i have noticed on a program at my new job this week (not access but a VB application) linked to SQL server is the Purchase Orders system.
You open the form add the items you need then when you click save it then assigns a PO number but there have been times when it produces an error while trying to save the record and you have to keep trying before it will close the form and save it.

The thing i am interested in is how does it no what number to assign the next po created by someone else while mine is waiting to get through to the server this type of scenario happens quite a lot to a few of us at the same time and i'm just interested in how it deals with it if you can help.

rgds.

 

[Pat Hartman]

SQL server and Jet handle the problem differently. You will notice that when your db is connected to Jet tables, the autonumber is assigned and committed as soon as you type something in any field. So, if you cancel the operation prior to saving the record, the assigned autonumber is lost even if no one else has yet added one with a higher value. In SQL server (and other RDBMS'), the autonumber (they're not called that) is assigned immediately prior to the insert. I don't know if this process is single threaded to prevent duplicates or if the database engine has a retry loop in case others are inserting records at the same time. After the append is committed, the assigned autonumber is returned to Access so it can be displayed on your form. Since you are getting an error, it seems the SQL server does not have a retry loop, you must retry yourself.