Splitting Database - multiuser

[Mcrawford]

I have split my database. I have four users that will be using the front end.
I've read that each user should have the front end on their local systems.
What are the disadvantages of having the front end on a shared network drive
so that all four users can access a single front end (instead of having it on
each pc).

Also, when I split the database the original name was TSCL.mdb. It split
into TSCL_be.mdb (back end) and TSCL.mdb (front end). I've seen some postings
about the front ending being a .mde file...why is mine a mdb file? Do I need
to do some sort of conversion to make it an mde file, if so, why?

I'll continue to search for reasons for using mde file, but thought I would
see what you all have to say.

Thanks much!

 

[Daniel]

Speed of execution!

Think about it for a second. Using 1 copy on the network, each time the
user make a request the request has to be relayed through the network to the
server... back and forth we go..... On the other hand, if you use a local
copy for each user, the request is made by the local machine to the local
machine, no net work required, it only uses the network to pull data from the
back-end as required. This had a major impact at my previous place of
employment!

It is bad practice to have multiple users using a single db. You really
need to give each user their own copy! I simply created a batch file on the
server that copied a central front-end (stored on the network so I could
update it easily) to the local desktop and then lauched it. It is
transparent to the user but simplifies my life as a developer.

 

[Allen Browne]

The important thing is that each user opens a separate MDB/E file. Ideally
it's on their local hard disk, since that's faster than the network and
reduces the network traffic, but you can use separate files on the server
(e.g. in the user's workspace) if necessary.

You can create an MDE using Access:
Tools | Database Utilities | Make MDE

The user cannot mess up your forms, reports, or code if you give them an
MDE. An MDB is fine if you trust them not to fiddle, if you want them to be
able to create/modify their own forms/reports/code and change yours, and if
they all use the same version of Access (to avoid the faulty binary problems
that occur when switching versions.)

 

[Mcrawford]

Thanks for the explaination. I will definately give each their own local file.

 

[Mcrawford]

Allen, If I choose to use MDE files what is the best practice for updating
when I make changes?

I'm assuming I make changes to a "master" MDB front end file and then
re-make MDE files each time I have an update, is this correct?

 

[Mcrawford]

Daniel, I think the batch file sounds like a good idea. Can you post an
example of what yours looks like?

I'm by no means a developer/programmer...yet. I'm just getting my feet wet.

Thanks!

 

[Allen Browne]

Yes, that's right.

In a small setting, you just copy the new MDE to each workstation.

For an automated process, see Tony Toews solution:
http://www.granite.ab.ca/access/autofe.htm

 

[Keith Wilby]

Daniel, I think the batch file sounds like a good idea. Can you post an
example of what yours looks like?

PMFJI ...

ECHO OFF

DEL "%temp%\DSE\*.*" /Q
RD "%temp%\DSE"
MKDIR "%temp%\DSE"

DEL "%userprofile%\desktop\DSE.lnk"
XCOPY "X:\MyPath\DSE.lnk" "%userprofile%\desktop"

XCOPY "X:\MyPath\DSEGUI.mde" "%temp%\DSE" /I
START "DSE" /MAX "C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE"
"%temp%\DSE\DSEGUI.mde" /wrkgrp "X:\MyPath\DSE.mdw"

This code copies a shortcut (to the batch file) to the user's desktop and an
mde file to the user's profile, then it opens the mde. The copied shortcut
then runs the batch file for subsequent use.

HTH - Keith.
www.keithwilby.com

 

[Keith Wilby]

Allen, If I choose to use MDE files what is the best practice for updating
when I make changes?

I'm assuming I make changes to a "master" MDB front end file and then
re-make MDE files each time I have an update, is this correct?

Yep, that's about it.