Data Won't Save - Disappears

[Momo3]

I spent several hours entering and updating data in my Access program yesterday. All data appeared to be saved as it appeared in report totals at the end of my data entry session. However, after quitting Access and restarting, the data is missing, new records, changes and deletions. It is no longer showing up in reports, and if I look directly at the table data it is not there either. If I were working with a commercial relational database, it would be as if it had done a rollback, or conversely, I had not "committed" the changes.

This did happen to me one time before where Access seemed to accumlate changes, only to disregard them when shutting down. I had to export the database and re-import to get it to properly save changes again. Just doing the Compact and Repair did nothing. There are no visible error messages when Access stops accepting changes. I do have my frontend forms and reports separate from my backend data, though both are sitting on my PC in the same directory.

Thank you for any light you can shed.

 

[Rabbie]

Which version of Access are you using and which Service Pack is applied. To find out click the Help menu and click on About Microsoft Office Access.

 

[Momo3]

I am running Access 2007 (12.0.6211.1000) SP1 MSO (12.0.6320.5000)

 

[Rabbie]

See this link for some known problems with A2007.

 

[Momo3]

I didn't see anything that seemed to apply. Is there a way to force a commit in Access 2007?

 

[gemma-the-husky]

are you using linked tables or local tables - perhaps you have several copies of the data, and you are not processing the one you think you are

 

[Momo3]

I am using linked tables, but I have triple-checked that there is only a single copy of data. Is there a way to force a commit?

 

[gemma-the-husky]

i am sure the behaviour IS to commit - i cant believe you could see the data without it being saved.

the true path of a connected table is given by

currentdb.tabledefs("anytable").connect

so just try

sub showme
msgbox(currentdb.tabledefs("anytable").connect)
end sub


to see where the data really is.

 

[gemma-the-husky]

another thing

open the bckend directly with one access session

open the front end with another session

do some stuff in the front end

then see if the data is really in the back end - just check the tables

 

[Momo3]

Thanks for the suggestions. I ran your code and am definitely pointing at the correct files. I am going to try rebuilding my databases and defragging my disk and hope that helps. If it happens again, I'll be back looking for help. Thanks!

 

[CraigDolphin]

I have had a similar issue when I reached the upper limit of my allotted quota of space on a network drive. I'd enter in new data, get no errors, and when I closed and reopened the database, the data that I'd just entered was AWOL. So if your db is on a networked drive with restrictive storage caps for users, or your hd is about full, these might also be factors to consider.

 

[Momo3]

Thanks Craig. That could very well have been my problem. I was down to very minimal space. I've got free space now and am keeping my fingers crossed. Thanks for the help!