Acceptable size for MS Access Database?

Question

I have developed an Access 2010 database.

It has three main tables:

HOUSES
id, 
address, 
number_stories, 
garden, 
parking, 
imageAttachments (~1-4 per record),
etc..., 
about 60 fields total


CONTACTS
id, 
houses.id(fk), 
contactType(owner, manager, etc), 
contactEmail, 
contactPhone


VISITS
id, 
Contacts.id(fk),
Houses.id(fk), 
visitDate, 
visitNotes

It also has a bunch of small lookup tables for some of the fields in the houses table.

Right now I have about 100 houses listed and only about 5-10 contacts and visits.
I'm expecting at its peak this database will hold 300-500 houses, with 3-5 contacts and visits for each.

Right now the database size is about 50 MB. I realize I didn't compact/repair it while I was building it or initially populating it which might be one problem. It seems to run very slow. Has this database grown too much? Is this a typical size for a database of this scope?

(I'm on windows 7 with 8 gig RAM so I don't think my own computer is the issue)

Answer 1

50MB is very small. I would not worry about size at this time. The DB can grow if you do a lot of coding in the DB, updating form designs, create new stored queries, and things like these.

If your queries are slow, it would not be due to DB size because Access tends to append data changes rather than handle data changes in place in many cases.

I would look into the indexing of all your tables. Look for any queries you are making that use WHERE clauses whose columns are not indexed. You may need to consider compound indexes for WHERE clauses with multiple colname= combinations.

What concerns me is the number of columns in the HOUSES table. 60 columns? That is a lot of columns. Changes are the lenght of many of the rows are spanning longer than Access would like. Fetching just a single row from the HOUSES table is proabaly an expensive operation. You should look into denormalizing the HOUSES table. You could just go to the Database Tools tab, and Click Analyze Table.

In addition, you could just go to the Database Tools tab, and Click Analyze Performance and start measure other potential bottlenecks.

If the database starts to grow too large (over 1GB) or if you DB is in the frequent of doing heavy updates of rows, you should run Compact/Repair periodically. You may be surprised how much space will be reclaimed in doing so. You can do it in one of two ways:

• With the DB Open, Click File in the Top Toolbar, then Click Compact Repair Button
• Setup a Windows Task to Launch the Compact/Repair

Example

• MSACCESS.EXE is located in C:\Program Files\Microsoft Office\Office14
• I have a Database Called C:\LWDBA\RolandosDatabase.accdb
• Create a DOS Batch File called C:\CompressMyDB.bat with the following lines

BATCH FILE

@echo off
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /compact C:\LWDBA\RolandosDatabase.accdb

Then just have a Windows Task kick off this batch file at the interval of your choosing

Answer 2

I've had Access databases of 1.5 GB (7 years ago on a machine much weaker than what you've described) run fine - for some queries. It really depends on what you are querying and how it's indexed. Without knowing the details of the queries and reports you're running, I can't say if it's too slow or not (and you don't actually define slow: 10 seconds? 2 seconds? 3 hours?) . I know in the past that Access had a 2GB file-size limit. I don't know if recent versions have changed that.