How do I automatically compact and repair an Access 2007 database?

views:

102

answers:

How do I automatically compact and repair an Access 2007 database?

I have a site that uses classic ASP and a database in Access 2007 format (.accdb). It is encrypted with a password and is about 300 MiB in file size.

The site works fine by itself but every now and then the database gets corrupted (the error is "unrecognized database format"). It can be fixed easily by opening the database in MS Access, then it will be repaired. Problem is it can take days before I notice the database is corrupted and during that time the site will be useless. Sometimes it takes months between each corruption, other times only a week or two.

What I want is the site to be able to call the "Compact and Repair Database" function itself every now and then (once a day or so) to keep the database in a working condition.

My question is how do I do this, make it repair itself?

I found this article: http://stackoverflow.com/questions/1548245/how-do-i-compact-and-repair-an-access-2007-database-by-net-code ...but I don't understand how I can make that work for me. I only know classic ASP and Java.

Can anyone write a little isolated ASP code that does this: "open connection to password-protected database", "repairs the database", "close the connection".

The site and database is on a dedicated server which I have full control over so I can implement any solution that exists.

Thank you very much!

You can write a script that repairs the database every now and then. But there are two problems:

1) How to detect the database is corrupt 2) How to minimize data loss.

The biggest problem is that you are trying to fix a problem which lies within the access database itself (frequent data corruption). Because of this, the product is not suitable for any serious application. So why don't you switch to a more reliable database? (MS SQL, MYSQL, ORACLE, SQL Lite, and more to choose from).

Gamecat 2010-06-27 18:33:21

There is no need to detect a corrupt database. Compacting a Jet/ACE database first detects if there are any problems in need of repair and if there are none, it skips the repair step and just compacts the file (rewriting data and index pages in contiguous data files and discarding unused data pages and updating all statistics and internal pointers, etc.). "Not suitable for any serious application" is simply blatant bigotry and wrong. If I could downvote you 10 times for that, I would. You obviously lack the qualifications to judge proper use and maintenance of Jet/ACE data stores.

David-W-Fenton 2010-06-27 22:15:44

"Not suitable for any serious application" is wrong. There are many situations where it's not suitable, such as on a website with a significant volume of updates/inserts. But it's quite suitable for serious applications.

Tony Toews 2010-06-27 23:12:36

If I wanted to only do it if the database was corrupt I could simply catch any errors upon usage and if the error message is "unrecognized database format" I'd know it's corrupted and can initiate a repair. By the way, you forgot to write the most important thing in your answer, how I actually "write a script that repairs the database every now and then". That's what I'm asking here. ;) So please write a comment with the code I need, will be very grateful!

Mike 2010-06-28 14:13:25

How many times to people have to tell you that you've made a wrong basic choice and can't really completely resolve the issue absent a switch to a more appropriate database engine? Why would someone write you a script when you should be avoiding the problem entirely?

David-W-Fenton 2010-06-28 20:09:19

Can SQL Server Express be legally licensed for use on a web server? If so then I'd suggest moving your data to it instead. If not there are other options mentioned elsewhere. At the following page SQL Server 2008 Express I see mention of a Web Platform Installer so that would imploy you can use it on a web server. But I do not interpret EULAs so I leave that decision to you.

Tony Toews 2010-06-27 23:19:06

@Tony On the left under Usage Scenarios, it says "Websites and web applications". So I assumed it's legal. But IANAL!

HansUp 2010-06-27 23:53:42

Thanks for the comment(s), however I'm only interested in direct solutions to my problem.

Mike 2010-06-28 14:05:02

Mike 2010-06-30 12:13:58

ansaurus

tags:

views:

answers:

How do I automatically compact and repair an Access 2007 database?

related questions