Why Split the database?

[Dbless]

I've been looking all over on the web for article to describe why the Access database needs to be split into front-end/back-end and what the benefits there are in doing so. Can anyone help explain why this is necessary and the benefits of do so. My database is about 300 meg. in size running Access 97' on a NT server.

Pre-thanks

 

[THWatson]

If you are running on a stand-alone computer, and the database is only on that computer, then you don't need to split the database for operational purposes.

The general advantages of splitting the db into Front and Back ends would be (1)so the back end, which would normally be smaller than the front, could still be copied onto a floppy without having to copy the entire db, and, more importantly, (2)so that the front end could be worked on, changed, whatever, and then updated without affecting the back end.

In my case, I have developed a db for a user in another city. The front end is almost 2 meg now, but the back end is less than 1/2 meg. Once I split it, it was much easier to make updates to the front end as needed, without needing to have the user send me her entire file, because she would be adding data all the time I was working on things regarding the front end. I could send her the new front end, she would copy it over the old front end, do any re-linking if necessary, and be off and running.

I think that there should be an article in the Microsoft Knowledge Base about splitting a database. And I have a couple of Access 2000 books that have information about the advantages and disadvantages.

Does that help?

Tom

 

[WildHare]

Splitting has definite benefits IN CERTAIN SITUATIONS.

It allows you to separate the data from the application - meaning that you can modify the forms/reports/queries/etc by working in a copy of the application (Front End) database, without having to take the system offline - users can continue to work in the 'live' system. When you have finished with the modification(s), each user simply gets, or refers to, the updated copy of the Front End.

Here is a sample scenario: You develop a system for a group of users on a network. You place the back end database which is nothing but TABLES, on a network share.

You either give each user a copy of the front end, or have them point to a networked front end - it doesn't really matter too much how you do this.

If a modification is needed, you simply edit YOUR copy of the front end, making needed changes. Then update the production copy of the Front end, and Voila, all the users have the new version.

If you don't split the database, NO USERs will be able to use the system while you have it in "edit" mode.

The front end LINKS to the tables in the Back end.

However, there are some things to remember about using JET (Access) as the back end data engine. First and foremost, JET is acting ONLY as a file server. When a user requests a set of records from a table, the ENTIRE TABLE will be shipped over the network to the user, where HIS machine will execute the query to extract the set of records. Thus, splitting a database does nothing for network traffic, and in some cases, may actually increase it. This is in opposition to how a network based data engine such as SQL Server would work - in that scenario, the data engine would analyse and run the query, and pass ONLY the result set down the network to the user. Less network traffic, but more Network CPU load. Pick 'em and choose 'em...

There are costs and benefits to 'splitting' - it does not need, IMHO, to be done ALL THE TIME. It depends on the situation.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[grnzbra]

Also, you can have HUGE amounts of data in your back end. Each of the big tables has its own database so it can grow to 1 or 2 gigs in size. If it gets bigger, break the table into several smaller tables (let's say by month) and use a union query to join them). I used to work on a system that had 5 one-table databases for a back end.

 

[psemianonymous]

Tony Toews (MS Access MVP) has excellent pages on everything Access. One of his pages covers splitting:

http://www.granite.ab.ca/access/splitapp.htm