Access MDB: do access MDB files have an upper size limit?

Question

Do access .MDB files have an upper size limit? Will, for example, applications that connect to an .MDB file over 1GB have problems?

What problems/risks are there with MDB files over 1GB and what can be done?

Answer 1

Yes. The maximum size for an MDB file is 2 GB and yes any file over 1 GB is really pushing Access.

See Access Database Specifications for more.

Answer 2

You may find data retrieval painfully slow with a large Access database. Indexing can reduce the pain considerably. For example if you have a query which includes "WHERE somefield = 27", data retrieval can be much faster if you create an index on somefield. If you have little experience with indexing, try the Performance Analyzer tool to get you started. In Access 2003 the Performance Analyzer is available from Tools -> Analyze -> Performance. I'm not sure about other Access versions.

One caveat about indexes is that they add overhead for Insert, Update, and Delete operations because the database engine must revise indexes in addition to the table where the changes occur. So if you were to index everything, you would likely make performance worse.

Try to limit the amount of data your client application retrieves from the big database. For example with forms, don't use a table as the form's data source. Instead create a query which returns only one or a few rows, and use the query as the form's data source. Give the user a method to select which record she wants to work on and retrieve only that record.

Your didn't mention whether you have performed Compact and Repair. If not, try it; it could shrink the size of your database considerably. In addition to reclaiming unused space, compact also updates the index statistics which helps the database engine determine how to access data more efficiently.

Tony Toews has more information about Access performance you may find useful, though it's not specific to large databases. See Microsoft Access Performance FAQ

If you anticipate bumping up against the 2 GB limit for MDB files, consider moving your data into SQL Server. The free Express version also limits the amount of data you can store, but is more generous than Access. SQL Server Express R2 will allow you to store 10 GB. Actually I would probably move to SQL Server well before Access' 2 GB limit.