Frontend and backend database question

[LightLY]

Dear Access gurus,

I would like to split my access database into frontend and backend. I
would to ask the helpful Access Gurus on this forum if the following
can be done;
1. Put Frontend and backend database on the same PC. Not sure if this
can be done because I have read that the backend database should be
placed on some server. I would like to keep things simple by just
distributing both frontend and backend databases at the same time.
2. Queries placed at frontend database. Backend database contains only
the tables that contain the data.
3. Distribute frontend and backend database to users. Users will have
freedom to create their own queries. When data changes, just
distribute the backend database to users only. This is so that the
customized queries of the users remain intact.

May I know if the above can be done? Thank you very much in advance.

 

[Tony Toews]

1. Put Frontend and backend database on the same PC. Not sure if this
can be done because I have read that the backend database should be
placed on some server. I would like to keep things simple by just
distributing both frontend and backend databases at the same time.

No, there can only be one backend file which all the PCs access (no
pun intended) via individual FEs. (Other than backups or possibly
testing purposes residing on your own PC.)

It is stronly recommended each user must get their own copy of the FE

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
free Auto FE Updater utility at http://www.autofeupdater.com/ to make
the distribution of new FEs relatively painless.. The utility also
supports Terminal Server/Citrix quite nicely.

2. Queries placed at frontend database. Backend database contains only
the tables that contain the data.

Correct. Queries, forms, reports, macros and modules go in the front
end. Tables and relationships go in the back end.

3. Distribute frontend and backend database to users. Users will have
freedom to create their own queries. When data changes, just
distribute the backend database to users only. This is so that the
customized queries of the users remain intact.

No, distribute your FE as an MDE to minimize the changes the users can
do. Now as you update the FE, and you will update it to add new
features and fix bugs, then the users get a new copy of the FE.

Power users get an almost empty MDB/ACCDB which contains only linked
tables and a few starter queries. Then they can create their own
queries and reports and/or export to Excel. Don't think that many
of your users will be creating queries. That's more of a power user
thing for folks who need to analyze data or to distribute customized
data in the form of Excel spreadsheets to clients.

Tony

 

[LightLY]

No, there can only be one backend file which all the PCs access (no
pun intended) via individual FEs.    (Other than backups or possibly
testing purposes residing on your own PC.)

It is stronly recommended each user must get their own copy of the FE

See the "Splitting your app into a front end and back end Tips" page
athttp://www.granite.ab.ca/access/splitapp/for more info.  See the
free Auto FE Updater utility athttp://www.autofeupdater.com/to make
the distribution of new FEs relatively painless..   The utility also
supports Terminal Server/Citrix quite nicely.


Correct.  Queries, forms, reports, macros and modules go in the front
end.    Tables and relationships go in the back end.


No, distribute your FE as an MDE to minimize the changes the users can
do.     Now as you update the FE, and you will update it to add new
features and fix bugs, then the users get a new copy of the FE.

Power users get an almost empty MDB/ACCDB which contains only linked
tables and a few starter queries.  Then they can create their own
queries and reports and/or export to Excel.     Don't think that many
of your users will be creating queries.  That's more of a power user
thing for folks who need to analyze data or to distribute customized
data in the form of Excel spreadsheets to clients.

Tony

Thank you very much for your quick and detailed reply. I am most
grateful.

Actually, my users can be considered as power users. So, I am
foreseeing that they would want to have the flexibility to create
their own queries. However, if I were to distribute as a single .mdb,
then everytime I update the database, my users' queries will be
overwritten. Then, they will complain up and I get whacked down. This
is why I have decided to distribute to my users a database consisting
of a front-end and a back-end so that they can customize their own
queries without fearing the queries will be overwritten when new data
is released.

 

[a a r o n . k e m p f]

splitting is not the reccomended way to do this.. Splitting was
replaced by upsizing about a decade ago

 

[Tony Toews]

On Mon, 14 Jun 2010 00:31:58 -0700 (PDT), LightLY

Please ignore Aaron Kempf's posting as he very seldom has anything
useful to post.

Actually, my users can be considered as power users. So, I am
foreseeing that they would want to have the flexibility to create
their own queries. However, if I were to distribute as a single .mdb,
then everytime I update the database, my users' queries will be
overwritten. Then, they will complain up and I get whacked down.
Understandable.

This
is why I have decided to distribute to my users a database consisting
of a front-end and a back-end so that they can customize their own
queries without fearing the queries will be overwritten when new data
is released.

Is this BE database read only? That is does it come from some other
data system. If so then what you are suggesting is somewhat
reasonable.

If, however the users are updating the backend with data using the FEs
then you can't distirbute the BE like you are suggesting. There can
only be one Access file containing tables that the users can update.
It can't be distributed as you suggest because the users will be
updatnig their local set of tables and not the common set of tables.

Tony

 

[Access Developer]

splitting is not the reccomended way to
do this.. Splitting was replaced by upsizing
about a decade ago

You never give up spreading misinformation, do you, Mr. Kempf?

Splitting Jet/ACE databases and using Access as a user interface to server
databases are each appropriate approaches in some situations, but not
directly comparable. Splitting has not been replaced by upsizing, ever.
Using Access with server databases, BTW, pre-dates the "decade" Mr. Kempf
often quotes.

Larry Linson
Microsof Office Access MVP

 

[LightLY]

On Mon, 14 Jun 2010 00:31:58 -0700 (PDT), LightLY


Please ignore Aaron Kempf's posting as he very seldom has anything
useful to post.


Is this BE database read only?   That is does it come from some other
data system.   If so then what you are suggesting is somewhat
reasonable.

If, however the users are updating the backend with data using the FEs
then you can't distirbute the BE like you are suggesting.   There can
only be one Access file containing tables that the users can update.
It can't be distributed as you suggest because the users will be
updatnig their local set of tables and not the common set of tables.

Tony

Thank you for replying again. You have been most helpful. I am now
more confident to start on my database soon.

 

[David W. Fenton]

"a a r o n . k e m p f @ g m a i l . c o m"


You never give up spreading misinformation, do you, Mr. Kempf?

Splitting Jet/ACE databases and using Access as a user interface
to server databases are each appropriate approaches in some
situations, but not directly comparable. Splitting has not been
replaced by upsizing, ever. Using Access with server databases,
BTW, pre-dates the "decade" Mr. Kempf often quotes.

Upsizing *is* a form of splitting, it seems to me!

 

[Access Developer]

Upsizing *is* a form of splitting, it seems to me!

In the broadest sense of the word, you're correct. In the Access world,
"splitting" is generally considered a shorthand for "splitting a Jet/ACE
database into front and back ends", at least in my experience. And, I think
that is the sense in which Mr. Kempf was using the term.

Larry Linson

 

[James A. Fortune]

Upsizing *is* a form of splitting, it seems to me!

It does no harm to think of it that way. We both agree that all
multiuser Access databases should be split to begin with. I would
characterize the relationship as 'upsized implies split'. That
handles Access databases that are originally split or unsplit, and
allows for a term other than splitting to be used for transforming
just the backend part of a split Access database. When I did some
upsizing, I believe I simply ran MAKE TABLE queries inside an ADP
before adding a SQLServer key field instead of using the upsizing
wizard.

James A. Fortune
(e-mail address removed)

95% of this game is half mental. -- Yogi Berra

 

[David W. Fenton]

In the broadest sense of the word, you're correct. In the Access
world, "splitting" is generally considered a shorthand for
"splitting a Jet/ACE database into front and back ends", at least
in my experience. And, I think that is the sense in which Mr.
Kempf was using the term.

"Sense" and "Aaron Kempf" are not terms that live peacably together
in the same post, seems to me.

 

[Access Developer]

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Sense" and "Aaron Kempf" are not terms that live peacably together
in the same post, seems to me.

Point well taken, and agreed.

Larry