ADP with multiple users

[Steve]

I have setup an ADP linked to SQL on the server. Here are a few simple
questions that I hope are no too basic for this newsgroup...

1) Each user must be running their own version of the ADP since it is the
front end. Hence, if 2 people try to open the same ADP file, one will get a
"read only" message. Is this correct?
2) When modifying the primary copy of the ADP, it is easy enough to
distribute it by replacing all of the existing versions that each individual
user is running. However, if a user is CURRENTLY in the ADP, then it cannot
be overwritten or changed. We would have to wait for the user to manually
CLOSE the ADP in order for the updates to be posted to their version of the
ADP. Is this correct?

Assuming #1 and #2 are correct, is there a way of forcing the close of an
ADP that a user is running? With eh MDB, there are ways of setting up
"application closing, please logout" messages to each user that is logged
in, however, with an ADP, each user is the ONLY user using that version.

There is not a .LDB file created so that we can see which ADP files are
open. The only way I can know if someone has their version open if by
walking around to each computer and seeing if it is open.

Is there another option? Forcing a disconnect of all active connections to
the SQL server (from Enterprise Manager) allows me to disconnect them, but
they still have the ADP open.

Help?

 

[Robert Morley]

1) This is "correct enough". In spite of the error message, multiple users
can generally use the same ADP at the same time, unless you're doing
something REALLY funky with it (i.e., modifying forms & reports on-the-fly,
that sort of thing).

2) This is also correct. For a small-scale distribution, you can probably
just call them/e-mail them and tell them there's a new version available.
For larger implementations, my solution for this has been to set up a VB
program that checks for a new version of the file on a network share and
copies it, then launches it. Have the user launch that program instead of
the ADP itself, and it will check for a new update every time they run your
application. You can also implement a simpler version of this by using a
dedicated update program in the user's startup folder, but this has the
drawback of only occurring when they log in.

3) The problem with your suggestion of forcing an ADP closed is that any
conventional way of doing it would abort the user's changes. Two methods
come to mind here, granting admin rights (with which you can force a process
closed) on each machine, or using SQL Server's option to lock everyone out
of your back-end database. You can reasonably assume that most users will
log out shortly after that.

A better method might be the one I mentioned in question 2, or simply create
a table with messages in it that your ADP checks at regular intervals (every
minute? every 5? whatever works best for your database load) via a hidden
form and when it sees a message, it pops it up to the user (presumably
asking them to log out). A more complex method would be to check logins and
go from there, but that's probably getting unnecessarily complex for what
you're trying to achieve.



Rob

 

[Mark Shultz Jr]

Steve,

1) ADP Files were not designed to be shared. I have read somewhere that
multiple people opening one ADP file can cause it to become corrupted and
have actually experienced that issue here myself, usually with an ADP file
being created in MS Access 2003 and opened with MS Access 2002(XP). I am
using Access 2002/2003 file format, so that may have something to do with it
as well.

2) As far as keeping your ADP up to date for your users, I have my users
execute a DOS Batch file. I'm attaching the code from it below. Paste the
bat file text (minus the *** at the beginning and the end) into a .bat file.
Create a folder on the server to share the installation files. You run this
file the first directly from the share on the server. It will check for the
existence of the application directory on the workstation's hard drive. If
it's not there, it will copy it. You run the bat file the second time from
the workstation's hard drive and it will copy the short cut from the
database's folder to the user's desktop.

When ever the user runs your app, they should ALWAYS use the batch file as
it will check for a newer version of the ADP file on the server. If a newer
version exists, it will copy it to the workstation and then launch it. If
the file on the server is older or the same age, it will launch what it
already has on it's hard drive. I had some functionality in my database that
only works with MS Access 2003, so this batch sequence also checks for MS
Access 2003 and will not launch the database project with out it.

Let me know if you have questions.

This code is provided without warranty!

Good Luck and welcome to the world of ADP/SQL Server!

Mark Shultz

***Begin Bat File***
@Echo Off

If not exist C:\DbDir\DB.adp goto CopyNew
If exist C:\DbDir\DB goto Update

:CopyNew
Echo Checking for Installation of the DbProject Application
Rem Check for local file - create directory and copy file if not present.
if not exist C:\DbDir\DB mkdir C:\DbDir
xcopy "\\Server\ShareName\DbDir\*.*" C:\DbDir\ /d /y
COPY DbShortCut.lnk "%userprofile%"\Desktop
Goto Launch


:Update
Echo Checking for Updates to DbProject Application / Updating....please
wait...
Rem updates local copy with server file if needed
xcopy "\\Server\ShareName\DbDir\*.*" C:\DbDir\ /d /y



:Launch
Echo Update Complete, Starting the DbProject System...
Rem Launch application
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" C:\DbDir\DB.adp
/runtime /cmd "Bat"
Exit

:WrongVer

cls

Echo ************************************************************
Echo ************************************************************
Echo ************************************************************
Echo **** ****
Echo **** MS Access 2003 Required! ****
Echo **** ****
Echo **** Please contact Tech Support ****
Echo **** ****
Echo ************************************************************
Echo ************************************************************
Echo ************************************************************


***END Bat File***

 

[Robert Morley]

Hey Mark,

Thanks for the update to what I said. I've only ever run ADP's on a
one-per-user basis, with the occasional second instance being accidentally
opened and usually closed again right away, so wasn't aware of this issue.
I also don't have Access 2003, so if it's a specific issue with that, then
I'd have even less reason to know.



Rob