Access Network question

[alex]

I have a split mdb (I will soon convert it to an mde).

Both files (the FE and BE) are on the network server. Each FE (30 of
them) is in the user’s personal folder and the sole BE is in another
folder.

I’ve been reading the posts in this group to find the best way to
rollout my application.

I’m a bit confused about something:

If I have a .doc or .xls file on the server, when the document is
opened, the actual application part (Word or Excel) that reads the
document is solely contained on each user’s PC.

Does Access function the same way? E.g., if I put the FE on the sever
am I dragging the entire application across the network when the FE is
opened or does the actual application on the hard drive do all the
heavy lifting?

I hope this question makes sense. Bottom line I guess: should the FE
reside on the actual hard drive and not the network? Keep in mind
that the reason or reasons would need to be rather compelling because
getting to everyone’s hard drive is going to be a pain!

Thanks,
alex

 

[dhstein]

Alex,

With any software, there is a file that resides somewhere and that file
gets read into the computer's memory during execution. If you have the front
end on the server, then the local PC will read that file into memory when you
start it up. Depending on how much memory the local PC has and what other
applications are running and what operating system you're using, the program
may or may not stay resident in memory. In most cases, I would guess that it
will, so that this read of the file will only occur during the initial
loading and from that point forward the system will just go to the server to
read the tables on the back end. I hope that answers your question.

David

 

[John Spencer (MVP)]

With a FE/BE setup, you should have a separate copy of the front end on each
user's computer. If you try to run one copy of the FE on a server for every
user you will end up with headaches - especially if you have local tables in
the front end.

If you are concerned about keeping the front end up to date (and needing to
re-install the changes on all the computers) Tony Toews' Auto FE Updater

http://www.granite.ab.ca/access/autofe.htm

This utility will automatically update the Microsoft Access front end (FE)
MDB/MDE/ADE/ADP and associated files from a server to client workstation(s) or
other network locations.

This utility is designed to automatically update the front end and
associated files from the server to the client workstation or
directory on the server. The utility will, if required, create the
directory on the workstation, copy files to workstation, create a
shortcut on the desktop and update registry keys. It will then start
the Access MDE/MDB. If none of this is required the next time you run
this utility then all those steps are bypassed and it just starts the
Access MDE/MDB.

--
Tony Toews, Microsoft Access MVP
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[alex]

With a FE/BE setup, you should have a separate copy of the front end on each
user's computer.  If you try to run one copy of the FE on a server for every
user you will end up with headaches - especially if you have local tablesin
the front end.

If you are concerned about keeping the front end up to date (and needing to
re-install the changes on all the computers) Tony Toews' Auto FE Updater

   http://www.granite.ab.ca/access/autofe.htm

This utility will automatically update the Microsoft Access front end (FE)
MDB/MDE/ADE/ADP and associated files from a server to client workstation(s) or
other network locations.

This utility is designed to automatically update the front end and
associated files from the server to the client workstation or
directory on the server. The utility will, if required, create the
directory on the workstation, copy files to workstation, create a
shortcut on the desktop and update registry keys. It will then start
the Access MDE/MDB. If none of this is required the next time you run
this utility then all those steps are bypassed and it just starts the
Access MDE/MDB.

--
Tony Toews, Microsoft Access MVP
  Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www..granite.ab.ca/accsmstr.htm

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County











- Show quoted text -

Thanks David and John for the advice.

John, I don't have any local tables in my FE. What other headaches
would I encounter? I ask because everything seems to be working well.

I've looked at Tony's FE updater. I don't think I have the required
access to our systems to implement this!

alex

 

[Arvin Meyer [MVP]]

Your setup is applicable only to a Terminal Server. On a regular LAN, you
are dragging the entire application over the network and are wasting an
incredible amount of network resources by doing that.

Access works differently than opening a Word document over the network, in
that the Work document is always only editable by the first user that opens
it. Access has a locking file and will let multiple users open the file, but
not edit the same records, or save anything to the same front-end.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a split mdb (I will soon convert it to an mde).

Both files (the FE and BE) are on the network server. Each FE (30 of
them) is in the user’s personal folder and the sole BE is in another
folder.

I’ve been reading the posts in this group to find the best way to
rollout my application.

I’m a bit confused about something:

If I have a .doc or .xls file on the server, when the document is
opened, the actual application part (Word or Excel) that reads the
document is solely contained on each user’s PC.

Does Access function the same way? E.g., if I put the FE on the sever
am I dragging the entire application across the network when the FE is
opened or does the actual application on the hard drive do all the
heavy lifting?

I hope this question makes sense. Bottom line I guess: should the FE
reside on the actual hard drive and not the network? Keep in mind
that the reason or reasons would need to be rather compelling because
getting to everyone’s hard drive is going to be a pain!

Thanks,
alex

 

[alex]

Your setup is applicable only to a Terminal Server. On a regular LAN, you
are dragging the entire application over the network and are wasting an
incredible amount of network resources by doing that.

Access works differently than opening a Word document over the network, in
that the Work document is always only editable by the first user that opens
it. Access has a locking file and will let multiple users open the file, but
not edit the same records, or save anything to the same front-end.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com


I have a split mdb (I will soon convert it to an mde).

Both files (the FE and BE) are on the network server.  Each FE (30 of
them) is in the user’s personal folder and the sole BE is in another
folder.

I’ve been reading the posts in this group to find the best way to
rollout my application.

I’m a bit confused about something:

If I have a .doc or .xls file on the server, when the document is
opened, the actual application part (Word or Excel) that reads the
document is solely contained on each user’s PC.

Does Access function the same way?  E.g., if I put the FE on the sever
am I dragging the entire application across the network when the FE is
opened or does the actual application on the hard drive do all the
heavy lifting?

I hope this question makes sense.  Bottom line I guess:  should the FE
reside on the actual hard drive and not the network?  Keep in mind
that the reason or reasons would need to be rather compelling because
getting to everyone’s hard drive is going to be a pain!

Thanks,
alex

Thanks Arvin. That makes some sense...I don't know what you mean by
Terminal Server.

Also, if I'm understanding you correctly, is that since Access has
record locking the entire application is pulled with the file (from
the network) to the individual's workstation.

 

[dhstein]

Alex,

I'm assuming that you have a run-time version of the front end that you're
executing. In that case, the file is "pulled" to the local PC, that is to
say that the local PC reads the file from the server to the local PC memory.
This occurs whether there is record locking, whether there isn't record
locking or even if today is Arbor Day. In other words, this is how all
programs operate. (As an aside, the comparisons to Microsoft Word are
misleading because everyone runs Word from a local copy on their desktop) So
your file of whatever size, let's assume it's 5 MBytes will get read into the
memory of the local PC and that data will go over the network to get there.
For most networks this is an insignificant amount of time - assume a 10 mbps
network (10 Mega bits per second) this is less than 1 second - but assume
overhead etc. so we're talking about a few seconds. Once it is read into the
local PC memory, it will stay there unless the operating system needs to free
up memory because of other programs running or whatever. In that case the
file would need to be read into memory again. So I see nothing wrong with
keeping one copy of the runtime on the server - in fact you don't even need a
separate copy in each users network folder. The reason is that that is an
executable and is not changing, so the file can be read multiple times by
multiple users and they will all be as happy as the proverbial porcines in
the proverbial you-know-what.

David

 

[dhstein]

Alex,

Bad and hurried math before - 5 MByte file on a 10 mbps network would take
about 4 seconds.

 

[alex]

Alex,

  Bad and hurried math before - 5 MByte file on a 10 mbps network wouldtake
about 4 seconds.  







- Show quoted text -

Thanks David for replying again.

....If I have a 5MB file on a 10MB per second network, wouldn't that
be .5 seconds?

So you're saying you don't see a problem with keeping the FE on the
network (it's about .75MB). I don't see, however, how we could use
only one FE if we were to leave it on the network and all users would
access that FE!

My situation above may not be clear...
I have one BE and 30 FE mdb's filled with the usual forms, reports,
and modules. Each FE is located in a seperate folder (on the network)
accessed by the user. I'm not sure what you mean by run-time
version. I have a master copy that I update (make changes etc) then I
simply copy the master and paste it into each person's folder.

alex

 

[David W. Fenton]

With any software, there is a file that resides somewhere and
that file
gets read into the computer's memory during execution. If you
have the front end on the server, then the local PC will read that
file into memory when you start it up. Depending on how much
memory the local PC has and what other applications are running
and what operating system you're using, the program may or may not
stay resident in memory. In most cases, I would guess that it
will, so that this read of the file will only occur during the
initial loading and from that point forward the system will just
go to the server to read the tables on the back end.

An Access front end, like a Jet back end, is a Jet MDB, and is never
completely read into memory. Only the needed data pages are
retrieved. To open a front end, the system tables will be read, and
enough of the Access project read into memory to present requested
UI objects to the end user. I don't know if Jet is smart enough to
request only parts of the BLOB field that stores the Access project
or if it retrieves the whole thing. Pre-Access 2000, the each UI
object was stored as a single record in the system tables, which
meant that it could be loaded incrementally.

My point is that it may be that there are multiple trips back to the
file when you requests objects that haven't been loaded from the
BLOB field yet. If that's the case, storing the thing on a file
server could result in noticeable lag time on loading new objects.

It's just silly to put your users' copies of the front end on the
server, unless the whole thing is running on a Terminal
Server/Citrix Server.

 

[Arvin Meyer [MVP]]

Thanks Arvin. That makes some sense...I don't know what you mean by
Terminal Server.

Also, if I'm understanding you correctly, is that since Access has
record locking the entire application is pulled with the file (from
the network) to the individual's workstation.
------------------------------
If you don't know what a Terminal Server is, you probably don't need to.

If you have the front-end on a server, yes, the entire front-end is pulled
from the server for each user. That's unnecessary. If connected by have the
front-end on each user's workstation, only relevant data is pulled from the
server.

 

[Arvin Meyer [MVP]]

....If I have a 5MB file on a 10MB per second network, wouldn't that
be .5 seconds?

So you're saying you don't see a problem with keeping the FE on the
network (it's about .75MB). I don't see, however, how we could use
only one FE if we were to leave it on the network and all users would
access that FE!

My situation above may not be clear...
I have one BE and 30 FE mdb's filled with the usual forms, reports,
and modules. Each FE is located in a seperate folder (on the network)
accessed by the user. I'm not sure what you mean by run-time
version. I have a master copy that I update (make changes etc) then I
simply copy the master and paste it into each person's folder.

Network speed is measured in megabits, not megabytes. There are 8 bits to a
byte. More than likely though, your network is 100 megabits per second so
you're correct. The speed isn't as important as the network traffic though,
because as more and more users load bigger and bigger files, the network
slows down considerably. That's because of the load, and because of network
packet collisions which require resending TCP/IP packets.

All that is unimportant. What may be important is that the larger the file
dragged across the network, the greater the chance for corruption. Just
trust that it is safer and faster to put the front-end on the local
workstations.

 

[David W. Fenton]

If you have the front-end on a server, yes, the entire front-end
is pulled from the server for each user.

Are you sure of that? Since an MDB front end is a Jet database,
won't it retrieve only as much as necessary? Of course, that's all
complicated by the fact that the Access project is stored as a
single BLOB field, but surely that has been optimized in order to
not require pulling the whole BLOB at once?

 

[Arvin Meyer [MVP]]

Are you sure of that? Since an MDB front end is a Jet database,
won't it retrieve only as much as necessary? Of course, that's all
complicated by the fact that the Access project is stored as a
single BLOB field, but surely that has been optimized in order to
not require pulling the whole BLOB at once?

I believe you are correct that it is pulled as needed, but then I try not to
have things that aren't needed in the database, so most if not all is pulled
from the server. Except with very simple applications, I tend to write
modules (Sales module, purchasing module, etc.), all of which connect to the
same data. If for instance, you need both sales and purchasing information,
you open them both. This has a benefit, of keeping users focused on their
tasks, as well as smaller front-ends.

 

[Tony Toews [MVP]]

I've looked at Tony's FE updater. I don't think I have the required
access to our systems to implement this!

You won't need any required access to the systems. The executable
file is just a file on the server that the user executes. It doesn't
need any special permissions. Indeed companies such as Boeing,
Mercedes and the TSA are using the utility.

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/

 

[Tony Toews [MVP]]

I'm assuming that you have a run-time version of the front end that you're
executing. In that case, the file is "pulled" to the local PC, that is to
say that the local PC reads the file from the server to the local PC memory.
This occurs whether there is record locking, whether there isn't record
locking or even if today is Arbor Day. In other words, this is how all
programs operate. (As an aside, the comparisons to Microsoft Word are
misleading because everyone runs Word from a local copy on their desktop) So
your file of whatever size, let's assume it's 5 MBytes will get read into the
memory of the local PC and that data will go over the network to get there.
For most networks this is an insignificant amount of time - assume a 10 mbps
network (10 Mega bits per second) this is less than 1 second - but assume
overhead etc. so we're talking about a few seconds. Once it is read into the
local PC memory, it will stay there unless the operating system needs to free
up memory because of other programs running or whatever. In that case the
file would need to be read into memory again. So I see nothing wrong with
keeping one copy of the runtime on the server - in fact you don't even need a
separate copy in each users network folder. The reason is that that is an
executable and is not changing, so the file can be read multiple times by
multiple users and they will all be as happy as the proverbial porcines in
the proverbial you-know-what.

David

When it comes to standard exe files your explanation is likely
correct.

However when it comes to Access MDB/MDE/etc you are wrong.

1) Sharing the FE on a server leads to greatly increased chances of
corruptions of the FE. Access is frequently making minor updates such
as query plans on the FE MDB/MDE.

In addition it is very difficult to load updates of the FE on the
server.

2) Access does not, to my knowledge, copy the entire FE MDB/MDE into
the RAM of the computer when Access is started up and loads the FE
MDB/MDE. I strongly suspect Access loads the objects such as
queries, forms and reports as it needs them.

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/

 

[Tony Toews [MVP]]

Are you sure of that? Since an MDB front end is a Jet database,
won't it retrieve only as much as necessary?

When it comes to queries, forms, reports and macros I strongly suspect
Access pulls it only as much as it needs. Hmm, one way to test would
be to put a graphic on the background of a form, run the MDB, review
memory usage, load the form and then review memory usage again.

Of course, that's all
complicated by the fact that the Access project is stored as a
single BLOB field, but surely that has been optimized in order to
not require pulling the whole BLOB at once?

For some of my clients MDBs I've noticed that they take up 40 Mb of
RAM on startup. With only loading two forms. So I suspect Access is
loading all the VBA code into RAM on startup.

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/

 

[David W. Fenton]

2) Access does not, to my knowledge, copy the entire FE MDB/MDE
into the RAM of the computer when Access is started up and loads
the FE MDB/MDE. I strongly suspect Access loads the objects such
as queries, forms and reports as it needs them.

This assumes that Access has smart logic for pulling data out of the
BLOB field where all of those (except for queries) are stored. I
don't know that there is such logic, but it's certainly possible.

 

[David W. Fenton]

For some of my clients MDBs I've noticed that they take up 40 Mb
of RAM on startup. With only loading two forms. So I suspect
Access is loading all the VBA code into RAM on startup.

Then that would tend to suggest that the whole Access project is
being loaded, seems to me.

 

[Tony Toews [MVP]]

This assumes that Access has smart logic for pulling data out of the
BLOB field where all of those (except for queries) are stored. I
don't know that there is such logic, but it's certainly possible.

AFAIK it's the code that is a blob. The objects themselves, tables,
queries, forms, reports and macros are individual objects.

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/