To Merge or Not To Merge... Backends

[RWilliams500]

Howdy. I am merging the frontends of a few different databases my job has had me make over the last few years. Since I've been learning as I go, each new database I make has things that work a little better/smoother. I've started merging the front ends so that it's easier for me to maintain, so my users don't have to click one of half a dozen different front ends to get to stuff, and I'm more confident in restricting users to just the functions they need within the new frontend.

The question is, should I merge the backends into one? Is there any benefit or drawback to maintaining them as separate backends linked to the one new frontend vs merging it all to one master backend like I'm doing with the frontend?

 

[DHookom]

If you want to maintain referential integrity between your tables, they need to be in the same ACCDB file. You must also consider the combined size of all the tables since Access has a size limit.

 

[CJ_London]

If each of your FE’s do not share data with other FE’s there is no real benefit in merging the back ends. You might hit a max db size or number of connections. On the other hand it might be easier to maintain

 

[Cotswold]

Maybe you don't need to? If the data is working fine why not just open the two of them from the merged FE?
There is no restriction to opening more than one BE database and keeping them open. I often did that in my software.

As a for-instance: I had a weighbridge program and a skip hire program. Some quarries didn't hire out skips and some skip hire companies didn't have a weighbridge. There was a separate ACCDB for each program. If a company had both programs then the software opened two BE databases which remained open until the software was closed. When in use data copied, or was read from one BE to the other.

I'd be reluctant to merge unless you will be paid to do it. Particularly if everything is working well. But bear in mind you'll probably need to do a fair bit of analysis and testing.

 

[The_Doc_Man]

Merging allows referential integrity. However, if the two BE files don't contain overlapping or interacting data, you don't have a strong driving force for your actions. Having split BE files means you cannot establish a relationship between tables in the two different files. However, you can still write queries to touch both tables. It is just that you won't get as much help from the query writer that normally would recognize a relationship and write the JOIN for you. You can still write the correct JOIN for yourself.

YES you can merge your multiple back ends. But also YES, you don't need to. Therefore, WHY would you merge them? If there is a business reason, go for it and watch out for capacity issues.

 

[Pat Hartman]

If there is no interaction or data dependency between the applications then not only is there no reason to merge either the FE's or the BE's, it is incorrect to do so. Do you use the same program to send your email, calculate your payroll and reconcile your bank records? No, you use three separate programs. Do you do all your shopping at a single store? The Swiss Army knife is the only truly useful multi-function tool I have.