Pound sign in Access generates an error

[Guest]

We are having a Access Database (v 2003) which contains number, text and
memo field types. There is a record in the database that contains the string
"#Error" (without the quotes) for a memo field. This gives a problem when you
use this data in a DTS package for datatransformation. Opening the tabel and
selecting the "#Error" record results in the following error message:

The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time.

Inserting the string "#Error" in a memo reproduces the error. We have't
found a way to remove the record containing the "#Error". The record
containing the "#Error" string is inserted in the Access database via a C#
program.

Does anyone know if there is a way to remove the records containing "#Error"?
Is the usage of the #-sign in Access allowed in columns memo field?

Regards.

 

[Steve Schapel]

Gaby,

This sort of thing is normally a symptom of data corruption. It sounds
like this is happening frequently, so you will need to investigate the
cause. I was not able to reproduce the problem by simply inserting
"#Error" into a table, memo data type or otherwise. And there is no
restriction to the use of the # character in Access data.

There is some good information aout corruption at
http://www.granite.ab.ca/access/corruptmdbs.htm

The only way I know of to remove the corrupt record is to make a new
table, run an Append query from the existing table into the new table
for all records prior to the corrupt one, and then another append query
for all records following the corrupt one, and then delete the original
table and rename the new one to the old name.

 

[Ken Snell [MVP]]

Just to add to Steve's comment, note that it's not as easy to search for #
sign in data by using Like operator. To find the # character, it must be
delimited in the Like expression by [ ] characters:

Like "*[#]*"

 

[Steve Schapel]

That's true, Ken. But in the case of a corrupted record, you won't find
it anyway, no matter how you search.

 

[hermie]

That's true, Ken. But in the case of a corrupted record, you won't find
it anyway, no matter how you search.

--
Steve Schapel, Microsoft Access MVP

Just to add to Steve's comment, note that it's not as easy to search for #
sign in data by using Like operator. To find the # character, it must be
delimited in the Like expression by [ ] characters:

Like "*[#]*"

 

[hermie]

I had de same problem but found the #error rapidly by sorting each column 1
by 1


herman

That's true, Ken. But in the case of a corrupted record, you won't find
it anyway, no matter how you search.

--
Steve Schapel, Microsoft Access MVP

Just to add to Steve's comment, note that it's not as easy to search for #
sign in data by using Like operator. To find the # character, it must be
delimited in the Like expression by [ ] characters:

Like "*[#]*"

 

[Ken Snell [MVP]]

Agreed. I was not thinking of using this for that kind of search.

--

Ken Snell
<MS ACCESS MVP>

That's true, Ken. But in the case of a corrupted record, you won't find
it anyway, no matter how you search.

--
Steve Schapel, Microsoft Access MVP

Just to add to Steve's comment, note that it's not as easy to search for #
sign in data by using Like operator. To find the # character, it must be
delimited in the Like expression by [ ] characters:

Like "*[#]*"