[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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...?
0
Mandev23
Asked:
Mandev23
  • 8
  • 8
  • 3
  • +1
2 Solutions
 
peter57rCommented:
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.


0
 
Mandev23Author Commented:
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...
0
 
peter57rCommented:
No, the normal use is as you are doing currently.  What did I say to make you think otherwise?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
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
0
 
Dale FyeCommented:
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
0
 
Mandev23Author Commented:
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.
0
 
peter57rCommented:
How are you creating the ID?
At what point is it assigned to the new record?
0
 
Dale FyeCommented:
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.
0
 
Mandev23Author Commented:
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.
0
 
peter57rCommented:
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.

0
 
Mandev23Author Commented:
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.
0
 
Jeffrey CoachmanCommented:
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"
0
 
peter57rCommented:
The Save button in Access saves the form design, not the data.
0
 
Mandev23Author Commented:
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
0
 
Jeffrey CoachmanCommented:
Can you explain how I would go about seeing this issue?
0
 
Mandev23Author Commented:
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
0
 
peter57rCommented:
These users are all on the same  standard in-house ethernet network?  Nothing unusual about it or the connections?

If so, then as far as I am concerned there is no possibility of users being given the same ID number.

If that is what you are seeing then for me  there is only one possible conclusion,  and that is that the users are not connected to the same backend database.


What version of Access is being used (and are all users using the same version)?  What O/s on users workstations and the server.

Your 'Save Record' button doesn't save the record as you have replaced the code with a msgbox() function which just produces a message.
However, Access will automatically save the record as soon as you try to move away from it or close the form.  In any case, once you see the autonumber value, it is used, and will not be re-used even if you do not save the record at all, say by doing an undo.




0
 
Mandev23Author Commented:
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?
0
 
peter57rCommented:
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
0
 
Mandev23Author Commented:
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
0
 
peter57rCommented:
The code is already in your form module _ iassume you added the button using the wizard and that's when the code got added.

Change the Click event property of the button so that it says [Event Procedure] - select this from the dropdown list.  If you want a confirmation message add

msgbox "Record Saved"

after the acCmdSaveRecord statement.

(Click the build button (...) to the right of [Event Procedure] to get to the code)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now