WHEN to Split an Access Database

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
107
I've split finished databases before and understand how and the benefits of doing so. I have a project right now where I'm sequentially adding departments to a database. Right now, the first one is finished and I'm ready to move to the second. The first department (that's already functional in the database) will have 3 users that will work in it for about a day each month. I'm not sure if I need to make the split now and then manage the FE & BE separately as I add the next department or if it's better to wait and split it later since I'm only 1/7 of the way done with the whole project. It seems like it'll be easier for dept 1 to have FEs to use, but that it might be a pain for me to build the rest of the database swapping back & forth between the FE & BE. I'm looking for advice about the timing of splitting and everything I Google seems to just make the case that it should be done, but I can't find a decision tree about deciding when the best time is to do it.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
162
Of course, something like that depends on what other deadlines you might have and how urgent they are, but in my opinion, it is much better to split the database as soon as possible.

If time is an issue, perhaps you can find a Saturday to just get it done. The longer you leave it as is, the more potential problems you are exposing yourself to. How many end users do you think you will ultimately have?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
IMO, The best time to split it is right at the beginning, especially since corruption is a potential issue.

It is not hard to manage it if you set it up right. Instead of giving each user a copy of the front-end to use, what I typically do is create a batch file, and give them that.
What the batch file does is the following:
- Goes out to a central location and copies a front-end copy I have stored there down to their local machine.
- Opens the freshly copied front-end from their local drive.

So, if they always run/open this database from the batch file, it will ensure that they always have the latest copy of the front-end.
As you have new versions of the front-end to distribute, you simply copy over the centrally-located copy of the front-end that the batch file copies from.
 
Solution

JonXL

Active Member
Joined
Feb 5, 2018
Messages
482
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
As has been said, the time to split is immediately. In my opinion, there should never be a reason for end users to work in an unsplit DB (it really shouldn't even exist - I typically split before doing much development and well before the application is ready for actual use).

If you're concerned about being able to make and/or deploy changes, you might have a design problem with your data structure or FE deployment process.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,233
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The notion that adding departments factors into anyone's reluctance to split indicates to me there is a design problem. New departments should be a simple matter of adding data in to tables unless one department has an entirely different design for forms or reports. In that case, current users blissfully carry on while you further develop your fe. Then the process for distributing new fe versions should be seamless. When they try to open an outdated one, they get prompted to download. Even simpler is to download every time they try to open, then they get what's current (I've never used that method but I know others do).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
Another thing to keep in mind is that by using a split database, you can actually create multiple different front-ends!
So if certain departments require something totally different than others, you can create/distribute different front-ends to each. They all tie back to the same back-end.

I used to use this method sometimes when we wanted to schedule a certain task to occur at the same time every day. I would create a different front-end that has VBA code that runs upon opening. That code would run some process, then close itself. So then I would use Windows Scheduler to open that FE database at my designated time each night to automatically run my code.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,233
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I did the same thing with one file. The code knew if it was a regular user or the machine that ran the updates that had opened the file and ran accordingly. I suppose you need a dedicated machine or log in to do that though. We had a pc that did nothing but run updates at night.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
I did the same thing with one file. The code knew if it was a regular user or the machine that ran the updates that had opened the file and ran accordingly. I suppose you need a dedicated machine or log in to do that though. We had a pc that did nothing but run updates at night.
Yeah, we just did it directly from our server. We had it running a bunch of scheduled jobs using Windows Scheduler every night.
The server is never shut off (unless there was a power outage and the generator failed!).
 

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
107
The notion that adding departments factors into anyone's reluctance to split indicates to me there is a design problem. New departments should be a simple matter of adding data in to tables unless one department has an entirely different design for forms or reports. In that case, current users blissfully carry on while you further develop your fe. Then the process for distributing new fe versions should be seamless. When they try to open an outdated one, they get prompted to download. Even simpler is to download every time they try to open, then they get what's current (I've never used that method but I know others do).
IMO, The best time to split it is right at the beginning, especially since corruption is a potential issue.

It is not hard to manage it if you set it up right. Instead of giving each user a copy of the front-end to use, what I typically do is create a batch file, and give them that.
What the batch file does is the following:
- Goes out to a central location and copies a front-end copy I have stored there down to their local machine.
- Opens the freshly copied front-end from their local drive.

So, if they always run/open this database from the batch file, it will ensure that they always have the latest copy of the front-end.
As you have new versions of the front-end to distribute, you simply copy over the centrally-located copy of the front-end that the batch file copies from.
Each department is capturing different performance metrics, different calculations, different queries. The tables that the other departments need don't exist yet either. The thing that ties all the departments together is that there'll be an analyst running all the queries with the new month's data for all the departments at once.

I was worried about having a bunch of different FE versions floating around.

Can you say more about replacing the FEs for folks and the batch file method? Is there an article you could point me to for the best way to accomplish this? What about VBA to Run a Batch File from MS Access (example included) - Data to Fish

I'm also considering having multiple FEs for each department. Would I be able to run queries from FE1 that live in FE2 using vba?
 
Last edited:

Forum statistics

Threads
1,140,925
Messages
5,703,208
Members
421,282
Latest member
hogie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top