0

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: 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!

Community
  • 1
  • 1
Mike
  • 71
  • 1
  • 2
  • 4
  • 1. Jet/ACE is not an appropriate data store for a website. 2. database passwords are useless for any purpose whatsoever. You obviously have something desperately wrong in the operating environment if it is repeatedly corrupting the database. You can either spend the time troubleshooting that and fixing it, or you can do the right thing and switch to a data store that is appropriate for this use. – David-W-Fenton Jun 27 '10 at 22:17
  • Though I appreciate you taking the time to write a comment, the pointers you give are completely irrelevant to my question. The site that use Access I'm having are many years old and I do not have the time to rewrite any code, I just want a solution that works. – Mike Jun 28 '10 at 14:04
  • That is, there is no band-aid solution to your problem. There is no way to detect corruption. Indeed, Jet/ACE/Access itself often misreports it, because a file can be internally just fine, but if the flags in the header don't all get properly set on close, it can be seen as suspect and will be reported as possibly in need of repair/compaction. – David-W-Fenton Jun 28 '10 at 20:12
  • If you're really stuck with your ACCDB as data store, then you need to troubleshoot what's causing the corruption and eliminate that. That is MUCH harder than simply switching to a more appropriate database engine, so it doesn't really fit your desire for a quick solution. – David-W-Fenton Jun 28 '10 at 20:13
  • Last of all, the fact that it's an ACCDB rather refutes your claim that it's an OLD website, since ACCDB didn't exist until the release of Access 2007. Somebody made a decision to use an ACCDB sometime in or after 2007. Perhaps the site existed before then with an MDB. MDB vs. ACCDB matters none at all except for the connect string, but it does rather weaken the implication that no recent major changes have been made on the site (the switch to ACCDB would have required the connect string alteration, at the very least). – David-W-Fenton Jun 28 '10 at 20:15

3 Answers3

0

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).

Toon Krijthe
  • 52,876
  • 38
  • 145
  • 202
  • 1
    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 Jun 27 '10 at 22:15
  • "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 Jun 27 '10 at 23:12
  • 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 Jun 28 '10 at 14:13
  • 1
    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 Jun 28 '10 at 20:09
0

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
  • 7,850
  • 1
  • 22
  • 27
  • Thanks for the comment(s), however I'm only interested in direct solutions to my problem. – Mike Jun 28 '10 at 14:05
0

I got the answer I needed in another question I asked: Why can't I use "CompactDatabase" in DAO.DBEngine.36 using VBscript?

The user "HansUp" gave me the following code:

Dim objFSO 
Dim objEngine 
Dim strLckFile 
Dim strSrcName 
Dim strDstName 
Dim strPassword


strLckFile =  "C:\Access\webforums\foo.laccdb" 
strSrcName =  "C:\Access\webforums\foo.accdb" 
strDstName =  "C:\Access\webforums\compacted.accdb" 
strBackup = "C:\Access\webforums\foobackup.accdb" 
strPassword = "foo"



Set objEngine = CreateObject("DAO.DBEngine.120")



Set objFSO = CreateObject("Scripting.FileSystemObject") 
If Not (objFSO.FileExists(strLckFile)) Then 
    If (objFSO.FileExists(strBackup)) Then 
        objFSO.DeleteFile strBackup 
    End If 
    If (objFSO.FileExists(strDstName)) Then 
        objFSO.DeleteFile strDstName 
    End If 
    objFSO.CopyFile strSrcName, strBackup



''dbVersion120 = 128  
objEngine.CompactDatabase strSrcName, strDstName, , 128, ";pwd=" & strPassword

objFSO.DeleteFile strSrcName 
objFSO.MoveFile strDstName, strSrcName 




End If 'LckFile

(The code got a few extra line breaks when I copied it for some reason, follow the link at the beginning of this post for a little cleaner version of the code if you wish.)

It compresses a Access 2007 database to a Access 2007 database (no format change) AND it also repairs any corruption (inconsistent state) in the database! Just what I was looking for. =)

Community
  • 1
  • 1
Mike
  • 71
  • 1
  • 2
  • 4