#DELETED - but not really

  • Thread starter Bryan in Bakersfield
  • Start date
B

Bryan in Bakersfield

I am having a occasional problem with records that will show up on the form
but then the data from the table will change to #DELETED. I know that this
is supposed to mean that the record was deleted from the table but that is
not the case. The table that the form is running on has an Autonumber field
and no records are missing.

The form runs off of a select query for the table:

SELECT DailyCallFile.CallID, DailyCallFile.SSN, DailyCallFile.TimeZone,
DailyCallFile.CallDate, DailyCallFile.CallType, DailyCallFile.DateWorked,
DailyCallFile.User, DailyCallFile.Result, DailyCallFile.Status,
DailyCallFile.Comments
FROM DailyCallFile;

But it's just a SELECT query with no Joins or WHERE, so this shouldn't be
the problem. The Allow Filters and Allow Deletions are both set to No also
and the Data Mode is not set when opening the form.

It only happens about 1 of every 5000 records worked for various users. Can
anyone give me an idea of why this is happening so I can try to correct it?
 
J

Jerry Whittle

Is the DailyCallFile.Comments field, or any others, a Memo data type? If so
your tables are probably corrupted. Make a backup of the database files right
now and put it in a safe place.

Then do a compact and repair. See if that fixes it. I'm assuming that you
tried a compact and repair.

Are there any weird symbols in a memo field? If so, here's what to try.

1. Again make a complete backup of the database mdb file(s) and put away for
safe keeping.

2. Create a query that excludes the particular problem record. Include all
fields or use the * wildcard. You need a primary key for this to work so that
you know exactly which record. An autonumber field is best if you don't
already have a PK. After running the query to ensure the proper records are
returned, change it to a Make Table query and run it.

3. Next change the query back to a Select query. This time only search for
the problem record. Make sure that all fields show EXCEPT for the problem
field. Run it to make sure. Then change the query to an append query and
append the bad record to the table that you just created.

4. Delete the corrupt table. Rename the new table with the same name as the
old one. You might have problems if there are relationships involved.

5. Create a new database and import everything from the old database. Use
this DB in the future.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
 
B

Bryan in Bakersfield

I'm finally got time to try and figure this out.

Unfortunately, there are no Memo fields in the DailyCallFile table. I only
have text, date, or int - along with an Autonumber. There isn't currently
any corruption in the tables - though there has been before. I compact and
repair after deleting those records.

I checked my VB that selects the record. I was thinking that I may have
used a global variable in the VB that was used by the query, but I had used
the Where Criteria instead.

stLinkCriteria = "[CallID]=" & ast("CallID")
DoCmd.OpenForm stDocName, , , stLinkCriteria

I wonder if the Where might be cleared like a global variable in the event
of a VB error? But then I think it would pull up all records - one at a time
- not give #ERROR. Also, any errors should be caught and displayed and
everyone who had the problem said there was no error - of course that
wouldn't account for any possible silent failures. They know the error when
the network loses connection - so I don't think it's a network problem.

Unfortunately, the problem is rare it's hard to pin it down. I don't want
to leave the database open though if I did I would be able to check it when
the users are actually using it.
 
Top