Split Database Question

  • Thread starter Thread starter Dave Ruhl
  • Start date Start date
D

Dave Ruhl

Our front-end is an Access 2002 MDE, with the back-end
data in SQL Server. The MDE is stored on a file server,
and each user has an icon on their Desktop for this MDE.

My question is, I see many people here reccommend putting
a copy of the front-end on each users PC. Why is that ?

We seem to be doing fine with it on the file server. The
only problem (minor) that I see to our method is that we
have to get everyone out of the database to move in a new
version. However, that does ensure that everyone is
using the latest version.

With the constant enhancements that I'm making to our
front-end, it would be a nightmare to distribute it to
every PC, 2-3 times a week.

Is there a problem with the server method that I should
be aware of ? Thanks...
 
Access is unique in that it allows multiple users to work in the file at the
same time. Access 97 and earlier even allowed multiple users to edit the
same file. This can sometimes lead to corruption. For this reason, it is
wise to use separate files. Even when using terminal services, it is good
practice to put a separate copy of the front-end in each users folder.

I have a startup sample database that auto downloads new versions from a
copy on the server:

http://www.datastrat.com/Download/Starter.zip

Tony Toews, another Access MVP, also has a versioning program at his
website:

http://www.granite.ab.ca/access/autofe.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks for the response Arvin. However, with my data in
SQL Server, I still don't see how multiple people using
the MDE would cause a problem ? Maybe the frequency of
new versions has prevented any corruption to the MDE
front-end ???

(P.S. FoxPro also allows muliple users. I worked with
it for many years before my new job caused me to switch
to Access. I always used a .EXE front-end (on a file
server) with SQL Server backend. The EXE is not
corruptable (excluding a virus), so that's why I thought
I should use this method with Access.)
 
The problem is that often some tings like a filter on a form etc can be
saved..and effect everyone. Any kind of code that creates a temp table...can
be stepped on top by another user that does the same thing.

Further, if a user hits ctrl-alt delete..or does something on his pc to
damage the mde front end (or leaves it in a weird state)..then ONLY that one
user suffers a problem...and not the whole she-bang comes crumbling down by
sharing the front end.

As a general rule...you will find this setup un-reliable. If your case is
different..then fine..but it is not a good idea.

In addition, for the last 10 years, with rare exceptions (your old FoxPro
was an example), you have ALWAYS installed the software on each pc. You
always install word on each pc. You always install excel on each pc. You are
certainly free to share some Excel, or word files on the server..but I bet
you cannot find ONE ARTICLE on the net that suggests that word, or excel
should also installed and run from the server? So, sure..share some
documents..or data on a server..but you never share the applications.

In other virtually every other piece of software you have is installed on
EACH PC. Now, all of a sudden you are suggesting that software that YOU
CREATE with VB, c++ or ms-access should break this rule. Why all of a sudden
break precedent with this ONE piece of software..while all other software
you are using is installed on each pc? The fact that you buy software, or
create it is not a valid reason. The valid reasons are concurrency problems,
increased network bandwidth. (you don't want to download word every time you
use it...nor should you download your application across the network each
time also...).

So, sharing the FE will drop your reliability. In many cases...you can get
away doing what you are..but it is not recommend.

With the link to Tony's auto front end updater...any issues of "updating" or
keeping your software up to date is not an argument anymore.

So, just keep the above in mind. If your setup runs ok..then fine..but if
you start to see some problems..then at least you know you can move the FE
to each pc...

Also...show me ONE support person that suggests that software should NOT be
installed on each pc.....
 
Dave,

EXEs *are* corruptable. All files are. One recommended way reducing
corruption on Access files is to use separate front ends. Just because the
MDE file is compiled does not mean it doesn't change. All the select
statements are decompiled every time the file is compacted. The same is true
of SQL-Server views. They are compiled and cahed the first time they are
run. Access queries are the same.

If you don't think that the Access file changes with each user, try this
experiment:

1. Compact the database and check its file size..
2. Open it with one user and run several forms, reports queries, etc.
3. Close it and check its file size.
4. Open it with a different user and repeat the exact same forms, reports
queries, etc.
5. Close it and check its file size. (Different again, isn't it?)
6. Have multiple users open the same forms, reports queries, etc.
7. Close it and check its file size. (Different yet again, isn't it?)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Access is unique in that it allows multiple users to work in the file

Excel also.

(david)
 
a copy of the front-end on each users PC. Why is that ?

Win2K had some bad networking bugs that only affected
Access and similar 'shared file' systems. MS has
conspicuously failed to guarantee subsequent performance.

Or to put it a another way: there is no competition in
the File Server area any more, and systems that use the
DOS 3.x database API are not strategic.

(david)
 
Thanks to all of you for your explanations. I suspected
there was a reason for the general concensus on the
subject, I just didn't know why. You have all been using
Access far longer than I have, so I trust your
judgement. Thanks again.
 
david epsom dot com dot au said:
Tools,Share Workbook, "Allow changes by more than one user ...."
Click to expand...

Not the same thing David. This is similar to a replication synchronization
where 1 user decides conflicts. The Access model, can dynamically lock and
unlock records based on use, not user. Or the last save wins, forgetting any
previous use.

But even that is not what we were talking about. Splitting the database
allows separate front-ends which can individually have design work done
(queries, forms, reports, code, etc.) without affecting any other user's
copy. Two users cannot work on the same queries, forms, reports, etc. in the
same copy, even with a separate instance.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top