Search code examples
ms-access

splitting ms access database to multiple backend

I have an ms access database with forms which I split .! I was wondering if I can further split the backend and move specific tables to a second backend . The problem I am trying to solve has to do with the 2GB size limit , where MS access fails, but if I am able to move couple of tables which fills up during my ETL , I could still be within the 2gb limit and can save the day

I split the ms database using the DBSplitter utility and needs to split the backend further

Solution

  • There is little or nothing special about how splitting a database works, and thus no need to use the built in tools for this purpose.

    To move out a few tables from an existing split database?

    Simply create a new blank database, and use the import from the external data tab of the ribbon. Import the few tables you want.

    Now, re-open the original back end data file, and delete those 2-3 tables you moved into the other new (second) back end.

    Now, close above, and open the front end and delete the 2-3 tables that we once linked to the original back end, and now using the linked table manager, add back the 2-3 linked tables but pointing them now to the back end #2.

    Also, I assume that before you start this ETL process, you have compaced the data file. And I assume you have ALSO turned off row locking feature. The row locking feature in Access can cause MASSIVE bloating and file growth, so I recommend you turn this feature off.

    The option I am speaking of is found here:

    File->option->client settings

    Then this:

    enter image description here

    I have seen the above reduce bloat and growth by many times, and in some cases you see next to no growth after some update operations.

    So, depending on how your ETL code works, I would consider/try the above option. Do note that you have to exit Access, and re-start for the above setting to take effect.