• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

MS Access 2007 unable to save - data lost

Hi, we had a network hiccup for 10 minutes and when a user tried to resave the MS-Access table to the SBS 2011 network drive where they reside he got the following message.

"Your network access was interuppted. To continue close the database, and then open it again."

We are unable to save the database, tables or objects locally as it gives the same message.

So we have had to close the database down and start again.

Questions:

1. Why doesn't Access resuem when the connection resumes?
2. I can't find an auto-save facility in Access, like Word or Excel.
3. We seme to have lost all the updates since the last forced save.

A lot of people seem to eb reporting this style of problem going back to Access 2000. What is the work around so that we don't have staff losing data that they have typed in.

Many thanks.
0
edhasted
Asked:
edhasted
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Answers you won't like...

>1. Why doesn't Access resuem when the connection resumes?
Access does not do very will with dropped connections.  If a connection is dropped and then re-established during a record add or edit before it is saved, it does not ultimately save it.

>2. I can't find an auto-save facility in Access, like Word or Excel.
That's because it doesn't exist.  Records are saved only when the user effectively navigates to the next one or VBA code says to save it.

>3. We seme to have lost all the updates since the last forced save.
My condolences.
0
 
edhastedAuthor Commented:
Don't worry but it would appear that Access had allowed the user to make a lot of edits to the database which weren't saved and didn't give an error message until he tried to force a save. Does this make sense as you're saying it saves after you edit each record, or rather go onto the next?

Is there anything I can do to make the process more robust? It would appear that a switch played up which is very rare but the user still lost a lot of work...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Access had allowed the user to make a lot of edits to the database which weren't saved and didn't give an error message until he tried to force a save.
If the connection was broken, the user probably made edits to the persisted data from the link table, but as soon as 'linky no worky' no saves would be successful.

>Does this make sense as you're saying it saves after you edit each record,
When you make a change (insert, update) in one record, then navigate to another, Access will automatically save that changed record to the database.   What seems odd here is that the connection failed and no error message appeared, giving the user the impression that all changes were being saved.

>Is there anything I can do to make the process more robust?
Hmm ... not really, other than moving data to a local table, editing there, saving there, then saving to the database, which is a boatload of work.
0
 
peter57rCommented:
You could try adding..

DBEngine.Idle dbFlushCache
to the form afterupdate event .

It worked once upon a time to force writes to disk, but it's anybody's guess whether it still does anything.
0
 
edhastedAuthor Commented:
I am slightly baffled that Access is so carefree in the way that it treats data. I would have thought after 18+ years of the product there would have been some resilience built in by now. I know people who use it networked in financial institutions and the ramifications for their data if they had an outage liker we had doesn't bear thinking about.

I'll try this.

Where exactly do I add DBEngine.Idle dbFlushCache and will report back if it works.
If so can I put it in an Access template so it always gets added in whenever a user creates a new Database or will this have to be hand crafted in each time.

Many thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now