Question about db deployment

  • Thread starter Thread starter Preston
  • Start date Start date
P

Preston

Hi,
I would like to thank you for the help you've given me. I'm almost ready
to push out the db to our employees. One question that i have remaining. I
have decided to split the db in two. The tables in one and the logic in
another. How should i go about distributing the front end. Should i have a
copy on each pc that it is going to used on? Should i have one copy on the
server that everyone uses? What is the difference between these two methods?
Thank you for your help,
Preston
 
Preston said:
Hi,
I would like to thank you for the help you've given me. I'm almost ready
to push out the db to our employees. One question that i have remaining. I
have decided to split the db in two. The tables in one and the logic in
another. How should i go about distributing the front end. Should i have a
copy on each pc that it is going to used on? Should i have one copy on the
server that everyone uses? What is the difference between these two methods?
Click to expand...


Since Access saves a fair amount of information in the
current database, it would be a potential disaster to share
the front end from a server.

Put a copy of the front end on each user's machine. I
recommend putting a copy on the server first and then
copying it from the server to each user's machine (I go so
far as putting code in the startup procedure to check if
it's running on the server and quitting if it is).

The difference is that one method can easily be corrupted
vs. the other requiring you to copy the file several times.

There are ways to automate the copy process using a program
like: http://www.granite.ab.ca/access/autofe.htm that can be
very helpful when you have relatively frequent modifications
to distribute.
 
OK thank you for the reply. Can you explain a little more how access handles
the multiuser scenario in either case? i know that access just executes
queries and such over the network when each machine has its own front end. I
am assuming that access maintains some type of data cache on the local
machine that it updates to the backend when necessary. How does the software
handle multiple users when they all connect through the same front end file.
This is the situation where you say there is a higher chance for corruption
right?

thank you again,
Preston

Since Access saves a fair amount of information in the
current database, it would be a potential disaster to share
the front end from a server.

Put a copy of the front end on each user's machine. I
recommend putting a copy on the server first and then
copying it from the server to each user's machine (I go so
far as putting code in the startup procedure to check if
it's running on the server and quitting if it is).

The difference is that one method can easily be corrupted
vs. the other requiring you to copy the file several times.

There are ways to automate the copy process using a program
like: http://www.granite.ab.ca/access/autofe.htm that can be
very helpful when you have relatively frequent modifications
to distribute.
Click to expand...

Ok, thank you for the reply. I will run a copy of the front end on each
desktop
 
The data locking file (.LDB) is in the same folder as the
mdb file (one for the front end and one for each back end).
So the difference in sharing the front end or not is minimal
in this regard as long as you never modify an object
(query/form/report/...) on the fly. Modifying objects on
the fly is a really bad thing in either case, but can
completely mess over a shared FE.

OTOH, as I said before, Access does save housekeeping
information to the file so a shared FE may suffer odd
collisions, which then opens the door for unexplainable
corruptions and your entire shop will be down until you can
restore a good copy of the FE mdb to the server. This is
not something you can guard against, but you can easily do
simple things in the FE's design/code to make it much worse
(casual use of temp tables being an obvious example).

I have only heard of a couple of people that have managed to
reliably share a FE from a server to multple users.
However, I have heard of lots and lots of prople that are
sorry they ever entertained the thought of sharing a FE.

Locking for the data in back end tables is not an issue in
either scenario. Access performs all the appropriate checks
for multiple user's working on data in the same table and
will warn before (pessimistic) or after (optimistic) two
users edit the same record (actually a 2K block of records
for pesimistic locking).

Data is cached and flushed as needed on the machine that's
running the app, so that is only a performance improvement
technigue. The most important thing you can do to minimize
network traffic and improve performance is to use table
indexes appropriately.
 
Thank you very much for your explanation. I like to try and understand what
the software is actually doing so that I can setup and design things
properly. Thank you for taking the time to explain this to me. If you know of
anywhere where I can look at a logical layer diagram of access and a physical
layer as well I would greatly appreciate it.
Ty
Preston

Marshall Barton said:
The data locking file (.LDB) is in the same folder as the
mdb file (one for the front end and one for each back end).
So the difference in sharing the front end or not is minimal
in this regard as long as you never modify an object
(query/form/report/...) on the fly. Modifying objects on
the fly is a really bad thing in either case, but can
completely mess over a shared FE.

OTOH, as I said before, Access does save housekeeping
information to the file so a shared FE may suffer odd
collisions, which then opens the door for unexplainable
corruptions and your entire shop will be down until you can
restore a good copy of the FE mdb to the server. This is
not something you can guard against, but you can easily do
simple things in the FE's design/code to make it much worse
(casual use of temp tables being an obvious example).

I have only heard of a couple of people that have managed to
reliably share a FE from a server to multple users.
However, I have heard of lots and lots of prople that are
sorry they ever entertained the thought of sharing a FE.

Locking for the data in back end tables is not an issue in
either scenario. Access performs all the appropriate checks
for multiple user's working on data in the same table and
will warn before (pessimistic) or after (optimistic) two
users edit the same record (actually a 2K block of records
for pesimistic locking).

Data is cached and flushed as needed on the machine that's
running the app, so that is only a performance improvement
technigue. The most important thing you can do to minimize
network traffic and improve performance is to use table
indexes appropriately.
--
Marsh
MVP [MS Access]

OK thank you for the reply. Can you explain a little more how access handles
the multiuser scenario in either case? i know that access just executes
queries and such over the network when each machine has its own front end. I
am assuming that access maintains some type of data cache on the local
machine that it updates to the backend when necessary. How does the software
handle multiple users when they all connect through the same front end file.
This is the situation where you say there is a higher chance for corruption
right?



Ok, thank you for the reply. I will run a copy of the front end on each
desktop
Click to expand...
Click to expand...
 
If such documents exist, I have never see them. I just
pieced it together from the Help files, KB articles, a few
books and these newsqroups.
 
Marshall,

Would you care to share the code for checking if the application is running on
the server? I guess I could come up with a solution, but I am lazy and if you
already have the code and can share it I will save myself the effort of
devising and testing the code.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John said:
Would you care to share the code for checking if the application is running on
the server? I guess I could come up with a solution, but I am lazy and if you
already have the code and can share it I will save myself the effort of
devising and testing the code.
Click to expand...


Kind of simple minded, John, but here it is:

Public Function IsServer()
Dim strConnect As String
Dim strFEpath As String, strBEpath As String

strFEpath = Left(CurrentDb.Name, InStrRev(strConnect,
"\"))
strConnect = CurrentDb.TableDefs!anylinkedtable.Connect
strBEpath = Mid(Left(strConnect, InStrRev(strConnect,
"\")), InStr(strConnect, ";Database=") + 10)

If strFEpath = strBEpath Then
MsgBox "Can not run from the server.", vbCritical, _
"Invalid Configuration"
Quit
End If

End Function
 
Back
Top