Front End database

[Ann]

We've always split our database but left both the front end and back end on
the server. I'm reading a lot about always putting a copy of the front end
on each users PC. Why is that? If I leave it on the server then I only have
to make one change to any of my objects for all to use. Plus, what about
Access security? Doesn't it make that harder? I have not objection to
putting it on the user's PC but I would like to know the pros and cons if
anyone can tell me. Thanks in advance for the help.

 

[Ann]

That could explain a few of our problems but isn't it really time consuming
if you have to make changes and then copy new files to a persons PC? Are
there easy ways to do that?

 

[Jeff Boyce]

Ann

Having a single copy of the front-end on a server being hit by multiple
users simultaneously is a great way of ensuring that, sooner or later,
you'll end up with a data corruption problem. In the long run, repeatedly.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Douglas J. Steele]

Check the free Auto FE Update Tony Toews has at
http://www.granite.ab.ca/access/autofe.htm

 

[Tony Toews [MVP]]

If I leave it on the server then I only have
to make one change to any of my objects for all to use.

Corruption issues aside it would be quite difficult to make the
changes while the users are using the FE MDB/MDE. You'd have to
ensure everyone is off.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Albert D. Kallal]

We've always split our database but left both the front end and back end
on
the server. I'm reading a lot about always putting a copy of the front
end
on each users PC. Why is that?

Ask yourself why for the last dozen years is so why you've always installed
word on your local machine? Ask yourself why for the last dozen years or so
you've always installed excel in your local machine?

Ask your IT department why all the applications you purchased are installed
on each PC?

Bottom line, there's a significant difference between some data, and that of
you developing an application that has code, forms, and software that
executes on your pc.

Basically the whole computing industry as a general ruled has installed
software on each PC.

I explain in details the philosophical reasoning behind why you install
software on each PC here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Ann]

What about terminal server? That doesn't put the application on your
desktop. Or virtual machine? The way that was explained to me there isn't a
PC to put an application on.

 

[Rick Brandt]

What about terminal server? That doesn't put the application on your
desktop. Or virtual machine? The way that was explained to me there
isn't a PC to put an application on.

In those cases the application is still installed on the PC where it is
being executed.

 

[Ann]

Then our company must have done it differently. We have a document
management system that has the applications integrated. We couldn't do it
with one of our applications they were testing out on terminal server because
it wasn't on our desktop??? I'm not in that department so I don't understand
all the workings of ts or vm, only what is explained to me. Is therereliable
documentation on either that I can read to understand these two things?
They are workings towards using vm in our company and I'd like to know how it
works.

 

[Rick Brandt]

Then our company must have done it differently. We have a document
management system that has the applications integrated. We couldn't
do it with one of our applications they were testing out on terminal
server because it wasn't on our desktop??? I'm not in that
department so I don't understand all the workings of ts or vm, only
what is explained to me. Is therereliable documentation on either
that I can read to understand these two things?
They are workings towards using vm in our company and I'd like to
know how it works.

A terminal server is very easy to understand. It is exactly like any other
computer in that you install programs and files onto it. It differs in that
the user (more often users) is using special software to run things on that
computer by remote control. That software sends your mouse and keyboard
data to the TS and it also gets back information for updating what you see
on the screen. Since the only thing going back and forth is user input and
screen updates the user's computer doesn't need to be very powerful and the
amount of traffic on the network is quite small.

As a single user just think if it as another computer you can run where the
wires for the keyboard, mouse, and monitor are very long, in some cases
thousands of miles long.

I'm afraid I don't understand the issue with your document managment system,
but there certainly are some programs that don't lend themsleves to being
used on a terminal server. Some I'm sure that would not work at all.

 

[Albert D. Kallal]

What about terminal server? That doesn't put the application on your
desktop. Or virtual machine? The way that was explained to me there
isn't a
PC to put an application on.

Correct. However, each user in a sense gets their "own" desktop, my
documents etc. In this case, each user STILL should have a seperate front
end..

You are correct in the sense for terminal services there's only one
computer, but terminal services is someone an exception to our industry.

So, even with Terminal server, each user still gets their own copy of the
front end placed in a folder on that terminal server, and they still own
that front end.

The bottom line is the design and architecture of MS access means that for
the most part beach user must get a separate copy of the front end, and this
does not change even when deploying Pierce software into windows terminal
services.

Without question however if you think about it when you use terminal
services and you have ten users running those applications, you've only
installed one copy of word, or Excel on that computer. The reason why this
can work is those applications were developed with what's called RE- entrant
code, and they actually tolerate multiple users running that code.

Unfortunately the applications that you as a software developer build and
design using MS access are not re-enrant and tend not to tolerate multiple
users running the same piece of code.

Thus once again for the most part, simply follow your time honored tradition
that each user gets their own copy of the code that you're going to run.
This issue of each user having their own copy does not change when using
terminal services, or deploying to your standard network in your office.

Terminal services is somewhat of an exception as to how software is designed
and deployed. and the only special change you have to take into account when
using terminal services that each user logs on has the wrong folder with
your own copy the front end...

 

[David W. Fenton]

Without question however if you think about it when you use
terminal services and you have ten users running those
applications, you've only installed one copy of word, or Excel on
that computer. The reason why this can work is those applications
were developed with what's called RE- entrant code, and they
actually tolerate multiple users running that code.

Unfortunately the applications that you as a software developer
build and design using MS access are not re-enrant and tend not to
tolerate multiple users running the same piece of code.

Albert, this is not it at all.

The reason you can't share an MDB front end application is that each
user has write access to it, and will change it (e.g., saved
filters, not to mention the "suspect flag"). For Word or Excel (or
MSAccess.exe, for that matter), you're sharing only read-only files.

If Access did *not* write any data to a front-end MDB/MDE, then you
could safely share it with multiple users -- it has *nothing*
whatsoever to do with re-entrant code.

 

[aaron.kempf]

linked tables is not the reccomended way to do things.

you should move to SQL Server / ADP if you want to be able to share
data with multiple users.

Access just isn't reliable enough; no matter how many work arounds
these kids try to con you into.

-Aaron

 

[Greg]

I would agree about switching to an ADP/SQL Server solution. I have an ADP
solution in place that is thousands of times faster than a standard MS Access
database and it never gets currupt and never gets slower because more and
more users log onto the system. If you don't have SQL Server 2005, don't
worry, you can get a free version called SQL Server Express Edition, which
works just as good as the regular SQL Server.

But, I have only one concern about going this route at this point in time
with MS Access. I have read many reports suggesting that Microsoft is
abandoning the ADP format. Had I known this earlier, I would not have done an
ADP to begin with and would have bitten the bullet and used VB.Net instead.

But, if Access is your primary skill set, ADP/SQL Server will be your best
bet. I'm still using Access XP and will probably continue with that version
for a very long time, so you'll get quite a few years out of your investment.

 

[Tony Toews [MVP]]

. I have read many reports suggesting that Microsoft is
abandoning the ADP format.

ADPs are still present in A2007 but haven't been enhanced in recent
versions.

Had I known this earlier, I would not have done an
ADP to begin with and would have bitten the bullet and used VB.Net instead.

VB.Net would likely have been a substantially larger man hour to get
the same job done.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Pendragon]

Albert (et al),

I have a client who recently switched to a terminal server environment,
abandoning their PCs for some kind of box to connect to the server. They
initially copied the front end into a generic folder on the server without
fully understanding the FE/BE nature of the Access application. As you
suggested, I redirected their db shortcuts to their personal folders so each
has a personal copy of the FE, albeit still on the terminal server
environment. So far so good.

I have another problem, however, from this terminal server environment. I
am unable to maintain a link to the defined workgroup. While logged in as
another employee, with the db application open, I can successfully join to
the CompanyWG.mdw workgroup file (located on the primary database drive O:\).
I have also programmed the Target line of each person's terminal server
database shortcut to (including the quotes)
"c:\program files\microsoft office\office11\msaccess.exe"
"i:\CompanyDatabase\CompanyDB.mdb" /wrkgrp "O:\Data\CompanyDataFile.mdb"
(where I:\ is the personal drive on the server).

Every time I open the database FE application, it opens to the switchboard
without a log in prompt, despite the forced direction of the shortcut.
Furthermore, when I double-check that the application is joined to the
appropriate workgroup (via Tools - Security - Workgroup Administrator), it
shows it is connected correctly!

So does the terminal server application have this effect on workgroups? Does
it not function properly because the FE technically is not operating across a
network (just across drives on the server)?

Any ideas are most appreciated.

 

[Joan Wild]

:
: I have another problem, however, from this terminal server environment. I
: am unable to maintain a link to the defined workgroup. While logged in as
: another employee, with the db application open, I can successfully join to
: the CompanyWG.mdw workgroup file (located on the primary database drive O:\).
: I have also programmed the Target line of each person's terminal server
: database shortcut to (including the quotes)
: "c:\program files\microsoft office\office11\msaccess.exe"
: "i:\CompanyDatabase\CompanyDB.mdb" /wrkgrp "O:\Data\CompanyDataFile.mdb"
: (where I:\ is the personal drive on the server).

You are showing the /wrkgrp path as a path to a mdb, and by its name it sounds like the name of the backend. It should be the name of your secure mdw file.

: Every time I open the database FE application, it opens to the switchboard
: without a log in prompt, despite the forced direction of the shortcut.

If you aren't getting a login prompt, then it isn't using your secure mdw. That suggests that it isn't properly secured, as you should only be able to open it while using the correct mdw file.

: Furthermore, when I double-check that the application is joined to the
: appropriate workgroup (via Tools - Security - Workgroup Administrator), it
: shows it is connected correctly!

That dialog does not tell you the current mdw file in use. It just tells you what the default mdw is (that means, the mdw file that is used by default, unless a different one is specified in a shortcut). To determine the mdw file actually in use, you open your mdb, hit Ctrl-G and type
?DBEngine.SystemDB
That will tell you the mdw in use.

: So does the terminal server application have this effect on workgroups? Does
: it not function properly because the FE technically is not operating across a
: network (just across drives on the server)?

Not a factor at all.

 

[Pendragon]

The "mdb" instead of the "mdw" was a typo - my bad! But your comment about
securing the file triggering something - I realized that in creating the
workgroup file I did not create a password on the Admin login (me). That
should do it.

Thanks for your other info. It was educating.

 

[Joan Wild]

Ummm..The user 'Admin' in a secured database should not have *any* permissions, nor own anything at all. This user is common to *every* mdw file. If you grant permissions/ownership to this user, then it won't be secure at all. Anyone with Access will be able to use your database.

You need to study up on security - it isn't as simple as it appears.
Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

Although the whitepaper is old, it contains information to help you understand security.

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm

--
Joan Wild
Microsoft Access MVP
: The "mdb" instead of the "mdw" was a typo - my bad! But your comment about
: securing the file triggering something - I realized that in creating the
: workgroup file I did not create a password on the Admin login (me). That
: should do it.
:
: Thanks for your other info. It was educating.
:
: "Joan Wild" wrote:
:
: > :
: > : I have another problem, however, from this terminal server environment. I
: > : am unable to maintain a link to the defined workgroup. While logged in as
: > : another employee, with the db application open, I can successfully join to
: > : the CompanyWG.mdw workgroup file (located on the primary database drive O:\).
: > : I have also programmed the Target line of each person's terminal server
: > : database shortcut to (including the quotes)
: > : "c:\program files\microsoft office\office11\msaccess.exe"
: > : "i:\CompanyDatabase\CompanyDB.mdb" /wrkgrp "O:\Data\CompanyDataFile.mdb"
: > : (where I:\ is the personal drive on the server).
: >
: > You are showing the /wrkgrp path as a path to a mdb, and by its name it sounds like the name of the backend. It should be the name of your secure mdw file.
: >
: > : Every time I open the database FE application, it opens to the switchboard
: > : without a log in prompt, despite the forced direction of the shortcut.
: >
: > If you aren't getting a login prompt, then it isn't using your secure mdw. That suggests that it isn't properly secured, as you should only be able to open it while using the correct mdw file.
: >
: > : Furthermore, when I double-check that the application is joined to the
: > : appropriate workgroup (via Tools - Security - Workgroup Administrator), it
: > : shows it is connected correctly!
: >
: > That dialog does not tell you the current mdw file in use. It just tells you what the default mdw is (that means, the mdw file that is used by default, unless a different one is specified in a shortcut). To determine the mdw file actually in use, you open your mdb, hit Ctrl-G and type
: > ?DBEngine.SystemDB
: > That will tell you the mdw in use.
: >
: > : So does the terminal server application have this effect on workgroups? Does
: > : it not function properly because the FE technically is not operating across a
: > : network (just across drives on the server)?
: >
: > Not a factor at all.
: >
: > --
: > Joan Wild
: > Microsoft Access MVP
: >