Data being lost with multiple users?

[T29]

I am new to access. I have created a simple split database with 4 tables, 1500 records in each and about 10 forms. It is largely used for tracking the status of projects with in and out dates. There are 15 users with about 5 simultaneous users. The back end sits on the shared drive and I have loaded the front end onto everyone’s computer.

Problem: A User enters some records and closes the program with no error messages. They reopen the program later in the day and some of the records they entered are not there? This problem seems to affect one user the most but a record here and there seems to be missing for a couple other users. The problem is not “in the seat” as far as I can tell. I read on here if your server momentarily drops a computer this can cause lots of problems? Has anyone experience this? Would SharePoint solve this problem? Any help would be extremely appreciated.

 

[dcb]

How are the users "saving" the records?

 

[T29]

Some are not saving. It seems to me the records auto save when you switch records or close a form. Some use a save record button I added to the form and some use the save icon in the upper left of access. The user with the most problems stated using the upper left save icon after each record entered but still didn’t fix the problem.

 

[boblarson]

Some are not saving. It seems to me the records auto save when you switch records or close a form. Some use a save record button I added to the form and some use the save icon in the upper left of access. The user with the most problems stated using the upper left save icon after each record entered but still didn’t fix the problem.

Well, not surprising since that save icon has nothing to do with saving records. It is for saving design changes.

 

[dcb]

WOW
You are really just "hoping" that access saves....

Add a button on your form that saves the record (follow the wizard) also add a close button that saves before they close.

In the on_close event for the form you can also DoCmd.RunCommand acCmdSaveRecord

 

[boblarson]

In the on_close event for the form you can also DoCmd.RunCommand acCmdSaveRecord

Better code would be the:

If Me.Dirty Then Me.Dirty = False

that way it will ONLY try to save if there is something to save, where the DoCmd.RunCommand acCmdSaveRecord will try even if there isn't.

 

[dcb]

Better code would be the:

If Me.Dirty Then Me.Dirty = False

that way it will ONLY try to save if there is something to save, where the DoCmd.RunCommand acCmdSaveRecord will try even if there isn't.

True: I had forgotten the code!
I never let my users "self navigate" - they always have to use a custom button just to prevent this type of thing....

 

[T29]

good to know about the save icon. I have already added the save record button and the close record button to all forms. So it should save when you use the close button? Can you explain "If Me.Dirty Then Me.Dirty = False" to me more?

 

[boblarson]

Can you explain "If Me.Dirty Then Me.Dirty = False" to me more?

So, when a bound form has new data added or data changed, it is said to be "Dirty." So, the code checks to see if there have been any changes (form is Dirty) and if so, it resets the Dirty property to False, which then forces a save of the record.

Does that help?

 

[georgedwilkinson]

I'm just curious. What is the primary key on the table that the data is not saving in? Is the primary key an auto-number type?

My guess, with the little bit of information we have, is that users are able to duplicate the key, with one of the users eventually overwriting the others.

Another curiosity, more general. I have created millions of lines of code in my lifetime and I can't count the number of databases with hundreds of thousands of users. I NEVER have this problem. When I'm using Access, I hardly ever even consider using VBA (the On Close event) for getting the system up and running. To do so would defeat the purpose of using Access, IMHO. As such, why are so many Access programmers (new and slightly more seasoned) interested in writing VBA for saving records? That is like taking a process that has been perfected by Microsoft with millions of dollars of development costs and throwing it away. I'm not saying that is what is happening in this case but I'm curious why.

 

[dcb]

I'm just curious. What is the primary key on the table that the data is not saving in? Is the primary key an auto-number type?

My guess, with the little bit of information we have, is that users are able to duplicate the key, with one of the users eventually overwriting the others.

Another curiosity, more general. I have created millions of lines of code in my lifetime and I can't count the number of databases with hundreds of thousands of users. I NEVER have this problem. When I'm using Access, I hardly ever even consider using VBA (the On Close event) for getting the system up and running. To do so would defeat the purpose of using Access, IMHO. As such, why are so many Access programmers (new and slightly more seasoned) interested in writing VBA for saving records? That is like taking a process that has been perfected by Microsoft with millions of dollars of development costs and throwing it away. I'm not saying that is what is happening in this case but I'm curious why.

He does kind of give it away I think in his design....
Saying that the users enter the data and then close the app is not "normal" usage. The navigation of "switching between forms" I also see as a user having forms open and selecting the one he wants by, i assume, minimize or just moving them around

I dont think the DB's that you have designed would be without a structured workflow? As essentially we learn quite quickly that this is the only way to deal with a UI....

These are just my thoughts- may be wrong

As for the duplicate, He has not mentioned any form of error and we have to assume that with whats being described that the OP would not have err trapping that would be preventing this popping up...

 

[T29]

I am using a unique project number for the key. All my tables are linked back to the customer table (My main table) with one to one relationships. I have referential integrity on all relationships and only one person can add new project numbers. All other forms have the project numbers as read only. I first thought users were entering in info but under the wrong project number. So upon return to the record it would be blank. I have disproved this theory.
I have searched on this problem and it doesn’t seem to exist like George Wilkinson pointed out. The question I keep asking is what am I doing different? I have noticed our server sometimes will drop a computer from the network momentarily. Our part time IT guy said this could be from the switching unit to what crazy things people get up to on the network. From what I have read on this forum the way access functions this momentary drop will cause all kinds of problems when the DB is split. I don’t know enough about networks to have any kind of opinion on this.
My front end links back to my tables are in this formaS:\mydata\backend. But Albert D. Kallal said they should be in this format: \\servername\mydata\backend . He said a simple digital camera could screw my current path up? Could this be part of my problem? I have no idea what the extension .mdb means? Could this be part of it? I opened access for the first time 2 weeks ago so I could be making awful mistakes.

 

[gemma-the-husky]

i think the most likely explanation is that a user PARTIALLY enters a record, but the record as entered breaks some constraint - so its incomplete

when you close the form, you may be intercepting and cancelling any error messages - which would include warnings that the record cannot be saved

ergo - some incomplete records dont get saved


-----------
if you REALLY want an icon to click, then make the form show "record selectors"

an unedited record will have a black triangle in the record selector. when it's edited/dirty the triangle changes to a pencil

click the pencil to save the record

 

[gemma-the-husky]

I am using a unique project number for the key. All my tables are linked back to the customer table (My main table) with one to one relationships. I have referential integrity on all relationships and only one person can add new project numbers. All other forms have the project numbers as read only. I first thought users were entering in info but under the wrong project number. So upon return to the record it would be blank. I have disproved this theory.
I have searched on this problem and it doesn’t seem to exist like George Wilkinson pointed out. The question I keep asking is what am I doing different? I have noticed our server sometimes will drop a computer from the network momentarily. Our part time IT guy said this could be from the switching unit to what crazy things people get up to on the network. From what I have read on this forum the way access functions this momentary drop will cause all kinds of problems when the DB is split. I don’t know enough about networks to have any kind of opinion on this.
My front end links back to my tables are in this formaS:\mydata\backend. But Albert D. Kallal said they should be in this format: \\servername\mydata\backend . He said a simple digital camera could screw my current path up? Could this be part of my problem? I have no idea what the extension .mdb means? Could this be part of it? I opened access for the first time 2 weeks ago so I could be making awful mistakes.

but this doesnt make a lot of sense - one to one tables? how many tables do you have? and what are they?

finally note that an .mdb IS the database itself (in the same way that a .xls file is a spreadsheet and a .doc file is a word document)

one other thing - are all your users running/sharing the same copy of the database on a network - that is a recipe for problems

 

[T29]

I have 5 tables. Customer info (20 fields), Project specs (10 fields), Tracking (46 fields), scheduling (33 fields) and Notes (12 fields). I could have use a flat table but was trying to group things logically to help me design the front end. I have read a lot about relationships but don’t truly understand them yet.
All users have a copy of the front end saved on their computers which points to the back end that sits on the server. So say a form has 10 fields on it. You would have to click the pencil on each field on the form as you enter it to have it save appropriately?

 

[gemma-the-husky]

I have 5 tables. Customer info (20 fields), Project specs (10 fields), Tracking (46 fields), scheduling (33 fields) and Notes (12 fields). I could have use a flat table but was trying to group things logically to help me design the front end. I have read a lot about relationships but don’t truly understand them yet.
All users have a copy of the front end saved on their computers which points to the back end that sits on the server. So say a form has 10 fields on it. You would have to click the pencil on each field on the form as you enter it to have it save appropriately?

No the record selector relates to the whole record. you can save it at any time manually, but often you dont need to, as the save will be automatic anyway

when a record is modified (eg by changing a field value) the record selector changes to a pencil

now the changed record will be saved in numerous cases - eg
- by moving to a new record
- by closing the form
- by running code
- by clicking the pencil
- by using the menu item - records/save record

or the edit can be cancelled in a number of ways eg
- by pressing escape,
- by running code

now when access tries to save the record, the save may fail, but if it does access will explain why - however, you can run code to suppress these messages - in which case the record will not be saved, but you may not realise thats happened

so i wondered if maybe this was happening, and this was why you had apparently missing records.

 

[gocoder]

How to let multiple users work on the same data base on LAN ?

 

[CJ_London]

double posting, OP has started a new thread here

http://www.access-programmers.co.uk/forums/showthread.php?t=273836