Deleted record problem

[Joseph M. Newcomer]

Microsoft Access 2003 11.6355,6408 SP1

We have a database which under some conditions appears to work, but after any editing of
any kind, goes into a mode where it says "duplicate record". Under the latest scenario,
we can no longer even open the database!

The primary key is called "InstId" and is an autonumber field.

When the database can be opened, we can sort on the primary key, which is numeric. But
when we do this, the keys sort as

169 Mor...
527 Institute...
171 Cesk...

Then there is the sequence

232 Rut...
233 University...
234 Eastern...

and finally the sequence

526 Muse...
233 University... <same as record between 232 and 234>
528 Museum...

If we do anything at all to this database, we end up with

526 Muse...
#Deleted #Deleted ... #Deleted
528

Any attempt to re-open this database gives an error that says "duplicate record" and we
can no longer open it.

Any attempt to do a query on this database gives an error that says "duplicate record" and
the query produces a long list of entries that say #Deleted in all fields

I have checked, and we are running msjet40.dll version 4.0.9511.0, the very latest version
of the Jet database engine listed for XP (we are running on XP Pro SP2 with a variety of
updates beyond that). I had already found the KB article about the #Deleted problems, and
this was the only one that looked promising.

We need a way to fix this problem so the database is usable again. If this is not the
correct newsgroup, please advise as to which one we should post in.
TIA
joe
Joseph M. Newcomer [MVP]
email: (e-mail address removed)
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm

 

[Tom van Stiphout]

On Sun, 22 Feb 2009 16:03:10 -0500, Joseph M. Newcomer

You seem to have some corruption in that table.
Repair & Compact the database.
If that does not help, export the good rows to a new table, drop the
old table, and rename the new table to that name, and fix up the
relationships.

-Tom.
Microsoft Access MVP

 

[Danny J. Lesandrini]

Joseph, I see no one has responded yet. I suspect the table is corrupt. (Let's assume we're
talking about only one table here.)

The obvious answer to your question below is that you need to compact and repair the database.
Note: If you get these errors from a client that is linked to the data file, compacting the
client does not fix the problem. You must open and compact/repair the data file.

If you've already done that, you may need to speak with people who data recovery. Something
is corrupt. If you can build a new database and pull the data into new tables, that might solve it.

I did once see a database where Access had inserted a duplicate in the Autonumber Field. If
that has indeed happened, you'll need to figure out which one it is and delete it. The errors,
if I recall correctly, weren't that far off from what you describe.

If you can't query the table, can you at least export it to text and analyze it there? Perhaps
someone else can comment on whether the datatype of the Autonumber field could be changed
programmatically and/or if the Index could be deleted. That might afford you the ability to get
to the data to move it and/or rebuild the table.

Just some random ideas.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

 

[Joseph M. Newcomer]

Thanks, I'll try that.

Note that once the damage happens, we seem to be unable to open the database at all, but I
still have a copy that is in weird state that can be opened.

The export function also fails with a "deleted record" error (I'd already tried that). We
don't actually have any programs that work on the database; all interfacing is done
through an Access-generated UI.

I'll give it a shot today.
joe

Joseph, I see no one has responded yet. I suspect the table is corrupt. (Let's assume we're
talking about only one table here.)

The obvious answer to your question below is that you need to compact and repair the database.
Note: If you get these errors from a client that is linked to the data file, compacting the
client does not fix the problem. You must open and compact/repair the data file.

If you've already done that, you may need to speak with people who data recovery. Something
is corrupt. If you can build a new database and pull the data into new tables, that might solve it.

I did once see a database where Access had inserted a duplicate in the Autonumber Field. If
that has indeed happened, you'll need to figure out which one it is and delete it. The errors,
if I recall correctly, weren't that far off from what you describe.

If you can't query the table, can you at least export it to text and analyze it there? Perhaps
someone else can comment on whether the datatype of the Autonumber field could be changed
programmatically and/or if the Index could be deleted. That might afford you the ability to get
to the data to move it and/or rebuild the table.

Just some random ideas.

Joseph M. Newcomer [MVP]
email: (e-mail address removed)
Web: http://www.flounder.com
MVP Tips: http://www.flounder.com/mvp_tips.htm

 

[Kai Pedersen]

Joseph, I see no one has responded yet. I suspect the table is corrupt.
(Let's assume we're
talking about only one table here.)

The obvious answer to your question below is that you need to compact and
repair the database.
Note: If you get these errors from a client that is linked to the data
file, compacting the
client does not fix the problem. You must open and compact/repair the
data file.

If you've already done that, you may need to speak with people who data
recovery. Something
is corrupt. If you can build a new database and pull the data into new
tables, that might solve it.

I did once see a database where Access had inserted a duplicate in the
Autonumber Field. If
that has indeed happened, you'll need to figure out which one it is and
delete it. The errors,
if I recall correctly, weren't that far off from what you describe.

If you can't query the table, can you at least export it to text and
analyze it there? Perhaps
someone else can comment on whether the datatype of the Autonumber field
could be changed
programmatically and/or if the Index could be deleted. That might afford
you the ability to get
to the data to move it and/or rebuild the table.

Just some random ideas.

 

[Kai Pedersen]

Joseph, I see no one has responded yet. I suspect the table is corrupt.
(Let's assume we're
talking about only one table here.)

The obvious answer to your question below is that you need to compact and
repair the database.
Note: If you get these errors from a client that is linked to the data
file, compacting the
client does not fix the problem. You must open and compact/repair the
data file.

If you've already done that, you may need to speak with people who data
recovery. Something
is corrupt. If you can build a new database and pull the data into new
tables, that might solve it.

I did once see a database where Access had inserted a duplicate in the
Autonumber Field. If
that has indeed happened, you'll need to figure out which one it is and
delete it. The errors,
if I recall correctly, weren't that far off from what you describe.

If you can't query the table, can you at least export it to text and
analyze it there? Perhaps
someone else can comment on whether the datatype of the Autonumber field
could be changed
programmatically and/or if the Index could be deleted. That might afford
you the ability to get
to the data to move it and/or rebuild the table.

Just some random ideas.

 

[emilybalise@yahoolcom]

 

[emilybalise@yahoolcom]