Front End or Backend - Where to put what?

[BigDuke6UK]

I am designing a fairly large scale Access 2003 application. I intend to have
a front end for around 20 clients and a server backend. Server is running Win
2003 Server and clients XP Pro. I am confused as to where I should put my
code and stored queries, on the front end with the table data or on the
backend. I have two very good books by my side but they contradict each
other. Also a programmer friend of mine says that all queries and code used
to access the data tables should be kept at the front end data layer but my
thinking is that queries and code should take place at the back end in order
to spare the server unecessary workload. Any help would be greatly
appreciated as I am going round in circles at the moment.

 

[Allen Browne]

Let's get the terminology straight:
- "Back End" = the mdb file that contains the tables only. It resides on a
shared location on the server;

- "Front End" = the mdb file that contains the queries, forms, reports,
code, and linked tables. Each workstation has their own copy of the front
end, usually on their own local disk.

The only tables that you want in the front end (other than linked tables)
are things such as:
- configuation table (workstation preferences);
- temporary tables (so each workstation can run stuff independently of
others);
- large lookup tables that the users do not change (such as zip codes);
- tables that you want overwritten when you release a new front end
(software update).

 

[Roger Carlson]

Piggy-backing off of Allen's post, let me just add that you CANNOT offload
any processing to the Back-End if the BE is an Access database file (MDB).
The ONLY way you can do that is if the BE is a SQL Server database (or
equivalent server-side database). NO processing can happen in the BE (that
is on the server itself). ALL processing takes place at the workstation.
Therefore, the most logical place to have all the queries and code is in the
Front-End, which resides on the workstation.

On re-reading your post, I cannot tell if your BE is an Access mdb file or a
SQL Server database. Perhaps you can clarify.

--
--Roger Carlson
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jeff Boyce]

Pig-pile on Allen! More terminology ... Access is a "file-server", so the
back-end delivers the entire table across those skinny little network
cables. SQL Server is a "client-server", so, with more effort to add views
and stored procedures and user-defined functions in SQL Server, your Access
front-end will only retrieve the few records you actually need.

 

[BigDuke6UK]

First of all thanks for your replies.
I intend to run Access at both ends so it seems my approach as already been
decided i.e queries and code go at the Front End. However a further confusion
on my part is the tiered approach of 'Data Layer, Business Layer and
Application Layer'. My thoughts were to seperate 'data access' code and
relevant objects from the 'presentation code' relating to Form events and
reports and somwhere in between the two have the 'application layer' dealing
with all the business logic such as large scale queries, functions and
procedures and basically all the behind the scennes stuff the application is
being built for. I think I may be in danger of thinking too much and
confusing myself with abstract concepts and what is actually acheivable. Any
further comments?
Thanking you in advance.

 

[Craig Alexander Morrison]

Unless you are using SQL Server, DB2 or Oracle you can forget your three
layers.

If you are using Jet then _just_ get the design of the database right (and
use field and table validation as much as possible). This means normalise
the data for that business. Many people make the mistake of thinking that
there is only one way to normalise the same data in different businesses, I
have designed several general insurance systems and the design of the
database with essentially the same data can be significantly different
depending on the business and the way that business handles the data. Many
business rules can be accommodated in the actual design of the database
itself. When normalising the data ensure that you are looking at the
business and not the real world (the real world is too complicated).

The Access applications should not contain any constraints that can be
applied in Jet. If anything the constraints in the applications should be
inside the constraints in the database. E.g. a specific field in the
database can have values in the range 0 to 10,000,000 however a particular
application may only be permitted to record in the range 2,000 to 200,000.

BTW You are not running Access at both ends, per se, Access only runs on the
workstation and can access a file from a server. The back-end is just a file
on a file server.

 

[BigDuke6UK]

Thanks Craig, it looks like I am trying to re-design the wheel. Maybe I can
get some sleep now. zzzzzzz