Best way to find corrupted record?

Hi,

I have a large table (25,000 records +) that is acting strangely when I try
to export it to Word using Mail Merge. I have the problem with no other
tables. I am guessing I have a corrupted record somewhere with some *&*%%*#
symbols in a field somewhere.

What is the best, fastest and easiest way to locate a sigle corrupted record
and purge it? Thanks.

Bill

Hi Bill

There are different kinds of corruption, some harder than others to fix.
First step is to backup the corrupted datbase, and then try a repair (Tools
| Database Utilities).

If that fails, delete any relations the table is involved in, and then
delete any indexes. Create a new (blank) database, and try importing the
table. If the problem was with the index, this usually works.

If that still fails, create a query into the table, and try to read half the
table. If you can read the top half and not the bottom half, half the bottom
half. Keep halving until you identify the problem record. If it turns out
that the problem record has an ID of 999, import the other records by
specifying criteria of:
= 998 Or 1000
Notice that the criteria does not refer to the problem record.

In some cases you can see that the problem is just with the memo field and
not with the main data. In this case you can import all fields except the
memo into another table, and then use an Update query to update the memo
fields from the *other* records. Again, do not refer to the ID of the
problem record.

For more information, see:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Bill Mitchell" wrote in message
...
Hi,

I have a large table (25,000 records +) that is acting strangely when I
try
to export it to Word using Mail Merge. I have the problem with no other
tables. I am guessing I have a corrupted record somewhere with some
*&*%%*#
symbols in a field somewhere.

What is the best, fastest and easiest way to locate a sigle corrupted
record
and purge it? Thanks.

Bill

Thank you Allen,

As always, your insights are very helpful and detailed. As an MS MVP, do
you have any pull with MS at all? If so, could you recommend they put a Find
& replace functionality in their Query SQL Editor window? Are you aware of
any add-ins that do this? I tire of always having to copy and paste things
into Word just to do a find and replace when changing my SQL's. Also, would
be great to have a function which adjusts SQL from the Query window so that
it works perfectly in VB Code. I always have to make the changes by hand.
Thanks.

"Allen Browne" wrote:

Hi Bill

There are different kinds of corruption, some harder than others to fix.
First step is to backup the corrupted datbase, and then try a repair (Tools
| Database Utilities).

If that fails, delete any relations the table is involved in, and then
delete any indexes. Create a new (blank) database, and try importing the
table. If the problem was with the index, this usually works.

If that still fails, create a query into the table, and try to read half the
table. If you can read the top half and not the bottom half, half the bottom
half. Keep halving until you identify the problem record. If it turns out
that the problem record has an ID of 999, import the other records by
specifying criteria of:
= 998 Or 1000
Notice that the criteria does not refer to the problem record.

In some cases you can see that the problem is just with the memo field and
not with the main data. In this case you can import all fields except the
memo into another table, and then use an Update query to update the memo
fields from the *other* records. Again, do not refer to the ID of the
problem record.

For more information, see:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Bill Mitchell" wrote in message
...
Hi,

I have a large table (25,000 records +) that is acting strangely when I
try
to export it to Word using Mail Merge. I have the problem with no other
tables. I am guessing I have a corrupted record somewhere with some
*&*%%*#
symbols in a field somewhere.

What is the best, fastest and easiest way to locate a sigle corrupted
record
and purge it? Thanks.

Bill

Yes, a find'n'replace would be useful in the SQL View of a query.
I've often copied out to notepad and back to achieve that.

Adjusting for VB code would be a bit more involved, but probably doable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bill Mitchell" wrote in message
news
Thank you Allen,

As always, your insights are very helpful and detailed. As an MS MVP, do
you have any pull with MS at all? If so, could you recommend they put a
Find
& replace functionality in their Query SQL Editor window? Are you aware
of
any add-ins that do this? I tire of always having to copy and paste
things
into Word just to do a find and replace when changing my SQL's. Also,
would
be great to have a function which adjusts SQL from the Query window so
that
it works perfectly in VB Code. I always have to make the changes by hand.
Thanks.

Allen Browne wrote:

Adjusting for VB code would be a bit more involved, but probably
doable.

I believe that's what Query Shuttle does
www.queryshuttle.com

--
Joan Wild
Microsoft Access MVP