Access Database Problems

January 7, 2005 1:01 PM

Access database problems...

I have a database which I use to store a set of data. Right now I have 2 Access databases, one which holds the data, and the other which acts as the end-user interface. The database with the data is 1.5GB, and grows every day by 3-4 MB with new data. This is causing the data fielding to be extremely slow.

What are my solutions for this? SQL server? Another database program? It has to run on a Windows platform, and preferably be free due to budget constraints.

posted by benjh to computers & internet (9 comments total)

First of all, you will have real problems when you reach 2GB, because that is as large as an Access database can be. Past that limit, you'll get errors that make no sense and other inconsistent behavior.

Second, are you running the compact and repair utility? Tools -> Database Utilities -> Compact and Repair. This will shrink an .mdb file dramatically in many cases.

Third, are you storing images or OLE datatype stuff in there? That's a notorious cause of bloating, and, indeed, file corruption. If so, you may want to store that stuff in the filesystem and keep paths to the files in the Accesss tables.
posted by crunchburger at 1:15 PM on January 7, 2005

First off, have you tried all the general optimization techniques for access?

like: here, here, and here

If you have, then you might consider going to SQL server. Generally speaking SQL Server will offer you the performance you want, although it's not free. I have databases with 1.5million+ records and can search through them pretty quick, and i know that's not even near the limit. Of course, SQL server has it's own optimizations you'll need to look into.

Is there a nice way you can split up the database? if so, then that can speed up your search tremendously, albeit your data will not all be in the same place.

i haven't tried any other database solutions, so i can't help you out there.

On Preview: i agree with crunch.
posted by escher at 1:20 PM on January 7, 2005

You might want to look at Microsoft SQL Server 2000 Desktop Engine (MSDE). It's free, but the license is a bit restrictive. Once you have it installed it's easy to open it or attach it in Access, and then fairly simple to import your Access tables into it.
posted by SteveInMaine at 1:34 PM on January 7, 2005

I used to run compress and repair on my access MDBs over night as a scheduled task (always make sure you have a backup first as part of the batch job). MS SQL won't have this bloat problem but it is rather expensive and requires careful looking after (at least compared to MDBs). Maybe you could divide up your tables amongst several MDB files instead of one.

You could at look at using MySQL for your backend database which is free, and there is an available ODBC connector - so at least you won't need to rebuild your front end MDB. Haven't tried it myself though...

Incidently, the database I had that used to do this was running constant refreshes of a form using a timer evert - by changing the updates from every one minute to every three I found the database did not bloat as quickly. Unfortunately I never got a chance to look for a proper solution.
posted by dodgygeezer at 1:38 PM on January 7, 2005

To follow dodgygeezer's point, I'm running several projects that use MySQL/PHP on a hosted server for an online app, but use an ODBC connection on the back-end to allow MS Access as an admin tool. Works great. (E-mail me if you want any further info.)
posted by LairBob at 2:14 PM on January 7, 2005

I just wrote out a long post and when I went to preview it, dodgygeezer said it all...sigh.

Um, mysql: free and fast.

Also, to keep your end user app (web-based, I assume?) running fast, move only necessary info into a separate table for them. You can script it to run an insert query at a particular time of the day or based on an event.
posted by bikergirl at 2:27 PM on January 7, 2005

I'm just here to back everyone else up. You might be able to patch things up, but look at migrating away from Access. MySQL is free, runs on Windows and can be pulled into your front-end as link tables with ODBC connections. SteveInMaine's suggestion is the other option for $0.00; since you have the front-end and back-end separated already, the license requirements might fit your needs.
posted by yerfatma at 3:19 PM on January 7, 2005

I would do things in this order:

1 - try to stay with Access (complacency is fun!). Is the schema normalized? If not, you may be able to make a significant reduction in database size by normalizing as well as dropping unnecessary indexes. This is a good step to take before you do #2 or #3 anyway, so I would try this first.

2 - see if you can get SQL Server, e.g., check out Ebay

3 - port the backend to MySQL (if so, make sure you check out phpMyAdmin)
posted by SNACKeR at 3:55 PM on January 7, 2005

Assuming that all your problems are a result of the RDBMS (and not weird SQL, bad indexes, bad denormalization, etc...) you might also check out SQL Server 2005 Express edition (currently in Beta). It's a little more robust than MSDE, and most importantly has better accompanying tools. I've played with this and it has been very stable for me.
posted by theFlyingSquirrel at 5:24 PM on January 7, 2005