WHEN to Split an Access Database

[ARW17]

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.

 

[CPGDeveloper]

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]

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.

 

[JonXL]

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]

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]

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]

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]

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]

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?

 

[Joe4]

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?

See the code I provided here: Possible to send out a "patch" or better way of updating databases for multiple users?