Comments Error ######## // Record Is #Deleted

[access2010]

MsAccess 2003 periodically shows the following errors in our two Comments fields;
######## or Record Is #Deleted
The table we are using has 36 fields of which 2 of the fields are comments.

The above error message occurs occasionally. when entering data through a form.

We weekly run these utilities => Tools Compact & Repair => Help Detect and Repair

Should we place the Two Comment Fields into a;
A = Separate Database and link them to the form?
B = Separate Table and link them to the form?

Your suggestions will be appreciated.
Nicole

 

[The_Doc_Man]

Unless you are looking at a number of records SO large that your DB as a whole would exceed 2 GB, you would NEVER put related comments in a separate database. NEVER EVER. Among other things, if you were trying to maintain relational integrity, that only applies to two tables in the same database that have a declared formal relationship. Having to maintain a relationship between parts of a split record when the splits are in two different files? You would quickly find out WHY you don't want that headache. The database you showed measured in KBytes, not even MBytes and certainly nowhere close to GBytes, so sizing shouldn't be an issue.

Looking at what you posted, which appears to only be a small table, that content that I see is not a formatting error of Access. When I tried to export it to a text file, nothing significant changed. Therefore, whatever you used to load that field put the "#####...#####" in it legitimately. I would look to the data source to see if whatever loaded that value or input that value has a data error in it that would trigger a formatting error, because that is what it looks like to me. More specifically, a formatting error in which you have an explicit format string but whatever your format string specifies, you gave it more data than the string would support.

"Record is deleted" errors might indicate corruption, but I didn't see anything during the export that suggested a record had been deleted and I saw no signs of corruption. From a consistency/integrity viewpoint, what you uploaded looked structurally OK.

I'm not going to say this is the only way to get that error, but if you have two users in the same back-end file and both are looking at the same table, and if one of them deletes a record but the other person does nothing, the other person will eventually see a "record is (or was) deleted" message when the auto-refresh cycle is triggered. The auto-refresh time is in the File >> Options >> Current Database section somewhere and it relates to essentially an auto-refresh of displays have been left up for longer than that auto-refresh interval.
.

 

[access2010]

Unless you are looking at a number of records SO large that your DB as a whole would exceed 2 GB, you would NEVER put related comments in a separate database. NEVER EVER. Among other things, if you were trying to maintain relational integrity, that only applies to two tables in the same database that have a declared formal relationship. Having to maintain a relationship between parts of a split record when the splits are in two different files? You would quickly find out WHY you don't want that headache. The database you showed measured in KBytes, not even MBytes and certainly nowhere close to GBytes, so sizing shouldn't be an issue.

Looking at what you posted, which appears to only be a small table, that content that I see is not a formatting error of Access. When I tried to export it to a text file, nothing significant changed. Therefore, whatever you used to load that field put the "#####...#####" in it legitimately. I would look to the data source to see if whatever loaded that value or input that value has a data error in it that would trigger a formatting error, because that is what it looks like to me. More specifically, a formatting error in which you have an explicit format string but whatever your format string specifies, you gave it more data than the string would support.

"Record is deleted" errors might indicate corruption, but I didn't see anything during the export that suggested a record had been deleted and I saw no signs of corruption. From a consistency/integrity viewpoint, what you uploaded looked structurally OK.

I'm not going to say this is the only way to get that error, but if you have two users in the same back-end file and both are looking at the same table, and if one of them deletes a record but the other person does nothing, the other person will eventually see a "record is (or was) deleted" message when the auto-refresh cycle is triggered. The auto-refresh time is in the File >> Options >> Current Database section somewhere and it relates to essentially an auto-refresh of displays have been left up for longer than that auto-refresh interval.
.

Thank you for your response.
The table that we use is 19,268 KB and many of the fields and data have been removed from the uploaded file for an easier preview.
Only one volunteer has access to this database at a time.

The comments are part of a data entry sequence and the person would only be at the comment field for a few seconds.
Crystal

 

[The_Doc_Man]

Nothing jumped out at me when I looked at your file. No signs of corruption, no particular idea why you would get the pound-signs (octothorpes, hash-tags). I also checked the table definitions for possible format strings that could cause the problem, but there are none. I repeat that from what I see, this is not explainable from the evidence and exhibits you presented.

To see a "deleted record" in a table makes no sense unless someone had a problem performing a deletion such that the deletion started but didn't finish - which would be REALLY strange, given that Access is supposed to be single-threaded and not interruptible at SQL level. I will do other research but I have to say that I don't think we have enough info to do much good for you.

 

[access2010]

Unless you are looking at a number of records SO large that your DB as a whole would exceed 2 GB, you would NEVER put related comments in a separate database. NEVER EVER. Among other things, if you were trying to maintain relational integrity, that only applies to two tables in the same database that have a declared formal relationship. Having to maintain a relationship between parts of a split record when the splits are in two different files? You would quickly find out WHY you don't want that headache. The database you showed measured in KBytes, not even MBytes and certainly nowhere close to GBytes, so sizing shouldn't be an issue.

Looking at what you posted, which appears to only be a small table, that content that I see is not a formatting error of Access. When I tried to export it to a text file, nothing significant changed. Therefore, whatever you used to load that field put the "#####...#####" in it legitimately. I would look to the data source to see if whatever loaded that value or input that value has a data error in it that would trigger a formatting error, because that is what it looks like to me. More specifically, a formatting error in which you have an explicit format string but whatever your format string specifies, you gave it more data than the string would support.

"Record is deleted" errors might indicate corruption, but I didn't see anything during the export that suggested a record had been deleted and I saw no signs of corruption. From a consistency/integrity viewpoint, what you uploaded looked structurally OK.

I'm not going to say this is the only way to get that error, but if you have two users in the same back-end file and both are looking at the same table, and if one of them deletes a record but the other person does nothing, the other person will eventually see a "record is (or was) deleted" message when the auto-refresh cycle is triggered. The auto-refresh time is in the File >> Options >> Current Database section somewhere and it relates to essentially an auto-refresh of displays have been left up for longer than that auto-refresh interval.
.

We / I appreciate your assistance in helping us solve or problem.
Would changing the field from a Comments field to a field size of 250 eliminate our continual errors?

Nicole

 

[gemma-the-husky]

You might have a corrupt memo field in a record somewhere, often visible as "Chinese" characters.

If you can find the bad record you might be able to copy the table into a new table in parts, ignoring the correct record. If it's linked in relationships, you might have to delete the relationships, and recreate them with the repaired table.

Copy your database before you try anything in case you make the problem worse.

That write conflict error may be a real error, possibly caused by you editing the same record in two different forms, which interfere with each other. That might just be a database design issue, or 2 users might actually be changing the same record at the same time.

 

[The_Doc_Man]

Would changing the field from a Comments field to a field size of 250 eliminate our continual errors?

I am not sure why your problem occurs so it is hard for me to say what would fix it. I would only be guessing. You don't deserve guesses but at the moment, I don't even have THAT much for you.

I have to ask this question because I'm trying to figure out how many players are playing in this game. Do your users directly enter into the table or is there a form to manage this data entry process? When the users make their entries, are they using the same computer or is there any sharing across multiple computers? If so, what kind?