db.mdb breaking?

Good day,

Regardless of the application you're using, MsAccess databases are not 100% reliable, it's part of the game.

First, MsAccess databases are not designed to handle heavy traffic and let me tell you that M$ doesn't have the same rules as most people would use when determining what "heavy traffic" is. Even 4 or 5 conurrent updates can be considered as "heavy" when dealing with a database used for a website. Let's face the facts, concurrent updates have always been a very weak point with MsAccess.

Unfortunately it happens at random that a database becomes corrupted and sometimes it is impossible to recover any data. This is where the PM (Preventive Maintenance) gets in the picture. Regular backups, or even creating a blank database and importing the objects of the tracker's database on a pre-established schedule could also be part of the so called maintenance.

One of the site I'm maintaining has no less than 35 MsAccess databases, controlling different sections of the site. This is painful, but when the budget isn't there, what can we do, right ? Nevertheless, I can't take for granted that the database will have an unlimited lifetime, even though they are only accessed in read.

If the "*.ldb" file was left in the directory, that will mean that the connection was not closed after the last access. The content of the "*.ldb" file could be used to determine how many "users" are accessing the db at a given point of time, mainly for troubleshooting.

When used on a web site, the user stored in the "*.ldb" file would normally be the Admin, which is the default.

If the "*.ldb" file is still in the directory, there is no way to compact db the directly on the server, since the database driver can't gain exclusive access, thinking it is being used by someone else.

Sometimes you simply can't get rid of it, so you may have to wait for the session that created it to timeout. I've experienced that situation when I was using global connections on some old sites.

The "Disk Space Quota" can also be a factor. Some hosting service will simply prevent usage of more space than what the site was assigned. So if for any reason you database has reached a size that would force the overall "Disk Space usage" to exceed what has been allocated to your site, then the update to the database may simply fail, with some errors like "write-protected" or any other misleading messages.

When a database looks like it is corrupted, don't compact/repair it right away. First, download the db, then create a blank one, and import the objects in it. Once the objects are imported, then you may proceed and compact the newly created DB. Naturally, after all that adventure, it will be appropriate to upload it back to the site.

One important point is to disable the logging for the period you will download the db and upload it back. If it gets updated while you download it, you may end up with a corrupted download, or even induce an error on the page(s) being logged during the transfer, which would not be much appreciated by the users.

So globally, Disk Space usage monitoring and Preventive Maintenance are mandatory when dealing with MsAccess related databases, regardless of the application they are designed for.

I've seen some application that needed the database to be replaced close to 10 times a year, even though the site wasn't that busy, so good maintenance planning is the key point when dealing with MsAccess databases.

Sorry, but there is not much that can be done to recover MsAccess databases, nor to prevent them to fail. If importing objects to a freshly created db doesn't work, the odds that a Compact/Repair will work are really slim. If you have MsAccess, you may consider trying the "/decompile" switch when opening the db, even if there is no code. After all, the queries are "compiled", so this may help recovering the db and/or its data.

The size of the db should not be a factor problem, as long as your disk quota is not exceeded. In a local application, I have a db which is close to 550MB, yes five-hundred and fifty Megabytes! It works, but you can schedule a long coffee break when compacting that bummer!

Happy tracking, nevertheless. :)

Daniel thanks for your detailed response. I was hoping, since this is a "pre release" version I'm using, that there was close connection missing somewhere in the script and I could simply add it. Too bad it's not that simple, but I like the program enough to spend some time working on creating a script to monitor it and to automate the compact and repair function.

thanks again...

Good day,

You may consider upgrade to version 1.3 Beta, (which is the one currently available on the site I believe.)

It has some improvements over v0.993 and as far as I can tell, Chad (the developper) did a good job at centralizing the connections handling, minimizing inadvertly left opened ones, although I did not had any real connections problems while testing versions prior to 1.3.

As per its design, in v0.993 and up, MT is a tad space hungry, would it be dealing with a MsSQL or MsAccess database.

Version 2 is under development, and a significant improvement in the disk space usage is expected, mainly due to data normalization.

In my testing version of MT I normalized part of the data, which lead me to major gains too. For instance, I actually have 36500 records in a MsAccess A2K db, and it is not exceeding the 7MB mark, which I believe is rather good.

If I'm not mistaken, v1.3 can report disk usage and allow you to compact the db via the setup page, which is also available in prior versions I believe.

I modified my setup page allowing to enable/disable logging via buttons, so that I can invoke the compaction of the db without having to transfer files over to the site or alter the application's configuration manually.

Chad did not explicitely mention the new features or improvements that will be part of version 2, however you can forsee major ones for sure.

Meantime, consider upgrading MT and perhaps adjust the setup panel to enable/disable the logging as I did. This is a real time saver, believe me.

I'm actually adjusting my "on-site" compact procedure so that all the tables are read, one by one, record by record. I'll do that extra step simply to ensure that all the data can be accessed/read without errors prior to do the compact, as doing a compact on a somehow damaged database may lead to a situation where the data can't be read anymore and even prevent the objects to be imported.

Also, part of my maintenance procedures I create blank databases and import objects from the in-use ones, would it be for MT or other databases. They are scheduled at different times, thus allowing me to span the maintance and minimize website downtime while downloading/uploading the databases.

It may sound a tad overdoing, but my past experiences with MsAccess databases and websites, lead me to change my beliefs in long lasting ones.

We have to be honest, constant reading/writing is done on the database and with an application like MT this is even more true, so we have to be carefull when dealing with MsAccess.

Glad it helped.