Front End / Back End – When to Use? (Any)

Topic

New Reply

So I hear a lot about front-ends and back-ends. Never used them myself. Until Monday morning, when I inherited a whole raft of databases split front-end back-end. I’ve since done a little bit of reading – and searching around the lounge – but have seen little of interest re the question: when to use an f/e-b/e set up? Anyone out there fancy either pointing me in the right direction of some high quality reading matter on the subject, or writing a star post about it? Even a thread holding varying view-points would be of help to me right now – and therefore hopefully someone else in the future. If the latter, my situation if it helps for the answer (although I guess a star post would need to transcend one individual’s requirements) is a 2k/2k setup, local hard-drive, one user, once a day for fifteen minutes usage scenario for each database. The most typical of these databases has a frontend size of ~50 Meg after compacting containing approx 40 queries and 20 tables, whilst the backend is 6 Meg with four tables – two containing large amounts of backlog and FGI data, with the other two containing small amounts of product heirarchy/lookup information.

Any takers?

Reply | Quote

Replies

I won’t try to write a full blown star post. Two comments about when/why to use them:
* to reduce network traffic and so increase speed. Front end is on local machine , back end on server, so only the data has to travel, not the forms etc.
* to separate data from code. You can upgrade a front end to a new version and have it automatically connect to the data in the backend – no importing to do. You can switch between a real environment and a test environment simply by renaming a backend file.

Reply | Quote

John’s post elegantly and concisely summed up the reasons for using a split database schema. I really can’t add much more than that to the “why to use it”. As to the “when to use it”, I’d say you really should get in the habit of doing it all the time.

Reply | Quote

If your front-end contains twenty tables and your back-end only six, I hope those twenty are mostly temporary tables. Your data tables belong in the back end. John and Mark have hit the high points, but no one mentioned that front ends usually *are* bigger than backends (at least until back ends get very large) because they contain objects like queries, forms, reports, code modules and lots of temporary objects created either by the system or by the application.

Reply | Quote

One other reason is applicable if you have no “permanent” tables in the front-end (i.e. ones that contain important data that must be kept from session to session). Access 2000 is somewhat more stable than Access 97 (I can’t speak for Access 2002), but in both cases in my experience if a database becomes corrupt the corruption almost always relates to the forms and reports which are in the front-end. So, if all of your tables are in the back-end you can restore the front-end without any loss of data.

Reply | Quote

Brooke:

In addition to the above mentioned good reasons, perhaps one of the best is that if you have an app with more than one user entering data all users are accessing the same data tables in the back end. As stated above, the front end goes on the individual work stations and the back end is on a shared resource.

This avoids replication / append / synchronization problems (challenges?).

Reply | Quote

Well, thanks to you all for taking the time to respond. That’s given me a fair bit to think about.

Reply | Quote

There are a number of other considerations that are secondary most of the time, but can occasionally be a show-stopper. I’m going to try to put together a comprehensive post that lays out the pros and cons of front-end/back-end designs. I did a search and found lots of references to split databases, but only an occasional reference as to why you might want to create a backend, so a single post seems like a good idea.

Reply | Quote

Quite simply, the time to use a BE/FE arrangement is when you are going to have multiple users. Any multi-user situation, unless the users are going to use the same machine. I guess the best way to judge that, is if you need to have tables in your DB available through a network. If that’s the case, you should put the tables on the network in a BE, and everything else locally, in the FE. (Some people do put their FE’s on a network resource, but if you can avoid that, DO SO!)

Reply | Quote

OK – here’s one persons view of the world relative to working with split databases – your comments and opinions are welcomed:

There are a number of reasons for splitting an Access database into a front-end and a back-end, where the back-end contains only tables, and the front-end contains queries, forms, reports and modules. More or less in the order of impact, they are:

• Significant performance benefits can be achieved if the back-end is on a server and the front-end is located on the local hard drive of the users

Reply | Quote

<>

The 50MB size of the front-end would worry me some. Our largest databases, with 100 or more linked tables, 600+ queries, 150 forms, 80 reports and 30 or so modules only runs about 40MB. It may be that you could reduce the size significantly by importing everything into a new database. Otherwise you have a real hummer on your hands!

Reply | Quote