Queries in FE or BE?

[Bob Loder]

Hi, Everyone, and Happy New Year!

I'm on the final throws of "publishing" my proprietary database (totally
Access XP). In reviewing each and every detail, and comparing with other
Db's, I've noticed that some "professionally-made" Db's have their queries
in the the BE, while most have them in the FE.
Is the following conclusion correct?:
It is more efficient, quicker execution, to have queries in the BE, and more
private to have them in the FE (once made into an .mde file)?
If this is the case, then I should put my queries in the BE, because my
program will only be used in my office among 8 users whom I can trust, and
who are not technically capable of "ripping" my Db apart and altering it.
Assuming all of the above is correct, should modules, also go in the BE -
what else?
This may be very basic, but we are in the Getting Started newsgroup, and I
think this is a very subtle design consideration that is usually overlooked.
I look forward to your opinions!

I have a second question that I shall ask in a separate post in that the
topic is very different.

Thanks,
Bob Loder
Tampa, FL

 

[Casey]

Bob,

With the speed of the newer computers, I doubt if it
would make much difference. In the type of security
situation you are in, with trusted employees, I still
recommend you make backups occasionally of the system, in
case of emergencies, to be able to replace it if something
goes noticeably wrong. Otherwise I would say you are
set. The residence of the queries issue you can learn
about in the early stages of the use of the program. If
it seems to operate at a reasonable speed, then you can
accept that as an accomodating factor, otherwise if you
want to try having the queries resident elsewhere, you can
try that. Sometimes the use of the system will tell you a
story of its own.

Casey

 

[Rick Brandt]

Hi, Everyone, and Happy New Year!

I'm on the final throws of "publishing" my proprietary database (totally
Access XP). In reviewing each and every detail, and comparing with other
Db's, I've noticed that some "professionally-made" Db's have their queries
in the the BE, while most have them in the FE.
Is the following conclusion correct?:
It is more efficient, quicker execution, to have queries in the BE, and more
private to have them in the FE (once made into an .mde file)?

I can think of no reason to put queries in the BE and have never seen an app
that did so. Queries in the BE are not available to be run in the FE (you can't
link to them like you can a table). If you used code in the FE to run a query
that was stored in the BE it would still be run totally on the client PC and
there would be zero difference in efficiency except of course that the SQL would
have to be pulled over the network first.

An MDE does *nothing* to protect tables or queries. Only code-based objects
(forms. reports, modules).

I'm wondering if you are confusing "queries in the BE" with server-based
database engines where you can have views or stored-procedures that are not only
stored in the BE, but are actually executed there as well. In that context
there is a major difference, but not when using a Jet BE which is a file-share
system, not client-server.

 

[Bob Loder]

Actually, I think you hit my confusion right on the head (see my other post
at 9:29 am).

The program where I found the queries on the BE had an .exe FE

I guess my two questions were more related than I thought!!

Thanks, Rick.

Bob Loder
Tampa, FL

 

[Rolls]

I've done this in workgroup situations with OLAP databases where we had an
MDB on the server that would be used for a month and then an updated MDB
with new YTD data would be added. Users would make their own copy. It
wasn't huge, about 30MB. I didn't "split" the database. Most users liked
it and would come to me anytime they had ideas for "enhancements" which
would typically be new queries. One paranoid supervisor insisted that she
be able to have "her" queries seperate from the group's, although what she
wanted to do was frequently at the limit of her abilities and seemed to be
intended more to frustrate than to help the group. She had more "operator
malfunctions" than the rest of the group combined.

If the user interface is complex enough so that it can't be run by multiple
users from the server without generating record locks, then it has to be run
as a desktop ap. If any user updates data (my example didn't) that has to
be available to all, then you do need a split database. Typically an icon
is placed on a user's desktop. It's developed with WinBatch or something
similar. This loads their front-end copy onto their desktop from the
server.

You could do some preprocessing (denormalize the data) so that frequently
requested query results are available within the MDB, which could reduce
record locking. If you're thinking of going to SQL Server as the BE with
Access as the FE then you might try setting the DB up as an Access Project
so you can migrate later.