#deleted in all fields (2000/2002)

[alans]

A user was using my DB. Its a form which ties into an underlying table. She says she filled in the form, went to another field and all the fields in the form went to "#deleted#. I have looked at the table and sure enough the data contains "#deleted". I cannot change any record and I cannot delete that row (record). If I try and delete it, it appears to do it. If I close it off and re-open it, the record is still there.

Hellppppp

Alan

[HansV]

You may have one or more corrupt records in the table. Try compacting the database (Tools/Database Utilities/Compact and Repair Database...). If Access finds corrupt records, it will replace corrupt values by #### and create a table MSysCompactError with information about the corrupt records.

Microsoft has a JetComp utility that can repair some databases that the internal command can't. You can download it from ACC2002: Jet Compact Utility Available in Download Center.

If this doesn't help, you can try to import as many database objects as possible into a new, fresh database. Or go back to a recent backup copy (you make backups regularly, of course <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

[alans]

I managed to compact the DB and it seems OK. The sys message says 'could not find field "description"' - but there is no field called description and I make no reference to it as far as I know.
So, any idea what could have caused this ?

[HansV]

Have you had a field 'Description' in a table (or query) in the past that has now been deleted or renamed? If so, the Filter property of the table or query (or even of a form or report based on it) might still refer to 'Description'.

[alans]

No, nothing at all.

Reading the docs for jetcomp, it implies that the #deleted stuff may have been caused because of record contention. Now, I have not changed the default record locking which is set to "shared", "No locks", "Open database using record level locking".

Should I get all the users to change their default record locking to "all records" or to "edited record" or just leave it alone ?

Alan

[HansV]

Perhaps others will comment on this too; I would leave the record locking settings as they are.

If the problems persist, it may be a good idea to import all database objects into a new blank database, and use that instead of the old one (keep a backup copy of the old one). You will have to re-create the startup options of the original database manually in the new database.

[WendellB]

I agree with Hans - don't mess with record locking unless you have continued problems. Another thing you should do if you haven't already is to create a split database with a front-end containing the queries, forms, report and modules, and a back-end with just the tables. That should reduce your corruption problems significantly. There have been numerous threads on that subject recently - Search will find them for you.

[alans]

Wendell, I have done that. It has reduced the corruptions a lot. We just had this one instance of the #deleted issue yesterday which I don't want repeated because I am off for 6 weeks (honeymoon) and there will be very little support !!!!!

Cheers

Alan

[WendellB]

I understand your concerns - unfortunately Jet based backends will occasionally suffer corruption, usually because somebody's PC locks up. Have you move the front-end to the individual user's PC? That also seems to help some, and it makes design changes easier as you can do development in a test front-end and then deploy without having to kick all users out. How many users does your database support? Depending on the number you may want to start looking at SQL Server for the back-end - it's much more robust.

In any event, you may also want to upgrade your backup situation. There are several methods for do backups, and some of them can be run several times a day. If you choose that kind of strategy, you can recover to a close to current situation if the unthinkable happens and the back-end is completely corrupted.

[alans]

The front ends are all on the users PCs. We get occasional front end lockups which is easily corrected. I had one back end corruption today (1st time in 2 days) which I was able to repair. I have between 6 and 12 users accessing it at any one time. I believe the lockups/corruption are caused by record contention in the .ldb file but I have no way of proving that.

We do a backup every day and I have been taking copies of the back end db every few hours. Burt I am now no longer on site so I will have to instruct someone to keep making copies every so often.

many thanks for your help so far - this forum has proven very useful.

regards

Alan

[WendellB]

If you are having record/page locking problems, you should be getting a message that someone else has edit a record, or locked a page, or something like that. Is lots of your activity data entry, or is most of it lookup or record editing? If you are getting corruption on the other hand, there typically isn't any error message - if that's the case, your network is most likely suspect, as Jet is very sensitive to network hicups.

[alans]

Nteworks could be the cause. It we seem to get much more issues when there are users coming in from the WAN. But I have no control over that and it won;t be changing soon. So I guess we have to live with it.

It's been OK today !!!!

Thanks

Alan