Maximum capacity of an MDB file?

views:

answers:

+1 Q:

Maximum capacity of an MDB file?

Hi,

I have a complicated system that is pushing data into an MDB file.

The mdb filesize is currently at 40MB.

Did anyone had any experience with larger mdb files and could tell me what is the maximum capacity that it will be still operational? (if it's dead slow at 150mb i don't call that operational).

Thanks!

+3 A:

For Access 2003:

2 GB. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.

per this article: http://www.databasezone.com/techdocs/acclimit.html

You can also find the info here: http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx

David Stratton 2010-10-12 14:53:10

Do you have any working experience with large mdb files? I know that the written limit is 2GB, but I want to know if it's really working at the same speed when it's 1GB as it's 40mb

Or W 2010-10-12 15:00:17

Yes, I do have experience with large mdb files, in the form of applications that I've had to re-write when they started showing issues. Personally, I would never use an Access DB in a production app unless I absolutely had to. There are so many alternatives that I would not even consider it.That said, this is an opinion, and one that will likely anger some people. I'm not saying Access is bad, just that I personally wouldn't use it. That said, this is an opinion, and one that will likely anger some people. I'm not saying Access is bad, just that I personally wouldn't use it.

David Stratton 2010-10-12 15:22:17

You're not only saying it -- you're saying it TWICE!

David-W-Fenton 2010-10-12 20:13:48

I would start looking to upsize with any app that was approaching 1GB, or I'd start with a different back end for any app that I could conceive of as needing over 1GB of storage during its useful lifespan.

David-W-Fenton 2010-10-12 20:14:54

must..learn..to..edit..properly.

David Stratton 2010-10-12 21:20:47

+3 A:

David already gave an answer for the maximum file size, so I'll address the speed issue.

Size of the DB alone isn't going to be deterministic of speed. 150 MB is nothing for an Access Jet DB. I've run Access MDB files well into the GB range with no noticeable performance issues. However, the larger the individual tables get the more important it will be to implement a good indexing strategy and architect the databases for optimal performance. This is pretty much the case with just about any DB engine, Access Jet included.

Also, the limit for an MDB isn't necessarily the upper limit on the total size of the data you can manage in a single application. With some clever architecture you should be able to split the data into multiple MDB files and link them into your application.

JohnFx 2010-10-12 15:01:26

Great answer, thanks a lot!

Or W 2010-10-12 15:07:49

I wouldn't get too excited about the part about multiple back ends, though -- you can't enforce relational integrity between tables in different data files, so it's a non-starter for most of the tables that are going to get large in a normal application. That is, all the important data tables are going to require RI, and thus have to be in the same file. If there's any reasonable possibility that you're to exceed 1GB, I'd say you should contemplate switching to a server-based back end with larger capacity sooner rather than later.

David-W-Fenton 2010-10-12 20:13:02

@David-W-Fenton - Very good point. However, with that much data it sounds like there is a distinct possibility that there are logical "Data Islands" wherein relations are somewhat localized, or at least some data that can be horizontally partitioned into archive tables.

JohnFx 2010-10-12 21:37:47

+2 A:

150 megabytes is quite reasonable for Access. I ouwld suggest visiting my Access Performance FAQ page.

Tony Toews 2010-10-12 19:29:39

+1 A:

I have Access databases in the 300 to 500 megabyte range with 20 simultaneous users giving quite reasonable performance.

Larry Lustig 2010-10-12 19:39:30

+1 A:

MS Access data response depends on database size, Linked tables and Hardware. I have used 1 GB MS Access databases with slight glitch when fetching files. Please also remember to run maintenance/compact process regularly to keep the database size trimmed. You can achieve compacting database dynamically using VBA command. But compact requires you to open database in exclusive mode.

PradeepGB 2010-10-13 13:17:27

I dispute the assertion about database size being relevant to performance. What matters is TABLE size (more specifically, INDEX size). While a database with large tables will be larger and can bog down performance, it's not because the database is larger but because the table is larger. So it's really number of records that is the issue, not size of the database.

David-W-Fenton 2010-10-16 16:00:18

ansaurus

tags:

views:

answers:

Maximum capacity of an MDB file?

related questions