Bypass MS Access 2GB file size limitation

Question

I am creating a MS Access *.accdb database with multiple tables and specific relationships.

This database will be used for a one time data transport to another system, containing order of a few million entries. A test with 2 million dummy entries already reaches about 1.8GB file size. Since MS Access has a built in limit of 2GB per database file I am searching for solutions to bypass this limit.

I came up with two ideas so far:

1. Split the database into two files, continuing the indexing during the transition from file1 to file2, taking care of independence for the entries in related tables.

   file1_table1 (indices 1..1000)
   file2_table1 (indices 1001..N)
   

2. Export the tables to different files, using one master interface file

   file1_table1
   file2_table2
   file3_table3
   masterfile (linking table1..3 and creating table relationships)
   

I would like to get some feedback on these ideas concerning concept, performance and usability or if there are any other ideas about this.

Please note that I want to stick to MS Access, building up a SQL Database would be overkill, since it will be used for a one-time transport only.

The data is distributed over several sources (MS Access .accdb, Excel Sheets *.xlsx, delimited files *.csv) and is analyzed, merged and prepared as a temporary one-time-use MS Access database, then transformed into a working database based on SQL, which includes a lot more than what I am preparing. So it's a classical ETL process.

The "Compact and Repair" command won't help, since it is really just the large amount of data, not a large amount of operations leaving behind a lot of junk. The "Split an Access database" procedure only seems to separate Queries/Forms/Reports from the backend file containing the tables, but I need to split the tables themselves, leaving me with the problem of consistency between the split files. This is not an issue with separating the interface (front end) and data back end; I need to split the data backend itself due to the large amount of data.

Answer 1

You can't bypass the built-in limitation of an Access database. You can however circumvent the built-in limitation by using built-in features like splitting the database objects between multiple *.accdb files and referencing them accordingly.

The Access 2016 Specifications state:

Total size for an Access 2016 database (.accdb), including all database objects and data:

2 gigabytes, minus the space needed for system objects.

Note: You can work around this size limitation by linking to tables in other Access databases. You can link to tables in multiple database files, each of which can be as large as 2GB. Tip: For more information on reducing the size of your database, see Help prevent and correct database file problems by using Compact and Repair.

You can even go a step further and, as you have already mentioned, split the data and the interface.

Split an Access database

Consider splitting any database that several people share over a network. Splitting a shared database can help improve its performance and reduce the chance of database file corruption.

After you split database, you may decide to move the back-end database, or to use a different back-end database. You can use the Linked Table Manager to change the back-end database that you use.

Because it is Microsoft Access, I would recommend sticking with the supported options rather than trying another solution that might or might NOT work.

We used to have an application that had the interface (FE) and data (BE) split. No noticeable performance difference, when hosted on a file server. However, when the BE reached 2 GB a write corrupted the BE and the data was unrecoverable, that was the time we decided to switch to SQL Server Express and then later to SQL Server. I don't think there should be a noticeable performance impact when you access multiple BE files containing the different tables. But having the BE on file shares will generally be slower than storing them locally.

Answer 2

I would highly reccomend migrating the back end to SQL Server.

Tables can be attached from access via linked table to SQL Server.

The advantage of this approach is that you can scale up to avoid the access file restriction yet still make use of all the work you have done in access.

There are even wizards to assist with importing and upscaling from access.

Performance will be directly affected by your sql file, table and index design. But roughly speaking should perform as well, if not better than access. Basic tips:

1. Create a user db with a decent file size, and set the file growth to be 100Mb or 1Gb.

2. Ensure each table has a primary key defined in sql server. Not just a unique index. Access wont allow you to update a sql table without a primary key defined in the linked table definition.

3. The equivalent of an access autonumber is an identity column. If you have staging tables with duplicates its worth adding an identity column as a pkey.

If you dont have access to a sql instance you could install sql express or developer on your pc.

If you are unfamiliar with sql server there will be a bit of a learning curve, but it will be well worth the effort.

SQL comes with some powerful tools that open up a lot of options for you. Learning SSIS might be too much for this project but you can achieve a lot with the import wizard in SSMS and save the packages to be reused.

Answer 3

If this is a one-time transport, why are you using Access at all? Put the data into a delimited (comma/tab/pipe) file and be done with it. Most any system will import from that format.