Data going missing

[303factory]

Hi

We're having a problem where data entered into our database by form is not always being saved

The data entry is into continuous subforms which are bound to an msaccess query. The query links to the datatables which are SQL Server 2008 using an ODBC connection with the native client 10.0 driver

Staff will enter as much as 70 new rows into the form and then when they leave and come back to it, it will all be gone. Unfortunately the error isnt repeatable it seems to happen at random.

I used to think this was one of the problems with an access back and and was hoping upgrading to SQL Server would fix this, but the problem is still happening every now and again. Obviously it's a big problem if someone has spent an hour or two typing in data.

Any suggestions why this may be happening and what safeguards I can put in place to make sure the data is saved? Could I introduce something that periodically force-saves the data from the form to the database instead of relying on my access client to save the data as its typed?

Regards

303

 

[vbaInet]

There could be a loss/drop in connection during the time those lost records were added. Not sure.

Try performing a Me.Requery for every 20 (or so) new records that are added.

 

[303factory]

if connection was lost at the point you performed a requery would you expect it to produce a debug error or hang?

 

[vbaInet]

It may well do since the requery function is trying to fetch records from the server. Not sure of the result, try it out and see what happens. Unplug the network cable and run the requery function. Obviously, do this on a test database just to be on the safe side

The only reason why I proposed a requery is to ensure that the records are saved after every how many records you wish.

 

[303factory]

yeah ill definately give it a go, better staff get a debug then carry on entering data into the black void of nothingness

 

[vbaInet]

Goodie! Keep us posted.

 

[303factory]

Right, three problems with this theory

1) The Me.Requery command causes the form to scroll back up to the top, which will be annoying for the user

2) When I disconnect the network cable, all the fields onscreen say #Name, which definately does not happen when we loose data, so this is not a good way to model the problem I am having

3) Despite not being connected to the network, the Me.Requery command does not cause a debug!

So I'm a bit stuck again.. anything else I could try?

 

[namliam]

Is this a multi user database with a single backend where temp tables might get deleted/truncated?

 

[vbaInet]

Are you using the Execute method of CurrentDb in any of your code? If you are, is there a dbFailOnError as one of its parameters?

I would still go with the Me.Requery as a safety net. To get back to where you were after a requery:

1. Save the ID of the current record before requerying
2. Use the GoToRecord method of DoCmd to move back to the current record using that ID you saved.

Here's another from pbaldy:

http://www.baldyweb.com/Requery.htm

 

[303factory]

Is this a multi user database with a single backend where temp tables might get deleted/truncated?

Yes up to 15 people use the front end client which connects to a single backend.

I know nothing of tempt tables or them getting deleted/truncated, can you point me at some literature on this?

 

[303factory]

Are you using the Execute method of CurrentDb in any of your code? If you are, is there a dbFailOnError as one of its parameters?

I would still go with the Me.Requery as a safety net. To get back to where you were after a requery:

1. Save the ID of the current record before requerying
2. Use the GoToRecord method of DoCmd to move back to the current record using that ID you saved.

Here's another from pbaldy:

http://www.baldyweb.com/Requery.htm

Thanks for the pointer on getting back tot he current record

I'm not using the executem method in any code, i simply have a continuous subform bound to a query. the form itself may have no code at all. Could this be related to my problem?

 

[vbaInet]

No code at all. Hmm... this could be network related. Are there certain staff it has happened to? It might be worth getting a new NIC for those computers.

How is the FE split? Each computer has a copy of the FE or there access the FE from a shared folder?

 

[303factory]

Yes it does seem to happen to certain people and not others. What doy ou mean by NIC?
Each computer has a copy of the FE

 

[vbaInet]

This is what I will do:

1. Get some brand new Network Cards and replace theirs
2. Might also be worth replacing their network cables as well
3. Re-install their FEs

 

[303factory]

is there anythign that can be done in my client to flag up network problems? I'll need some sort of proof this will work before I can get funds released for this.

I'm not having any luck with the Requery - if I do it on a timer and go back to the record using a bookmark it puts the cursor in a different field, and if I do it on the 'after-insert' then it re-orders my form so the new record is at the top and not at the end where it should be.

 

[303factory]

is there anythign that can be done in my client to flag up network problems? I'll need some sort of proof this will work before I can get funds released for this.

I'm not having any luck with the Requery - if I do it on a timer and go back to the record using a bookmark it puts the cursor in a different field, and if I do it on the 'after-insert' then it re-orders my form so the new record is at the top and not at the end where it should be.

Ingnore this.. I've told it to do do a requery then goto new record on the after insert.. things will re-order but only if the user hasnt been filling out the time and date information..

still interested to know why this is happening and if there is some cunning 'check network' function i can use on a timer but have solved getting the requery to work in a paletable way

 

[vbaInet]

Ingnore this.. I've told it to do do a requery then goto new record on the after insert.. things will re-order but only if the user hasnt been filling out the time and date information..

If you're doing a requery after EVERY insert then it will affect traffic. Which was why I suggested you do a requery after every 20 or so Inserts. Up to you which number you choose.

1. Create an Integer variable on the form's Declarations section.
2. Add a one to it for every INSERT
3. Perform your check and if it's 20, perform the requery and set the variable back to zero.

still interested to know why this is happening and if there is some cunning 'check network' function i can use on a timer but have solved getting the requery to work in a paletable way

If you want a way of monitoring the network then you should be looking to purchase a software specifically for that purpose.

Network cards aren't expensive. I'm sure for the convenience (and a possible solution) they wouldn't mind purchasing those.

 

[303factory]

If you're doing a requery after EVERY insert then it will affect traffic. Which was why I suggested you do a requery after every 20 or so Inserts. Up to you which number you choose.

1. Create an Integer variable on the form's Declarations section.
2. Add a one to it for every INSERT
3. Perform your check and if it's 20, perform the requery and set the variable back to zero.

If you want a way of monitoring the network then you should be looking to purchase a software specifically for that purpose.

Network cards aren't expensive. I'm sure for the convenience (and a possible solution) they wouldn't mind purchasing those.

Yep it only does this for every 10 records.
heh dont underestimate the bureaucratic shackles of my company
ill take a ganders at network monitoring software too

 

[vbaInet]

Ha! Good luck negotiating. Just tell them you have a strong feeling it's the NIC and bamboozle them with a few technical terms too