2GB Limit

[jmslavin]

Hi All,



Trying to get around the 2GB limit in Access (using 2010 version). I have split the database up into 4 separate databases each with a few tables. I then have another database that does not own any tables, but instead links to all the other tables contained in the 4 separate databases. My VBA code then only interacts with this "mother" database. Again this "mother" database does not own any tables, only links to them so my code can just connect to the "mother" database and not have to worry about connecting to the 4 separate databases that hold the actual tables.

I've noticed my "mother" database file size is growing with the more tables I link to. Is this "mother" database also subject to the 2GB rule? By linking to more and more tables in other database files, and those database files growing, will my "mother" database hit 2GB and so this is not effective? Instead of using a "mother" linking database, should I just write my code to connect to the 4 individual separate databases that actually hold the tables?

Thanks!
Jeff

[ItsMe]

Linking a front end file to a back end file should not cause the FE to grow in size. Something else is causing the growth. If you find yourself constantly doing compact and repairs to keep the size of your FE down, I would consider the various procedures your FE is running that may cause system tables to grow in size.

2GB is large. Make sure you are following normalization rules. Also, 64bit versions of Access can manage file sizes larger than 2GB. However, I have not tried this and generally do not recommend 64bit versions of Access. I would not want to see what happens when a new 32bit version tries to open a 3GB BE file.

[NTC]

front end (all code, forms, reports, queries) must link direct to back ends (tables only). attempting any other set up will not help and can only hurt. no single file (whether front or back) can exceed 2G. one can split the BEs as you have done. One can split the FEs. I have an insurance client with 100s of reports where we have an FE just for reports with no data input.

and that's it. no other finagling will help. typically if it is a table size issue - you must have a healthy amount of RAM or suffer long waits for queries to run before one hits that 2G max for a BE....

Access is a great technology - but it is a pick up truck compared to SQL Server which is an 18 wheeler....there can be a pay load issue at some point where one does need bigger cargo capacity.....

[John_G]

Hi -

The growth in size of the FE is very likely due to the queries - even though the source tables for the quesries are in the BE, the queries still run in the FE. I have seen first-hand a situation where a multi-table query caused the FE to crash because the query caused the 2GB limit to be exceeded in the FE.

Database splitting is not always the answer!

John