Why should I split my Database?

[Wiz47]

I was having a discussion with Rickster57 concerning my newly developed database, and he recommended that I split it so that it has a front end and a back end. Rick listed a number of very good reasons for doing this (so I will definitely do it). But I wanted to pose the question so I could get some of the reasons the more seasoned Access programmers have for creating a front and back end to their programs.

 

[Dreamweaver]

To be honest if Rickster57 gave me some good reasons for doing something that would be good enough for me he's helped me a number of times.

 

[Wiz47]

To be honest if Rickster57 gave me some good reasons for doing something that would be good enough for me he's helped me a number of times.

I'm not doubting Rickster57's reasons for a second. I have come to respect him greatly and have taken his very sound advice to heart. I was merely putting the question out there for others so that I could know what reasons they have for splitting the database.

 

[boblarson]

Good point Dreamweaver. I also recommend it as it will:
1. Allow more users to connect without as much slow down (it will still slow down the more users that have connected, but it won't be AS pronounced as them all doing it directly).

2. With multiple users, if only one manages to set their default to "Open Exclusive" - bang there you go, no other users will get in if they open it first. But with a split system you don't experience that happening (unless they open the backed directly).

3. With Forms, Reports, Macros, and VB Modules in the same database as your data you run a higher risk of corruption. Each of those objects present their own set of potential corruption possibilities and if just one thing happens - BOOM! You can lose all of your data, as well as your user interfacing. With a FE/BE split, AND with each user having their own copy of the frontend (which I highly encourage), if one user's FE goes bad, it won't likely affect the other users at all and you can just replace theirs with a copy.

4. When connected via a network, Access can be kind of touchy. It can get corrupted by a network interruption/disconnection if the database is still opened and connected to by a user. This can happen, even with the briefest interruption and if you have only one MDB file that users are connecting to, then a slight "hiccup" on the network to one user can affect all users as it can corrupt the database and you just lost everything. When using a FE/BE situation, the corruption is less likely to occur in the BE as the things that most often get corrupted are forms, reports, macros, and VB code.

Those are a few of my reasons for going with a FE/BE situation. And, as we all SHOULD know, BACKUP, BACKUP, BACKUP. Make sure you have good backups occuring of your BE and always keep a good backup of your most recent FE.

 

[pbaldy]

IMHO, it's mandatory for a multiuser application. Corruption and speed/network traffic are the primary reasons.

 

[Wiz47]

Good point Dreamweaver. I also recommend it as it will:
1. Allow more users to connect without as much slow down (it will still slow down the more users that have connected, but it won't be AS pronounced as them all doing it directly).

2. With multiple users, if only one manages to set their default to "Open Exclusive" - bang there you go, no other users will get in if they open it first. But with a split system you don't experience that happening (unless they open the backed directly).

3. With Forms, Reports, Macros, and VB Modules in the same database as your data you run a higher risk of corruption. Each of those objects present their own set of potential corruption possibilities and if just one thing happens - BOOM! You can lose all of your data, as well as your user interfacing. With a FE/BE split, AND with each user having their own copy of the frontend (which I highly encourage), if one user's FE goes bad, it won't likely affect the other users at all and you can just replace theirs with a copy.

4. When connected via a network, Access can be kind of touchy. It can get corrupted by a network interruption/disconnection if the database is still opened and connected to by a user. This can happen, even with the briefest interruption and if you have only one MDB file that users are connecting to, then a slight "hiccup" on the network to one user can affect all users as it can corrupt the database and you just lost everything. When using a FE/BE situation, the corruption is less likely to occur in the BE as the things that most often get corrupted are forms, reports, macros, and VB code.

Those are a few of my reasons for going with a FE/BE situation. And, as we all SHOULD know, BACKUP, BACKUP, BACKUP. Make sure you have good backups occuring of your BE and always keep a good backup of your most recent FE.

Since my db will be going on a network, all of the above are great reasons to split it as a FE/BE. Rick also advised doing it for those reasons. Clearly, as mentioned above, it should be mandatory in a network situation. In my past experience as a programmer, I just compliled the native code into an .exe and put it on the server as one application - but with Access - splitting it seems like a needed and necessary measure.

 

[RuralGuy]

Aside from the many other reasons, being able to update the "code" without disturbing the data is very high on the list for me. Here's what some MVP's think if you haven't seen them yet.
Allen Browne Tony Toews

 

[Wiz47]

Aside from the many other reasons, being able to update the "code" without disturbing the data it high on the list for me. Here's what some MVP's think if you haven't seen them yet.
Allen Browne Tony Toews

Absolutely outstanding links. This just reinforces what y'all have been saying.

 

[modest]

I don't get this thread. Why didn't you just state the reasons that Rick gave you so we could add to or deter you away from?

The big thing is you have more security and encryption options (e.g. You can set a BE on an encrypted folder). However the other main reason is corruption and backup issues with Access due to the many users accessing. Another reason I like to is that in my work a lot of different departments share certain tables. You can build different FEs to access the data and perform operations. This helps manage FE code as well as the size of the FE itself.

 

[The_Doc_Man]

I'll add another issue: Size.

Particularly if you have a lot of code, complex graphics on your forms, or other space-eaters, having a split FE/BE gives you 2 GB limits on EACH FILE INDIVIDUALLY. I.e. suddenly your limit is 4 GB.

Further, though I'm not sure this is always good practice, it is theoretically possible to define tables that reside in the FE as temporaries, leaving your permanent tables in the BE. In which case importation and exportation via temp tables becomes less of a space eater. Because now if you are sloppy about compacting your FE, you only hurt yourself and the BE is never affected by the temporary tables.