Use a VBA procedure to copy good records from a corrupted to table

[joris]

At a very small bookshop (where I volunteer) we use an access 2000 database to keep track of the books that are ordered and when they have arrived.



This database has been used intensively for over 9 years and recently started crashing very often. So I started looking for a solution to make it more stable. By importing the tables into a clean database I found one table that gives me problems importing into a clean database. So I guess this is the table that causes the problems. When I open the table I can also see that there is data corruption, because some titles of books have changed into random numbers or ++++++++ signs.

Ofcourse I tried the most obvious things first like repair and compact. But I guess there is no way of repairing this table. But to avoid data loss I want to export the not corrupted data to a fresh table in a new database. The problem is that the Table is very large, with over 16500 records.

This website http://www.devx.com/dbzone/Article/42850/1954 points me to an solution with a VBA procedure how to automate the rescue of the data that is not corrupted.

Problem is that I have no experience with running VBA code and this website just gives some code but not some clear instructions that a newbie would be able to follow.

So now I try to crash-course myself into the basics of VBA in Access, but I am not getting much further. I hope that someone here on this forum would like to give me some easy to follow instructions on how to get this code running on the corrupted table...

[June7]

You can download the bas file from that site, extract it from the zip, then open it with Notepad. Copy/paste the code into a VBA module. The procedure is a Function so you have to call it as part of an expression. If you change it to a Sub doesn't have to be part of expression, just invoke the Sub.

Access Help should have introductory guidelines on coding.

16500 records isn't really a lot. If you can open the table and see which records are corrupt, can you sort them so that the good ones are all together? Then you select them as a group and do copy/paste to another table.

Could also try export to Excel, see if that cleans up the corruption or makes it easier to isolate the corrupt records. Then import or copy/paste good ones back into a new table.

[joris]

Thanks for your fast reply June7.

I understand the part about copy and pasting the code in a module. But don't know how to go from there. It is frustrating, but I just lack the basic skills to figure out how to do this myself. Some step after step instructions would be very appreciated...

So now I am just trying very hard to copy paste the good records manually into a new table, but I only succeed when I try a few lines. (20 or so) Larger selections always fail, no matter how I sort. Then 16500 really are a lot of records to go through.... Luckily I have a backup from a few months ago, without any corruption. So that makes it a bit better... Still it is a lot of (boring) work.

Exporting to excel is not an option btw, because the corrupt table doesn't let it's data be exported...

[June7]

You can put the code in general module or behind a form.

Call the procedure (function or sub) from a button Click event.

As a function call it with:
x = CopyDataFromCorruptTable("put your table name here")

As a sub call it with:
CopyDataFromCorruptTable("put your table name here")

That's about it, one line in button Click event.

Do you know how to create button and Click event procedure?