Link to home
Start Free TrialLog in
Avatar of Mandev23
Mandev23

asked on

Using an MS database for single use only

Hi,  i have a split MS database, a front and back end.
The back end resides on the server, and all users have a front end which they access. However I am having problems where records are being over-written and cannot understand why. This happens randomly.
I was therefore wondering if there was a way i could set the database so it can only be used by one user at a time...?
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

If records are being 'overwritten' then there is something in the application design which is specifically allowing this to happen.  By default, Access does not 'overwrite' one user's changes with another user's changes made at the same time, it produces a warning message and asks what you want to do.  So are uses dismissing this message by choosing the 'overwrite' option.

Similar questions are sometimes posted here, and my first thought is always to question why the business operating procedure requires more than one user to be accessing the same record at the same time.  There may sometimes be a genuine need but sometimes it is just down to poor operational controls or a table or form design which is not allowing   different parts of the data to be maintained separately.

You will find it completely unacceptable to users to be told that they can only have one-at-a-time usage of the application so you need to address the real problem.

Notwithstanding that, to answer your question, you could set folder permissions so that users do not have file create rights.  This will prevent the creation of a lock file which is required for sharing.
This will mean that the second or subsequent users are prevented from gaining 'edit' access to the data.


Avatar of Mandev23
Mandev23

ASKER

hi peter57r
so the normal scenario for a MS Access database is to use it stand-alone?
but what is the purpose of then splitting the database to share across multiple users?  as i need to in this instance...
No, the normal use is as you are doing currently.  What did I say to make you think otherwise?
As Pete sates, you need to address this mysterious "Random Overwrite" issue first.
(and worry about "Band-aids" later)

Can you clearly post an actual scenario where this happened?
In other words, ...clearly define "Random over-write"
Random Fields?
Random Records?
"John" changes magically to "Bob"?
All instances of a data updated to the same (wrong) date?
...etc

Please be explicit

JeffCoachman
Just a guess, but one cause of this would be update queries that might not be fully qualified in a WHERE clause, so you are updating multiple records at a time rather than only one record.

How do you know that a field doesn't contain the "correct" data?

Dale
We have a 'quote-log' database to enter quote details for a customer.
The situation is, when a user goes into the database, gets a new ID and enters the details of the record, then exits the database, 7/10 the QuoteID has been over-written another record someone else might have entered. We know this because when the record is originally entered, staff print the record out for reference. Then when they go back into the database to find & edit their quote, it normally is over-written by someone else's details. This happens randomly.
How are you creating the ID?
At what point is it assigned to the new record?
Are you writing the record to the table before printing the record out for reference?  How are you printing the record, is it via a report, or are you just doing a screen print of the current form?  I would ensure that whatever method you are using, you make certain the record is saved before you do the print-out.
the QuoteID is a AutoNumber  and primary field.  When the user clicks on 'New Record', the QuoteID is blank, but then soon as they populate the first entry on the record, the QuoteID is generated and is visible on the form.  that ok?

i have done a 'merge to word', which is triggered when the user clicks on a command button. The fields on the form, populate into a Word Document which i designed, allowing the users to print out.

i can upload a copy of the front / back end if you would like me to....
Greatly appreciated.
I'm with Dale (fyed) on this - I just wanted to check the ID situation first before saying the same thing.
It sounds like the record is never being saved.

it happens on a random basis.
how do we get the record to save?  

just click on Save in the top left of Excel Menu bar, we do this already.
I think at this point we need to see a sample of this database that exhibits this issue...

As the previous experts have stated, we really need to see what is going on with your "Saves"
The Save button in Access saves the form design, not the data.
boaq2000 / peter57r
Attached is the front end (V2-3.zip)  and the back end of the database (V2-3-be)

Users access the front end, which has linked tables to the back end. Every user has a local copy of the front end in their C: drive, which they use

Any help would be greatly appreciated. Cannot seem to figure out why random records are overwritten..
V2-3.zip
V2-3-be.mdb
Can you explain how I would go about seeing this issue?
When you go into the front end db (V2-3), (ensure macro/security is enabled), click on Edit record from the switchboard, you will get the main form entry. From the bottom right, click on the end arrow button to take you to the last record >| button, then click on the > next button to take you to a blank record. To get a QuoteID, select the 'status' of the quote from the drop down  i.e. select anything won, Lost etc. then you will see the QuoteID appear. Now when multiple people have the database open, we dont know if someone else is already using that QuoteID. How can we ensure that record is unique and saved?  prevent over-writing?  can you look at the design...
-Hope this makes sense
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi peter57
Yes everyone is on the same network. Everyone using Access 2007. The Back end is on the Server Shared network drive, same for everyone. you can see the linked tables all point to the same directory R:  which is our sales drive.

I havent included a  'Save Record' button on the form, so not sure what you mean here..

instead of everyone having a local copy of the front end on the C: drive  (which points to the back end) i have asked users to access the front end from the server location instead (i have given them the link), so we are going to see how that goes.. know what i mean?
For me, separate front-ends is more reliable than using the same file.
But anything is worth a try.

I have attached an image showing the false Save Record button.
Capture.JPG
can you show me how to code the 'false save button' so it does it's job of saving the actual record..?

at the moment i still have no solution from the experts
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial