Question Data not saved after program exit

[Vigilante001]

Hello gurus,

Some users of a database I created have reported times when they would enter 10+ lines in Datasheet view, close the program, and those newly entered records would be gone. The old ones from days past are still there. Furthermore, this behavior of not saving data happens inconsistently, and only on a couple computers (4 out of 30). By the time they bring the computer in, it stops doing it. There are no errors that they see when entering...

Has anyone encountered a situation where Access does not automatically save data? I thought that was one of its basic features!

 

[gemma-the-husky]

if there is an error for some reason, but you are suppressing errros- eg with

docmd.setwarnings false

then you may not realize the records have not been saved

how Are you saving them?

 

[Vigilante001]

Thank you both for the quick replies...

gemma-the-husky::

The user enters one of two ways (form or datasheet).... I made a makeshift split-form (like Access 07) but for 2003 because the users run Access 03. I did this with a datasheet subform that is directly linked to the table, and unbound textboxes in the main form that load the table's data for whatever record the user chooses (think exactly like Access 2007 split form)
After each unbound text receives new/edited data, through SQL I do an UPDATE string to ensure the data is synced in near real-time. All field values requery after each entry. If data is entered/edited into the datasheet, it is essentially llike writing it to the table.

I have incorporated a handle so that the user must initiate a new row before the SQL strings run, though, so any time new data is entered, it is ensured to link with a table row. Furthermore, if that mechanism failed, the requery would show that records are not receiving data after the first try.
(again, like Split form not displaying your new row in datasheet after typing it in form view)

I'm only error suppression I'm using is with the SQL strings, and that resumes (WarningsOn) immediatly after. When users report the glitch (again 4 out of 30 users, and those 4 only see it 1% of the time), they see all records in both views... so the data is transfering. But when they close the database and open it, the new data is gone.

Good idea though, and that is why it's so frustrating...I have yet to see an error. All other errors are fatal so that I get a call about them & fix them.


Pat Hartman::

I'm sorry, still noob enough to need orphans, KI, and RI spelled out. Be glad to respond after that...


One new development was that a recent user having the glitch couldn't see C: in My Computer, noly network drives. Any leads?

 

[The_Doc_Man]

In a relational database, relational integrity (RI) works like this.

You define a relationship between two tables, one of which is the parent and one of which is the child. The parent has a prime key (PK). The child has corresponding values as a foreign key (FK). Parent/child RI means

If you try to add a child record for which the FK does not match an existing PK value in the parent table, you can't add the record.

If you try to delete a parent record for which some child records exist, RI will delete the child records first.

You said you are using multi-form operations. I suspect that there is an error in the relationship or in the way the form implements the relationship.

 

[Vigilante001]

The_Doc_Man::

Thank you for the clarification/insight... so my next question is, how would one catch if an RI issue is causing data loss?

I get it right, RI prevents unlinked/unrelational entries from existing in a relational database, and in my database, the relationship between the unbound fields(FK) in the form and the subform datasheet fields(PK) might mismatch from time to time, thereby transferring the data to nowhere because the PK has been deleted?

Here's the next question (if the above is correct):

1. Would a user be able to enter multiple rows into a form (FK) that was messed up, and see them appear to the datasheet (PK referencing the table)?

 

[Vigilante001]

Also, I checked the relationships window and I have none, though that is expected because only one table is being interacted with. I will keep RI in mind should I link it with another.

 

[gemma-the-husky]

I'm only error suppression I'm using is with the SQL strings, and that resumes (WarningsOn) immediatly after. When users report the glitch (again 4 out of 30 users, and those 4 only see it 1% of the time), they see all records in both views... so the data is transfering. But when they close the database and open it, the new data is gone.

if your data is not being added at all - this must surely be the culprit

if you are using docmd.runsql, with warnings off, try it with warnings on to see what is happening

then instead use currentdb.execute sqlstrg instead, with error trapping enabled, to suppress the warnings in a different (better) way

 

[Vigilante001]

gemma-the-husky:

But the data is being entered during that session. Requerying the subform (sourced to the table) shows those records as present. Then, close the database and open it, and they would be gone. Would that still happen if the SQL were failing? Alas... and good call on the Execute, I learned about it after making that part of the DB, and will be switching it soon...

 

[gemma-the-husky]

there MUST be something going on with the update

i dont quite follow your set up, but you need to debug and check it carefully

could it be that regional date settings are wrong on the funny computers,? i have seen that cauise errors in writing data.

 

[Vigilante001]

I'll be doing just that tomorrow... hopefully I find something. If it is still doing the error, then I'll be able to troubleshoot it. I'll post back if I figure it out... Thank you all for your help. I'll check the error trapping too...ugh!

 

[Vigilante001]

Pat Hartman::

1. Yep, the table itself didn't have the records, so it wasn't a filter block on the form.
2. DataEntry is set "No", so the users see all records created.
3. That's the kind of tip that makes you lean back and say "Ohhhh...". Good to know if I decide to revamp the whole thing, and coding it that way would make it much easier to add new fields in the future. The only problem is the single form doesn't allow edits... just viewing. Is there a work around for that?

One idea I had for not saving was if for some reason the user opened two copies of the database, would one be read-only and not save records? In that case would the records show up in the table?

Thank you for the reply and good coding advice...