[dba-SQLServer] Who Has adp/ade open?

David Emerson newsgrps at dalyn.co.nz
Mon Nov 28 18:55:51 CST 2005


Interesting.  Terminal Server must open separate instances of the 
programme because there is no problems with several users all having 
the programme open at the same time and updating data.  There also 
doesn't seem to be any restrictions as to the number of times the 
programme is opened in the same session!!

Thanks for the ideas for restricting the number of times they can 
open the programme.  I hope it doesn't get to that but it may have too.

David

At 29/11/2005, you wrote:
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ac2003_ta/html/odc_acmultibest.asp
>
>Opening an Access Project
>If you try to open an Access project (.adp) file or an Access project
>extension (.ade) file in a multi-user environment, you may receive an
>error message similar to the following:
>"The database DatabaseName will be opened read-only because one of the
>following occurred: The file is locked for editing by another user, or
>the file (or the folder in which it is located) is marked as read-only,
>or you specified that you wanted to open this file read-only."
>This error message occurs because an Access project is strictly a client
>and has no multi-user capability. The workaround for this issue is to
>deploy a copy of the .adp file or .ade file to each computer.
>
>So something different must be happening under terminal services
>
>I assume each user is running a copy of the adp under a seperate session.
>and then opening more in the same session.
>
>What you could do in code from an autoexec macro
>is check if a .txt file exists with anything in it.
>If it doesn't exist continue on using the existance as a switch.
>create txt file
>and deleting on close of the adp
>
>Or maybe better create a table entry in sql server with the user name on
>logon
>deleteing the name on logoff.
>and check beforehand if that user name pre-exists then dump em out with
>application.quit
>and a friendly warning.
>This might be a problem if there is a line drop. Then  there would
>have to be intervention to remove the user name from the table.
>
>Another way might be to run a vbs script to startup the adp terminal session
>and check if the adp file for that session is already open.
>
>or this used to work with mdb's from the myst's of time not sure of adp's
>
>'Usage:
>  '   debug.Print IsItRunning("MSAccess", "System")
>' to check Access itself
>
>     'Print IsItRunning("MSAccess", CurrentDb().Name)
>' to check the specific application
>
>   '  Print IsItRunning("MSAccess", "System.mda")
>' to check that specific security file
>
>Public Function IsItRunning(strApp As String, strTopic As String) As
>Boolean
>    'Doesn't allow multiple copies of Access program running at the same
>time
>    'Implement  to stop users from starting 2 copies of program
>    ' Access version dependant, use Long for Access 97; int for Access 2
>     Dim Channel As Long
>     Application.SetOption ("Ignore DDE Requests"), True
>     On Error Resume Next
>     Channel = DDEInitiate(strApp, strTopic)
>     IsItRunning = (0 <> Channel)
>     DDETerminate Channel
>     On Error GoTo 0
>     Application.SetOption ("Ignore DDE Requests"), False
>End Function
>
>
>
>
>David Emerson wrote:
>
> >Thanks Marty.  Perhaps some background will help.
> >
> >The program is run as a runtime via terminal server.  There can be up
> >to 10-15 concurrent users at a time.
> >Part of my program uses the following line to copy a spreadsheet 
> into a table:
> >
> >DoCmd.TransferSpreadsheet acImport, , "dbo.ttmpTemporary", Me!txtDataFile
> >
> >Sometimes this causes Access to crash (Access has encountered a
> >problem and needs to close ...).
> >
> >In the past it has seemed to be caused by users having several copies
> >of the program open at once (they only need one copy but try to tell
> >them not to keep opening a new instance of the program but to use the
> >one they already have open :'( )  and then closing their terminal
> >server session down but not closing the databases first.
> >
> >When the server is rebooted then the problem line works fine until
> >suddenly it decides to spit out the dummy again.
> >
> >I do have Enterprise Manager and can see what users have the
> >connections to the database.  Part of the problem is that there are
> >three versions of the front end all connecting to the same SQL
> >database.  I was hoping to try to identify somehow what copies of the
> >front end were open to see if there are any patterns.
> >
> >I am trying to a) find out if the user's work haboits are causing the
> >problem, or b) if it is some other problem, trying to find out what.
> >
> >David
> >
> >
> >At 29/11/2005, you wrote:
> >
> >
> >>Is this because you don't have a version of SQL EM?
> >>
> >>Maybe a call to DBCC in SQL to get user connection.
> >>Another way that might be more exact.
> >>You could use WMI and check through each machine name in the domain and
> >>check for running
> >>adp task processes. You could then maybe and I stress maybe also find
> >>out if a particular adp file is open
> >>on that machine via some similar WMI method.
> >>
> >>WMI can also give you all machine names in a domain.
> >>
> >>Look at various sample scripts here
> >>http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/
> >>
> >>This might be time consuming.
> >>Something like this
> >>
> >>'Reports the account name under which each process on a computer 
> is running.
> >>'you would have to run this for every machine name.
> >>Dim strComputer As String
> >>Dim strUserDomain As String
> >>Dim strNameofUser As String
> >>Dim colProcessLiust As Object
> >>Dim objProcess As Object
> >>Dim objWMIService As Object
> >>Dim colproperties as variant 'or is it object
> >>
> >>strComputer = "."  'This is the machine name in the domain.  "."
> >>indicates "local"
> >>Set objWMIService = GetObject("winmgmts:" _
> >>    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
> >>Set colProcessList = objWMIService.ExecQuery _
> >>    ("Select * from Win32_Process")
> >>For Each objProcess in colProcessList
> >>    colProperties = objProcess.GetOwner(strNameOfUser,strUserDomain)
> >>    debug.print  "Process " & objProcess.Name & " is owned by " _
> >>        & strUserDomain & "\" & strNameOfUser & "."
> >>Next
> >>
> >>
> >>David Emerson wrote:
> >>
> >>
> >>
> >>>I am trying to identify what users have a database open.  adp's don't
> >>>seem to have an ldb file (presumably because they use SQL and not
> >>>Jet).  Apart from looking at the current activity in SQL management,
> >>>is there any other way of finding out who has an adp or ade open?
> >>>
> >>>Regards
> >>>
> >>>David Emerson
> >>>Dalyn Software Ltd
> >>>999 Moonshine Rd, RD 1
> >>>Judgeford, Porirua
> >>>New Zealand 6006
> >>>Phone    0064 4 235-6782
> >>>Fax      0064 4 235-6783




More information about the dba-SQLServer mailing list