CODEWITHSUNDEEP
ms-access
Rithwik Sarma
Rithwik Sarma

Reputation: 51

How to deal with Databases which exceed 2gb

I have to work with a database which exceeds the 2gb limit. I have tried splitting the database as well but I'm not able to do it. Please suggest what I can do to solve this problem

Upvotes: 0

Views: 9659

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

When faced with the inherent 2GB limit on the size of your MS Access database, there are several steps that you can undertake, depending on how aggressively you need to reduce the size of the database:

Step 0: Compact the Database

The obvious first step, but I'll mention it here in case it has been overlooked.

Step 1: Splitting the Database

The operation of splitting the database will separate the 'front end' data (queries, reports, forms, macros etc.) from the 'back end' data (the tables).

To do this, go to Database Tools > Move Data > Access Database

This operation will export all tables in your current database to a separate .accdb database file, and will then link the tables from the new .accdb database file back into the original database.

As a result of this operation, The size of the back end database will be marginally reduced since it no longer contains the definitions of the various front end objects, along with resources such as images which may have been used on reports/forms and which may have contributed more towards the overall size of the original database.

But since the vast majority of the data within the file will be stored in the database tables, you will only see marginal gains in database size following this operation.

If this initial step does not significantly reduce the size of the back end database below the 2GB limit, the next step might be:

Step 2: Dividing the Backend Database

The in-built operation offered by MS Access to split the database into a separate frontend and backend database will export all tables from the original database into a single backend database file, and will then relink such tables into the front end database file.

However, if the resulting backend database is still approaching the 2GB limit, you can divide the backend database further into separate smaller chunks - each with its own 2GB limit.

To do this, export the larger tables from your backend database into a separate .accdb database file, and then link this new separate database file to your frontend database in place of the original table.

Taking this process to the limit would result in each table residing within its own separate .accdb database file.

Step 3: Dividing Table Data

This is the very last resort and the feasibility of this step will depend on the type of data you are working with.

If you are operating will dated data, you might consider exporting all data dated prior to a specific cutoff date into a separate table within a separate .accdb database file, and then link the two separate tables into your frontend database (such that you have a 'live' table and an 'archive' table).

Note however that you will not be able to union the data from the two tables within a single query, as the 2GB MS Access limit applies to the amount of data that MS Access is able to manipulate within the single .accdb file, not just the data which may be stored in the tables.

Step 4: Migrate to another RDBMS

If you're frequently hitting the 2GB limit imposed by an MS Access database and find yourself sabotaging the functionality of your database as a result of having to dice up the data into ever smaller chunks, consider opting for a more heavyweight database management system, such as SQL Server, for example.

Upvotes: 2

Olivier Depriester
Olivier Depriester

Reputation: 1625

You can split your database into several files. The feature exists in the menu Database Tools > Move data.
You can read the Microsoft documentation about it
But prepare yourself to migrate to a new RDBMS in a near future because you are reaching the system limits ...

Upvotes: 1

Related Questions