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.