table problem, field shows #deleted

[Eljefegeneo]

I have a split DB

A table has a field named [ProgramNotes] where the designation "#deleted" has come up for two records. When I tried to reinsert the data from an old backend in this field it says that "Your changes cannot be saved because the field "ProgramNotes" has been updated by another user.

First of all, would this happen without any notification that another user was on the same record? And since I am not on the server copy of the back end but one on my desktop, why would this message in the paragraph above come up?

But aside from that, I figured out a way to correct this but want to make sure that I am doing it correctly. I compacted and repaired the back end (which always gives me dread to even think of doing this). The two records now show "################" in the field. If I delete this "data" if one can call this data in the errant record, then I can retype data from an old saved copy of the back end. I guess I should also ask what this "################" means.

So, (1) what happened and why, and can I do anything to prevent this in the future and (2) did I fix it in the correct manner? Any and all suggestions and comments will be appreciated.

 

[Pat Hartman]

This is some type of corruption. To recover, create a new, empty BE and import all the tables EXCEPT the one with the corrupted records. Then I would take an old copy of the BE, prior to the corruption and import the table design only for the "bad" table. Then run an append query that EXCLUDES the two bad records if you can identify them by PK. Finis the data conversion by creating the two records by hand in a temp table. Then run an append query to append the records to the fixed up table. This part is necessary only if your PK is an autonumber. The only way to add a row to a table and supply an autonumber value is by using an append query. You can't just type in an autonumber value.

Once all the data is imported, make sure that all the primary keys and indexes are recreated and then recreate the relationships. Make a backup of the database, then compact it and give it the original table name.

Don't forget, you need to do this off shift while NO ONE is accessing the production database. To ensure that no one updates the database while you have the BE offline, rename the production database so the app will fail is someone attempts to use the app until you are ready.

 

[Eljefegeneo]

Wow, that sounds very complicated. I'll try it, and I am sure coming from you it will be just fine.

Just as a question, is there something wrong with the way I "fixed" it? Are you suggesting that doing a compact and repair on the back end is risky? I am asking this for future knowledge.

Thanks.

 

[The_Doc_Man]

I guess I should also ask what this "################" means.

Typically, you see that string of octothorpes because the field is too narrow to show the real value. Happens with date fields quite a lot, but can happen with other fields too. If you can use drag-n-drop techniques to widen the field, you might ... stress MIGHT ... see what is actually there, but no promises. And of course you don't have to save changes when you close the table that you examined that way, so the field width change wouldn't be permanent.

As to what is going on, the persistent "#deleted" records were probably being written back as part of an update when someone exited the DB abnormally - power fail or network drop are the #1 and #2 guesses for "abnormal" with a forced reboot (such as but not limited to an insistent Win10 O/S update) at #3. That bad DB session termination left some markers in a half-updated state. And I suspect the data for your string of octothorpes might have been involved. In particular, if that was a string data type, the way that strings are stored leaves them susceptible to this specific error of "partially updated" status.

 

[Eljefegeneo]

Thank you for your explanation of why the error might have occurred. I shall pass this on to the miscreants who possibly caused the problem and tell them to notify me of any lost connections while using the DB remotely. Since one user is doing this every day, it is possible that he lost connection with the remote desktop which does have it own front end and the server which has the back end. Possibly the LogMeIn connection dropped him arbitrarily.

Initially I thought that the "################" was the usual text item too large for its space, but actually it is what appeared in the query datasheet view after I did the compact and repair of the back end. In any case, I could then delete the "data" and then type in the correct data.

 

[The_Doc_Man]

If it is literal strings of octothorpes, the next question is how would that field normally be filled? You might see that if you did something like format a string based on an actual data length too large for the stated format size. But you say it is in a query? What does the underlying table say? (Or what DID it say?)

 

[theDBguy]

Hi. If you did a C&R and there were errors, you should see an error log created by the process. It might tell you what is the problem.

 

[Eljefegeneo]

The DBGuy: Unforunately I did not look to see if there was an error log. Since I have copied and C&R various copies of the backend, I cannot seem to find anything that says there were errors. If it ever happens again, I will certainly look for it, but hopefully it won't happen again.


Doc Man: The query would not bring up the records. It only said #deleted on the form. When I ran a query it said record deleted. Of course it wasn't deleted, only an error in one field.

 

[Eljefegeneo]

I checked the backend that I did the C&R on that was on the server and there was no error log.

 

[The_Doc_Man]

If it was a query, check the table that was the source of the query. If you can identify the offending record by some key value, you might be able to see something different.