Access: Multi User Environment

[Cheddarbug]

Several of us are inputting information via a Form and the records disappear on some users. Any ideas?

 

[dhookom]

Can you explain what you mean by "disappear on some users"? Are some of your users typing over existing records? Are you all entering data in to the same MDB?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cheddarbug]

When I say "disappear" the entries become a record associated with a primary number but the data within the fields reads "error". It is the same MDB with multiple forms writing to one table. We're in question of the property settings for a "shared" database which we got off of the help menu. Do we need to change the "retry and refresh interval settings"? We've already checked the options and the database is shared.

Thanks.

 

[dhookom]

I have never used any settings other than the default. If you are getting "error" in fields, I think you are experiencing some corruption.

Is your application built so that mutliple people may be editing the same record at the same time?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cheddarbug]

Where are the settings so that multiple people may be editing the same record at the same time?

 

[SilentAiche]

Cheddarbug,

In the File/Open dialog box, the Open button has a drop-down list that deals with read-only and exclusive attributes.

Once your db is open, click on Tools/Options and select the Advanced tab. There is a section labeled Default Record Locking - I recommend you select Edited Record for a multi-user environment. Also, right below that is a box to check for "Open databases using record-level locking" (be sure this box is checked).

BTW - I'm using Access 2002 on Win XP.

Hope this helps.

Tim

 

[dhookom]

The reason I asked about the possibility of multiple users editing the same record at the same time is because a properly structured application and tables will generally avoid this.

For instance, if you are in the business of processing loans and each loan must move through multiple steps (edited by multiple users) where dates and possibly status are stored. A poorly written application would use a table like:[blue][tt]
tblLoans
===========
LoanID
LoanType
LoanAmt
RecvdDate
ReviewDate
ReviewStatus
SubmitDate
SubmitStatus
ProcessDate
ProcessStatus
...etc...
[/tt][/blue]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

CheddarBug

Welcome to Tek-Tips.

Duane has talked to you about database design.

But there are two things, partially addressed in the above posts for your thread.

First, is the creation / assignment of the primary key. A primary key is essential with a database design -- each record must be uniquely defined. A common approach is to use the AutoNumber data type. However, in a multi-user environment, use of AutoNumber is problematic. It is possible for two or more users "grab" the same AutoNumber field. The AutoNumber, if used, is assigned when a person starts entering a new record. This is called the OnInsert event. If several people are creating a record at about the same time, they could all grab the same AutoNumber. For example, imagine several data-entry clerks entering an Order, entering all the details for the order takes five minutes, so two or three clerks "grab" the same Order number. In theory, only one clerk will be able to enter the order and the other two will get an error when they try to update the record because the table will not allow a record with a duplicate AutoNumber -- breaks the rule for uniquely identifying each record.

See MichaelRed's FAQ on another apporach...
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)

Second, multiple users can edit the same record. Only the results of the last person who saves will ben seen -- they will over-write the data entry from the other users.

As a start, with multiple user database applications, set the options for all users openning the database....
From the menu, "Tools" -> "Options" and select the "Advanced" tab, and ...
Default Open mode: Shared
Default record locking: Edited record
Open database using record-level locking (enabled)

You may wonder why Access is causing you so much grief, and you may have expectations that Access should do a better job in handling multiple users -- I understand. However, Access is a relatively inexpensive yet pwoerful tool. Other more powerful databases that are better suited for multiuser access with better security will cost significantly more, and require more database administration.

Richard