Should my design be changed??

[buzz]

I am writing this because I had a different post in another newsgroup and
they recommended that I change my design. The way I have it set up is I've
got a Access 2000 database that users use that is on a shared location; they
all access this master database through a shortcut to a macro that just opens
up a form for the users. I wanted this all in one location so if I had to
change or add a report, it was done in one location. The tables (or a.k.a.
the back end) are linked tables to a DB2 database where the data is really
stored; so really, all the users are sharing a front end database. The
question I had is if there is a way to change the shortcut to only open this
database in a readonly format?? So if I wanted to change a report while
someone was using it, I could get the exclusive rights to do the change. As
it is today, I have to hunt down the user that opened the database first to
create the .ldb file to get them out so I can make the changes needed.
Please advise with any recommendations.

 

[Roger Carlson]

You can leave a copy on the network that is shared by all users. (The usual
advice is to have the FE on each workstation, but it doesn't have to be.)
However, that should not be the "Master" copy. You should have the master
copy elsewhere (I keep them on my hard-drive with periodic backups to a
network location). You make your modifications in the master copy. Then
you just copy your master copy over the shared network copy.

You still have to get everyone out of the database, but only for the few
seconds it takes to copy the file. On my website
(www.rogersaccesslibrary.com), there is a small Access sample database which
shows how to remotely kick all users off the application and keep them from
logging in until you let them. The sample is called "LogUsersOff.mdb"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Kevin3NF]

Yes, you need to move the FE to each user's desktop.

Use Tony Toews AutoFE updater to get around the issue you mention:
http://www.granite.ab.ca/access/autofe.htm

You make a change, and the next time each user opens it, they automatically
get a new copy of the FE.

Works really well

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions

 

[Roger Carlson]

Kevin,

I have to disagree. While having the FE on each desktop is recommended,
there are times when it is not possible.

For instance, in my own case, the Gods of Network Security decreed that the
users' systems would be "locked-down", that is, users would have no rights
to their own harddrives. In this case, I cannot have the FE on the
workstation.

I've used two solutions.
1) If there are not too many simultaneous users, I left a common copy of the
FE for all users. While I have read dire warnings about this, in ten years
of doing it, I've never had a problem.
2) For higher traffic systems, I've created separate directories on the
network for each user and given them a separate FE. Then I can "push" a new
version to everyone or use code to allow them to "pull" a new copy
(something like the AutoFE Updater).

I'm not disagreeing that the FE on each workstation is best. I just
disagree that he must do it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Duane Hookom]

I think I might have been the "replier" from the posting in the other news
group. Thanks Roger and Kevin for re-inforcing my suggestion.

 

[Roger Carlson]

Always glad to back you up, Duane.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[buzz]

I understand everyone's comments but I don't think you're quite understanding
on how this is set up. This access database uses mostly linked tables so the
actual backend is already on another system; I just have the users sharing a
front end system. This database is backed up nightly in it's location and I
tend to my own copy so there are other master copies. I don't understand why
I continue to be told that I should distribute this front end to all the
users; if that's the case, I'd just be sending everyone copies of what
they're already accessing now to be stored on their hard drive. But what is
the problem with having a shared front end; it's one location and one update
when it needs to be done. What potential problems could I run into that I
haven't for the last year that this has been used in this format?? The users
I deal with that use this database are pretty ignorant when it comes to
anything like this and I am not able to just distribute it for them; I would
have to manually apply the change to a PC and a shared location just seemed
the best way to go.

I want to thank Roger for the recommendation and I will look into what he
has for logging the users off. I still want to thank everyone for the
concern but I'd like to know what the concern is that I should be worried
about. The whole distributed front end would seem to be more of a
recommendation than a must do.

 

[Duane Hookom]

I sometimes create or use temporary tables for more efficient reporting. If
these are created in a shared front-end, they will potentially clobber each
other. I also will use code to modify the SQL property of a saved query.
Again this will cause issues in a shared MDB.

Loading an MDB from a local drive is much faster than from a network drive.
Also, if one person corrupts the front-end, all users are locked out.

I have one application at work where the shortcut on the start menu copies a
new front-end to a user's C: drive and then opens. The time required to copy
and open is very minor. All I have to do us update the front-end on the
network as reports are added or whatever.

 

[tina]

that sounds a very handy approach to FE distribution, Duane! i'd love to
learn how to do it. i've never modified a shortcut to do a copy/paste (i
just create file shortcuts by clicking the option on a "shortcut" menu.
<g>). could you share the trick, or direct me where i might read up on how
to set it up? thx, tina

 

[Duane Hookom]

Here is the script/vbs file that I created.

'===================
'
' File: UpdateIngredients.vbs
'
' ORIGINAL AUTHOR: Duane
' Date : 07/14/2005
'
' COMMENT: Create a subdirectory on the users' H: drive and copy
' the latest version of the Ingredients front end
'===================

Dim fso 'to be used for file related code
Dim f
Dim Wsh
SET wsh=WScript.CreateObject("Wscript.Shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
If not FSO.FolderExists("H:\Ingredients") Then
Set f = fso.CreateFolder("H:\Ingredients")
End If
'delete the old file if it exists
If FSO.FileExists("H:\Ingredients\Ingredients.mdb") then
FSO.DeleteFile("H:\Ingredients\Ingredients.mdb")
End If
'copy the new version files to the user's H drive
FSO.CopyFile "N:\DEPTS\QA\Formulas\Ingredients.mdb", "H:\Ingredients\"
wsh.RUN "H:\Ingredients\Ingredients.mdb",3
wscript.Quit

 

[tina]

hmm, okay. <flounders around, spluttering, wondering why the deep end of
the lake looked so enticing from shore>
would you mind holding my hand a little further, or directing me to basic
educational resources? i don't know where to save the code you posted - into
a file named "UpdateIngredients.vbs", but how? is that just a .txt file with
a different file extension? and i don't know how to call the file from the
shortcut.

if i'm needing more detail than you want to go into, no problem. i'm
obviously out of my depth, and this really isn't the forum to teach me
something new from the ground up. but thanks for taking the time to try and
help me out, i really appreciate it! <paddles back to shore and crawls out,
vowing to take swimming lessons first, next time>

 

[Duane Hookom]

This is kinda like a batch file but is vbscript. As you can see, it is
similar to vba but uses some Windows dlls. To test this, you can change some
file a directory names to reference NotePad.exe or another program. When you
feel comfortable with what the batch file does, substitute the network
location of your mdb and where you want to copy it to on the user's drive or
share.

At our plant, a copy of the master mdb is located at
N:\DEPTS\QA\Formulas\Ingredients.mdb. This vbs script will make sure there
is a folder in the user's share on the network and then copy the file there.
The script then opens the file.

Make sure that no "permanent" information or objects are stored in the front
end since they get overwritten every time the user opens the application.

 

[tina]

thanks, Duane!

This is kinda like a batch file but is vbscript. As you can see, it is
similar to vba but uses some Windows dlls. To test this, you can change some
file a directory names to reference NotePad.exe or another program. When you
feel comfortable with what the batch file does, substitute the network
location of your mdb and where you want to copy it to on the user's drive or
share.

At our plant, a copy of the master mdb is located at
N:\DEPTS\QA\Formulas\Ingredients.mdb. This vbs script will make sure there
is a folder in the user's share on the network and then copy the file there.
The script then opens the file.

Make sure that no "permanent" information or objects are stored in the front
end since they get overwritten every time the user opens the application.