Hi All,
My knowledge of databases and Access is almost zero but none the less I have to try and solve a problem with a small home-grown Access database and hoping someone can help.
I've done many searches and can't locate what I thought would be an easy answer. There was a posting on this site by cassyjack with an answer by jbennet that I thought for sure would fix our issue but no joy.
We have a small database with a few Tables, a couple of Forms, and a couple of Queries and Reports. There are about 5 users and the single .MDB file is on a Public folder on our SBS 2003 server. All users have full access to the folder and the file.
We use a Form to log new jobs coming in. Each job is manually assigned the next available number. (i.e. 0304-10, 0305-10, etc.) This number must be unique.
For some reason we are not able to have more than one person enter new records at a time.
There are no error messages or warnings that prevent a second user from opening the database or entering records. All indications are that they are the only person using it and are happily creating unique records.
The problem is that the user(s) cannot see new records that are being entered the others and therefore they end up assigning the same job number to more than one record. (Bad)
If a user closes the Form and then reopens it, they will now see all the latest records.
Based on my searches, I have done the following without success:
* Used the Split Database utility to create a FE and BE.
* Copied the FE file to two different users' local Desktops.
- This did not solve the problem ...
I then (per jbennet's posting) changed the following settings on the BE file on the server under Tools >Options >Advanced:
* Set Open Mode to "Shared"
* Changed Default Record Locking to "Edited Record"
- I did find that these had originally been set to "Exclusive" when I first opened the dialog box but changing them did not solve the problem ...
I then made the same changes on the FE files stored on the two users' Desktops.
- This did not solve the problem ...
Out of desperation, and because I had nothing to lose, I made some changes to the Refresh Interval and Number of Update Retries on the same screen. Still no joy.
After each of those changes I would close all open sessions and reopen the database on both PCs before testing to see if it helped.
I also played around with the Security settings but got myself in trouble real quick and had to restore a previous version of the files. (Love that Volume Shadow Copy !!!) Turns out there was no security set on any of the files so pretty sure this is not the issue.
At this point I've pretty much reached the limits of my technical skills and can't think of any new ways to ask the questions in Google to find the answer.
Any suggestions would be muchly appreciated !
Jay Perez
Rochester, NY