When to split Access database? 6

[WaltW]

I have a couple of Access databases that are on a server and regularly shared by 5-10 people. It's been suggested to me that I consider splitting one or both of these databases into a back-end (containing the tables/data) and a front-end (containing all other objects). Can somebody spell out for me the advantages and disadvantages of splitting a database? Are you less likely to corrupt data or a database used by multiple users when it's split? When is it a good idea to split a database, and when are you better off leaving it alone? Is there already a FAQ on this subject?

Thanks for your help.

Walt

 

[lupins46]

For a multi-user system there are NO circumstances where it is better not to split. And each user should have their own copy of the front-end file.

 

[BNPMike]

Personally I don't see the point of splitting the database unless you want different people to have different views of the application.

I emphasise this is a personal view not supported by testing.

In the old days (Access 2) people might run the application on their local hard drive because delays were caused by actually building the screen. Nowadays workstations are blindingly fast and how many people do you know keep anything on their local drives - everything is on servers for security and so you can roam about different offices of your employers: Evilmegacorp.

I doubt splitting reduces data corruption. I can't see why. It might however reduce locking caused by people trying to edit reports etc.

 

[Edski]

Advantages:
You can re-design the front-end(s) over and over and over again without having to touch the back-end (as long as you're not changing the table structures).

Front-ends can be tailored to individuals/departments etc.

Scenario #1: You design an unsplit database with many tables and queries/forms and send it off to a client. They enter lots of data and after one year they find an error in the program or they want something changed in a form or report. The database is huge and so they cannot send it over email. They'll have to burn it on cd, but they don't have a cd burner. So you'll have to catch a plane to their site and fix something trivial.

Scenario #2: You design exactly the same database as above but you split it. Client wants a minor change to a form or report so you simply make the changes to the front-end and because it is only 1.5Mb (zipped) you can email it to them, tell them to simply overwrite their existing FE and continue happily.

Disadvantages:
None that I know of.

 

[willir]

WaltW

From my perspective...

Splitting data to a backend and forms / reports / code on the front end...
- Far easier maintenance as indicated by Edski. Maintaining a single large database involves working with a production database - risky, or importing the changes - now you have name / rename your forms or code. this advantage by itself is worth the price of addmission.
- The Front end database (forms, reports, code) can be on the local PC. This can result in an improvement in performance because the forms, etc are not copied across the LAN. (Although some developers will actually have the front end exist in the same network folder as the database thus negating this advantage.)
- As indicated by Mike, you can offer different views. One view for the Cashier, another for Management, etc.
- Users can not directly access your data tables including the design. This can save you a world of hurt if some one thinks they know better and tweak to table or design to ruin.
- And yes, the Front end can be small enough to copy to floppy.

The downer on using a front end / back end is that you have to maintain linked tables. For example, some body forgets to login (password issue) - they will have the front end loaded, but the back end with the data will not be accessable because they do not have access to the backend.

Richard

 

[Edski]

Just to add to Richard's last point:
There is code out there to check for broken or missing links on startup. You can then trap the FE before any forms are loaded. If it's a case of the BE being moved then you can ask the local user to find it easily enough and restore the link. If it's a case of not logging in then that's the user's fault. Thus I don't see this as a disadvantage. The problem is with the user not logging in, not the issue of splitting or not. So I still don't know of any disadvantages.

If anyone knows of any I'd be happy to hear.

 

[MoLaker]

The key here might be in that WaltW has only 5-10 users on his databases. With only that number, the advantages of a split database over not split shrink substantially. Network traffic would be relatively low regardless of how the databases are configured. Maintenance might not be a particular problem with so few users, either. If users don't have their own front-end, you don't have the issue of distributing an updated database and with so few users, updating a database residing on the server shouldn't pose a particular problem either. Also, it sounds as though the databases are on an internal server (not distributed to a customer) which reduces the problem of delivering an update.

All that said, I would still recommend splitting databases in a multi-user environment almost every time. If for no other reason than "just in case". Just in case we add users. Just in case the database gets really large, just in case I want to upsize, etc., etc. Whether the front-end is placed on the server or on each workstation would depend on network traffic issues. As for a user opening the database when not logged in to the network, that's easy to control with user level security and placing the workgroup file on the network. If he can't log in, the database won't open and there won't be any missing links. Of course, missing links can be handled in other ways, as Edski stated.

Corrupted databases - I cannot prove it one way or the other, but I'm inclined to believe corrupted "data" is less likely with a split database. I have written and maintained several split databases on Novel networks and on Microsoft networks - both types with their own level of failures resulting in ungracefull database exits. Over a period of several years, we experienced several corrupted databases - some on the workstations and some on the server. In all cases, if the database was split (most were), the back-end was never corrupted - whatever that might prove.

Just my 2, well maybe 5 cents worth.

 

[GDGarth]

I've got both split and unsplit multi-user databases on our network. Those that are split are less likely to suffer from corruption, and much easier to fix when they do crash.

The unsplit databases will crash, usually inflicting damage, when a network error gives them the "disk or network error" message. A split database may or may not crash due to a momentary interrruption of network availability.

I keep a copy of the front end on the network, and copy it over to the user's local hard drive every time they open the database. That way, they always have a new, uncorrupted version of the front end.

 

[WaltW]

Thanks very much to all who responded to my question. Your input has been very, very helpful!

Walt