Bypass MS Access 2GB file size limitation 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:


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)

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.
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.