Sharing a Database - Multiple Users

[lm0304]

After creating a new database to keep track of new quotation numbers assigned, I copied the database into a "network" directory where all users (3 others) that need to use it would be able to access it, then created a shortcut on each of their desktops.

It seemed to be working fine for a little while, but recently we have been seeing some problems. The database sequentially numbers each quotation entered, but after another user enters one or sometimes two new quotations and takes note of their quote numbers, another user goes in to enter a new quotation and is NOT seeing the previously assigned numbers. (For example, if User A enters in a new quotation and has 2002-111 assigned to it, then when User B goes in the database to enter a new quotation, they get the exact same number, meaning the database is not saving ALL USERS entries.)

If anyone can help, I would greatly appreciate it. It seems that some days it works, and other days, I need to re-enter data that someone else had already done. I tried "repairing the database" to see if the missing numbers appear, but it doesn't seem to work.

Thanks, in advance, for your assistance.

 

[mymou]

Hi lm0304

Not 100% on these kinds of issues - but i would imagine that two users simultaneously entering data would create this problem - check it out.

I think that access doesn't commit a transactions for a record until the data is saved (moving to next record, closing form etc). This creates the problem.

If this is correct, the easy solution would be to set 'no duplicates' in table design - and then maybe let the user deal with the error (or add some error traping). Otherwise you could do lots of checking before a record was saved.

Hope this is helpful.

Stew

 

[GDGarth]

I've had similar issues, and the way I dealt with it was to add the new record as soon as it's entered. I usually put a "Save Record" button on the form, that checks for proper data, etc., then saves the record.

The other issue I've had is, with multiple users on a network, you'll get corrupted files. I'm in the process of splitting databases - putting the data on the network, with a separate front end that will contain the interface. If you're seeing things act differently than they did originally, you may have a corrupted database.

I've had virtually no success with repairing damaged databases - I usually have to go back and recreate it from a backup copy.

 

[wbuckles]

I have sort of an interesting suggestion. I'm assuming you have a form that links to a query or table directly. Rather than suggest major changes, I'll suggest something kind of strange, but it works.
(You'll need to make sure the form is in add mode).
You'll see that in the table, access doesn't update the record for some time after the user completes the info on the form. Put an 'after update' macro in for one of your text boxes (Whatever they finish last to create a new record). The macro will have two lines (you can also do this in code, but if you do not have a workgroup, I'm also guessing you don't want to spend a lot of time on fixing this)
The two lines will be:
Goto Record "Next" and Goto Record "Previous"
This will force the form to skip ahead and back after the user types in some info. Then, they can continue working on it, writing it down, print it, whatever, but the table has locked in the autonumber quote number. That will eliminate your dupes.

 

[mymou]

wbuckles

I think RECORDS | SAVE RECORDS (shift enter) has the same effect.

Stew