Front End/Back End

[Garret]

Hello Everyone. I kind of found some information on this topic
yesterday but I still have a few questions.
I have Access 2000, and I am looking to create a Front End interface
that Users will be using, and a Back End interface (or basically
regular MS Access) that the Administrator can use. I want to do this
using only one copy of Access, in other words, I don't want to buy
Access for each of the computers that will be using the Front End.
I've heard of MDE as a Front end file, is this correct? If so, how can
I create an MDE, distribute it to the Users' computers, and keep the
MDB (main database) separate for Admin use?
Also, the Admin has to be able to update the Database and have the
changes happen everywhere (so Front-End users information is constantly
updated).
Much Thanks.

 

[Rick Brandt]

Hello Everyone. I kind of found some information on this topic
yesterday but I still have a few questions.
I have Access 2000, and I am looking to create a Front End interface
that Users will be using, and a Back End interface (or basically
regular MS Access) that the Administrator can use. I want to do this
using only one copy of Access, in other words, I don't want to buy
Access for each of the computers that will be using the Front End.
I've heard of MDE as a Front end file, is this correct? If so, how can
I create an MDE, distribute it to the Users' computers, and keep the
MDB (main database) separate for Admin use?
Also, the Admin has to be able to update the Database and have the
changes happen everywhere (so Front-End users information is
constantly updated).
Much Thanks.

You're inter-mixing terminology.

In a split app there is a separate front end that you give each user and one
that you keep as the developer and a shared back end file that holds the data.
The back end is not "for the Administrator".

Separately, the front end files that you give your users can be MDE files rather
than MDB files which prevents the users from modifying code-based objects or
viewing the code. An MDE still needs Access on the computer to be used.

Separately, your users can be given the Runtime version of Access to run the
file you give them instead of having to have a licensed version of Access
installed. When you install the Runtime you are in fact installing full-blown
Access. It just includes hundreds of registry settings that prohibit all of the
development capabilities. It can be used to "run" Access files, but not to
create them or modify their design.

In order for you to give them the Runtime you would have to purchase the
Developer's Tools for your version of Access. It is not inexpensive and your
app has to have quite a bit more "polish" to be successfully used in the Runtime
environment so this is only worthwhile from a cost standpoint if you will be
distributing to at least several users. For just a handful, you are better off
just purchasing Access for each user.

 

[Garret]

What kind of cost are we talking about here? So far I only have 1 copy
of Access 2000. The Database will need to be accessed through the
Front End on at least 4 or 5 other computers.
Also, you were telling me yesterday of how to customize the menu bars
and so forth. Is this what I would still do when creating this Front
End? How about if I used Developer's Tools? Does the Developer's
Tools require Professional knowledge of Access?

 

[Rick Brandt]

What kind of cost are we talking about here? So far I only have 1 copy
of Access 2000. The Database will need to be accessed through the
Front End on at least 4 or 5 other computers.
Also, you were telling me yesterday of how to customize the menu bars
and so forth. Is this what I would still do when creating this Front
End? How about if I used Developer's Tools? Does the Developer's
Tools require Professional knowledge of Access?

First off you would have to scrounge on the internet a bit to even acquire a
copy of the developer's tools for Access 2000 since it's two versions old.
That version doesn't provide the Developer's tools as a separate package (I
don't think) so you are basically buying the entire Office Suite again (just
the Developer's version). I have no idea what you would find it for, but it
originally sold for about 300 dollars for the upgrade version and about
double that for the full version. For only 4 or 5 computers this might not
be worth it when you also take into account the extra time and effort you
will have to put in (see below).

Funny you should ask about menu bars. Part of the "extra polish" I was
talking about when using the Runtime is the NONE of the built in Menus,
Toolbars, or Shortcut menus are available in the Runtime, nor is access to
the db Window. The developer has to provide interfaces to all of the apps
functionality with menu forms and custom command bar objects.

In addition, error handling has to be much more robust in a Runtime app.
Errors that a licensed copy of Access would handle for you will usually
terminate a Runtime app unless a proper custom error handler is in place.
Since Macros have no error handling you basically should avoid any macros at
all in a Runtime app. There are a few features that won't work in the
runtime (Filter-By-Form for one), so you have to watch for those as well.

As for the skill level to actually create a runtime distributiuon package I
would say that it is only "medium", but a higher level is often required to
deal with any "issues" that come up. For the most part installing the
runtime is fairly straight-forward, but it does cause problems on systems
that already have another version of Access installed. It is not something
I would go into lightly.

 

[Garret]

So the only way to have the other Users access a Front End of this
Database is to either buy Access on each of their computers, or buy the
Developers Tools for Runtime?
In the Developers Tools, in creating all the menu bars manually like
that, I want to know if this program links directly to MS Access, so
you can include code like:

DoCmd.OpenForm "Form1"

or does the Developers Tools have to seek out routes to the MS Access
Database to do something more like:
DoCmd.OpenForm "C:/Documents/Folder1/ect....

or something completely different?

Also, I still need to know how all these will be linked so everyone is
using the same version of the Database, 24/7.

 

[Rick Brandt]

So the only way to have the other Users access a Front End of this
Database is to either buy Access on each of their computers, or buy
the Developers Tools for Runtime?
Correct.

In the Developers Tools, in creating all the menu bars manually like
that, I want to know if this program links directly to MS Access, so
you can include code like:

DoCmd.OpenForm "Form1"

All of the same commands and code work identically. You just don't get the
built in command bars or the db window. For example; if you had an Access
file that did not have a custom main menu defined in Startup and did nothing
to automatically open a form at Startup, when launched in the Runtime you
would have a big gray window and no way to do anything (except close it).

[snip]

Also, I still need to know how all these will be linked so everyone is
using the same version of the Database, 24/7.

If you give each user a front end with links to a common back end then they
are all seeing and editing the same data. If you want them to have the same
version of the front end after you make changes then you have to replace all
of their files with a new one. The use of the Runtime has no relevance to
that issue. There are methods and tools you can acquire that can automate
this distribution for you.

 

[Garret]

All of the same commands and code work identically. You just don't get the
built in command bars or the db window. For example; if you had an Access
file that did not have a custom main menu defined in Startup and did nothing
to automatically open a form at Startup, when launched in the Runtime you
would have a big gray window and no way to do anything (except close it).

Thats probably what I would want right? Then I can create my own menu
bars. Theres no reason that a User should see the database window
either

If you give each user a front end with links to a common back end then they
are all seeing and editing the same data. If you want them to have the same
version of the front end after you make changes then you have to replace all
of their files with a new one. The use of the Runtime has no relevance to
that issue. There are methods and tools you can acquire that can automate
this distribution for you.

If I buy MS Access for each User, then are the tools required? If not,
do you have any links to these tools, or at least specific names so I
can research them?

On a side note, if I don't do either of these (buy 5 Accesses or
Developer's Tools for Runtime), and I just create my own interface
using MS Access how you were telling me how to yesterday, is there then
a way to distribute that to each user?

 

[Rick Brandt]

Thats probably what I would want right? Then I can create my own menu
bars. Theres no reason that a User should see the database window
either

The runtime does automatically impose a "security" of sorts yes.

If I buy MS Access for each User, then are the tools required? If not,
do you have any links to these tools, or at least specific names so I
can research them?

I don't understand what you're looking for. Your app is a file. To make
design changes to the app you make design changes in your copy of the file
and when you are finished you give all your users a copy of the new file
which replaces the old copy they had previously. Access (be it licensed or
the Runtime) is merely the executable that is going to run the file you give
them. It has nothing to do with "keeping them on the same version". That
is your problem.

Tony Toews has a tool that allows for updated versions of the front end file
to be automatically distributed to each user when they open the application
any time a source copy on the network is updated. I personally have the
users first open a separate Access file that I call the "VersionChecker".
It compares version numbers in their local file to a table in the back end
and if they don't agree a new copy of the front end is copied to their PC
and then opened.

On a side note, if I don't do either of these (buy 5 Accesses or
Developer's Tools for Runtime), and I just create my own interface
using MS Access how you were telling me how to yesterday, is there
then a way to distribute that to each user?

No. This would be the same as giving them an Excel file when they don't
have Excel installed. They would double-click it and Windows would indicate
"I don't know what to do with this file". Now, if by "create your own
interface" you mean write a stand-alone executable program in C++ or VB or
some other programming tool and simply use an Access file to store the data
then yes, you can do that.

 

[Garret]

I don't understand what you're looking for. Your app is a file. To make
design changes to the app you make design changes in your copy of the file
and when you are finished you give all your users a copy of the new file
which replaces the old copy they had previously. Access (be it licensed or
the Runtime) is merely the executable that is going to run the file you give
them. It has nothing to do with "keeping them on the same version". That
is your problem.

I guess I didn't mean "version". I just meant that everyone should be
using the same Data in the same Database. For example, if I give
everyone the database, I want records to be added/updated/removed and
the front end guys can then see the updates instead of of using the
same "version" that was originally distributed.

Tony Toews has a tool that allows for updated versions of the front end file
to be automatically distributed to each user when they open the application
any time a source copy on the network is updated. I personally have the
users first open a separate Access file that I call the "VersionChecker".
It compares version numbers in their local file to a table in the back end
and if they don't agree a new copy of the front end is copied to their PC
and then opened.

This appears what I would be looking for.

No. This would be the same as giving them an Excel file when they don't
have Excel installed. They would double-click it and Windows would indicate
"I don't know what to do with this file". Now, if by "create your own
interface" you mean write a stand-alone executable program in C++ or VB or
some other programming tool and simply use an Access file to store the data
then yes, you can do that.

I wouldn't know how to program like that. But I thought there was a
'read-only' kind of file that did not require Access to be installed in
order to access the Database, except they can still add/update/delete
records but cannot design any new tables/forms/reports/ect. Nothing
like that?

 

[Rick Brandt]

I guess I didn't mean "version". I just meant that everyone should be
using the same Data in the same Database. For example, if I give
everyone the database, I want records to be added/updated/removed and
the front end guys can then see the updates instead of of using the
same "version" that was originally distributed.

That is accomplished with the FrontEnd/BackEnd setup. All front ends have
links to the same back end tables so everyone is working with the same
"database" even though they each have their own application file.

This appears what I would be looking for.

Yes, but for distributing design changes to the front end, not for data.

I wouldn't know how to program like that. But I thought there was a
'read-only' kind of file that did not require Access to be installed
in order to access the Database, except they can still
add/update/delete records but cannot design any new
tables/forms/reports/ect. Nothing like that?

What you are describing is the Runtime. It is "free" for those who are
using it, but it is not free for the developer who gives it to those users.

 

[Garret]

I was looking into it and was curious...what is an .MDE then?

 

[Lynn Trapp]

an .MDE is a version of a database file that has had all the code compiled
and the text version of all code removed. It also prevents anyone from
modifying certain objects in the database.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Garret]

an .MDE is a version of a database file that has had all the code compiled
and the text version of all code removed. It also prevents anyone from
modifying certain objects in the database.

Is that what I should use as a front-end? I've heard that many people
do use this, and I find it hard to believe that every database
programmer goes through all the trouble like this when making a
front-end/back-end system.

 

[Lynn Trapp]

Is that what I should use as a front-end? I've heard that many people

do use this, and I find it hard to believe that every database
programmer goes through all the trouble like this when making a
front-end/back-end system.

It depends entirely on your situation. If you need to prevent users from
making any changes to forms and reports and code, then it is a good way to
prevent it. If some of your users need to be able to create and/or modify
those things, then you don't want to use it. It's not really that much
trouble to do, however.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Garret]

It depends entirely on your situation. If you need to prevent users from
making any changes to forms and reports and code, then it is a good way to
prevent it. If some of your users need to be able to create and/or modify
those things, then you don't want to use it. It's not really that much
trouble to do, however.

Can't I still use the Security system with the different Users/Groups
to determine who can change data in which forums? All I need for the
Users who use the front-end is to be able to see all the forms, and be
able to add/update/delete records.

Now that I think about it, only a few of the Users (a few of the
bosses) will have the ability to delete records. I don't know if I
should have a command button that becomes locked until it is one of the
Administrators who is logged in or what...I wouldn't know how to do
that either =/.

 

[Rick Brandt]

Is that what I should use as a front-end? I've heard that many people
do use this, and I find it hard to believe that every database
programmer goes through all the trouble like this when making a
front-end/back-end system.

What trouble? You finish your current batch of design changes and then you
go to the menu and make an MDE file. Takes less than a minute.

 

[Lynn Trapp]

Can't I still use the Security system with the different Users/Groups

to determine who can change data in which forums? All I need for the
Users who use the front-end is to be able to see all the forms, and be
able to add/update/delete records.

Yes, you can certainly use the user level security, but with Access a
multi-faceted approach to security is best -- ULS, MDE's, Runtime, and your
own special forms of coded security combined provide the best security.

Now that I think about it, only a few of the Users (a few of the
bosses) will have the ability to delete records. I don't know if I
should have a command button that becomes locked until it is one of the
Administrators who is logged in or what...I wouldn't know how to do
that either =/.

That's an excellent approach. The Security FAQ contains code for determining
whether or not a user is in the Admins group.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Garret]

What trouble? You finish your current batch of design changes and then you
go to the menu and make an MDE file. Takes less than a minute.

I was talking about the whole thing with programming in Runtime and/or
buying everyone Access. How do you make and MDE file anyway?

 

[Rick Brandt]

I was talking about the whole thing with programming in Runtime and/or
buying everyone Access. How do you make and MDE file anyway?

I would imagine that in most corporate environments all users are simply
given Office Proffessional (which includes Access) and the complications of
the runtime simply don't enter into it. That is how it is in my company,
but I actually still use the runtime because over the years most users have
upgraded to newer versions of Access and my apps have (for the most part)
stayed in Access 97.

Even then the runtime install is something that is done once per user and
that is the end of it. Future upgrades are simply swapping out a file just
the same as if they were using a licensed copy. I haven't actually created
a new Runtime "package" in a couple of years.

Making an MDE is one of the options on the main toolbar. In Access 2000 it
is found at...

Tools - Database Utilities - Make an MDE

 

[Garret]

I work for a small business thats kind of like a large business too in
terms of how they operate. But I'm not sure if we have Office
Professional, or just Access. I can find out-so you are saying that
runtime comes with this so I would not have to buy it?
Also, I don't think that I would have to purchase an Access for each
computer, I could just install Access off of the same CD onto each -
its only going on 5 or 6 computers, and shouldn't ever be accessed by
more than 1 or 2 people at a time anyway.
Can computers without Access use .MDE files?