Front End vs Back End

[JethroUK©]

i've put a lot of work into my forms/reports and i want to protect &
seperate them from the main data tables

if i split the databse in two - should i leave the queries with the tables
or move the queries to the front end with the forms/reports (assuming i have
a choice)

 

[JethroUK©]

Query solved - I've just found a built-in splitter (after i'd already split
it) - it puts queries with the front end stuff

 

[Joe Cilinceon]

Query solved - I've just found a built-in splitter (after i'd already
split it) - it puts queries with the front end stuff

If you want the queries with the tables you can open the backend and import
them then link them from the front end. I've looked at a couple of
commercial applications built on Access mdb files and have seen them done
both ways.

 

[John Vinson]

i've put a lot of work into my forms/reports and i want to protect &
seperate them from the main data tables

if i split the databse in two - should i leave the queries with the tables
or move the queries to the front end with the forms/reports (assuming i have
a choice)

The tables go in the backend. EVERYTHING else goes in the frontend.

John W. Vinson[MVP]

 

[Joe Cilinceon]

The tables go in the backend. EVERYTHING else goes in the frontend.

John W. Vinson[MVP]

I have a question John about this. I've looked at several commercial
programs using mdb file structure and about half have the queries in the
back end. I was just wondering what the advantages are to either setup. I
did notice that with an mde file queries can be added and edited but
reports, module, forms can't.

 

[JethroUK©]

I have a question John about this. I've looked at several commercial
programs using mdb file structure and about half have the queries in the
back end. I was just wondering what the advantages are to either setup. I
did notice that with an mde file queries can be added and edited but
reports, module, forms can't.

i assumed mde compiled forms/report/code/modules - but you cant compile
tables/queries

i'm not sure of benefits/disadvantages with the queries (hence the
question) - but the splitter put them in the front end, so i assume thats
the best place

 

[Joe Cilinceon]

I'm kind of curious about the advantages and disadvantages of both methods
myself.

I have a question John about this. I've looked at several commercial
programs using mdb file structure and about half have the queries in
the back end. I was just wondering what the advantages are to either
setup. I did notice that with an mde file queries can be added and
edited but reports, module, forms can't.

i assumed mde compiled forms/report/code/modules - but you cant
compile tables/queries

i'm not sure of benefits/disadvantages with the queries (hence the
question) - but the splitter put them in the front end, so i assume
thats the best place

 

[Rick Brandt]

I'm kind of curious about the advantages and disadvantages of both methods
myself.

Queries are executed in the FE. What you are describing as "queries" are just
the saved definitions of queries. If stored in the BE the FE will have to pull
those definitions over the network before it can execute them. All this
accomplishes is needless network traffic. I can think of no advantage to
putting the queries in the BE.

 

[Joe Cilinceon]

Queries are executed in the FE. What you are describing as "queries"
are just the saved definitions of queries. If stored in the BE the
FE will have to pull those definitions over the network before it can
execute them. All this accomplishes is needless network traffic. I
can think of no advantage to putting the queries in the BE.

Thanks Rick, I was wondering since the #1 program in my field does just
that. However it isn't a network setup but on 1 computer system.

 

[Rick Brandt]

Thanks Rick, I was wondering since the #1 program in my field does just that.
However it isn't a network setup but on 1 computer system.

Well in a split app deployed on a single PC the only real thing you are gaining
by splitting is the ability to easily update the front end simply be replacing
it without disturbing the data in the back end. Here again there is nothing to
"disturb" in a query definition and a front end upgrade could very often require
new and/or changed queries. These also seem to point to the front end as their
logical location.

 

[Albert D.Kallal]

Thanks Rick, I was wondering since the #1 program in my field does just
that. However it isn't a network setup but on 1 computer system.

No, likely does not do the above. If you do have a split database, then how
does the front end use, or execute queries in the back end?
(answer: you can't link to those queries, and you can't "see" them in the
front end).

The only reason why I can think of some queries in the back end is that they
are there for maintenance type stuff, or are not actually used. They also
could possibly be used if/when the back end is opened via automaton, but I
doubt that.

It is also possible they are "copied" on occasion from the back end to the
front end.

If you on occasions open up the back end for types of maintains, then those
queries can be used.

However, how can you use queries in the back end when you launch the front
end? You have to explain this to me, or what you say don't make sense?

 

[Dirk Goldgar]

However, how can you use queries in the back end when you launch the
front end? You have to explain this to me, or what you say don't make
sense?

I suppose you could open a separate connection to the back-end (using
OpenDatabase in DAO, or via an ADO Connection object) and execute them
that way. But it's not clear to me what the point would be.

 

[david epsom dot com dot au]

People who don't have their application stored in
a database (like we do) still have to find somewhere
to store their queries.

They can store them as text in the executable.
They can store them as text resources in the executable.
They can store them in the same database where their data is stored.

Storing your queries in the same database where the
data is stored is often better than storing them
as text in the executable because

1) You can update the queries separately from the executable.
2) The queries hide the database internals from the executable.
3) The queries can be precompiled and optimised in the database.

You can partition your application into three (or more) layers:

Forms
Business Logic (queries)
Data

If you want to have a two layer system, you can make
a decision about where you want to place the middle
layer. You can even split the middle layer if you
want, with some queries in one place and some in
the other:

First the architecture implications:
VB6 + SQL Server = queries in database

Then the coding practice decisions:
VB6 + database = queries always in the same place for both MDB and SQL
Server

And the performance implications:
SQL Server = queries in BE database
Access MDB = queries in FE database

And the application development logic:
Frequent changes = stored with frequently changing stuff
Infrequent changes = stored with static stuff

So if your database structure was frequently
changing, you would naturally put queries there
so that your application didn't have to change
at all.

(david)

 

[Joe Cilinceon]

I was looking at the demo of the app again and to be honest I've never paid
any attention to the front end (exe file). I would assume that is it
probably written in some other such language and just uses the access file
structure for its tables. I never paid much attention to the front, as I
was more interested in their table design and relationships.

 

[Brendan Reynolds]

That's the point exactly, Joe. When the 'front-end' application is an Access
application, there's little if any reason to have queries in the 'back-end'
data MDB. But if the application is *not* an Access application, then there
are good reasons to save queries in an MDB.

 

[Rick Brandt]

That's the point exactly, Joe. When the 'front-end' application is an Access
application, there's little if any reason to have queries in the 'back-end'
data MDB. But if the application is *not* an Access application, then there
are good reasons to save queries in an MDB.

Yes the "point" changes entirely when the front end in not Access.

 

[Joe Cilinceon]

Thanks Brenden & Rick for the explanations. I'm just trying to get a handle
on the why's of this.

 

[Marshall Barton]

Good post David. It provides a nice framework for thinking
about this issue.