2

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.

3

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.

1

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.

  • I totally agree with you, this would be the best way to proceed. Unfortunately I am very (very, very) limited in terms of available / allowed software, so I will have to stick with MS Access... For curiosity: If I were to link my MS Access DB to tables in SQL Server, would referential integrity between these tables be available? Or same like linking tables between MS Access *.accdb files this could not be maintained? – HeXor Mar 29 '17 at 5:27
  • @HeXor I think its worth lobbying management to make an exception. Trying to work around this limitation in access will take more time introduce risk to your migration. – Sir Swears-a-lot Mar 29 '17 at 5:36
  • You could implement referential integrity either in access or SQL using foreign key constraints. However if i was doing it id use queries rather than force with constraints. – Sir Swears-a-lot Mar 29 '17 at 5:37
0

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.

  • Since this is used as a temporary database, merging different sources together and preparing the Input for consistency, duplicate removal, etc. this is a bit too complex for delimited file usage, losing control of data/link consistency checks – HeXor Mar 22 '17 at 10:08
  • That sounds like something you should be doing on the source, the target, or within an ETL tool built for it, but not in an Access database. If you really want to do it in a file, you could use SQLite. – CalZ Mar 22 '17 at 12:06
  • Using a professional ETL tool would be overkill for this project size, and since we are talking about several different sources, which are prepared individually and then merged together as one database for transport.The final database will Import this temporary database, and is implemented by a different developer. – HeXor Mar 22 '17 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.