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

MartyConnelly martyconnelly at shaw.ca
Mon Nov 28 18:35:04 CST 2005


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 
deleteing the name on logoff.
and check beforehand if that user name pre-exists then dump em out with 
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

 '   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 
   'Doesn't allow multiple copies of Access program running at the same 
   '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.
>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
>>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 & "."
>>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?
>>>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
>>Marty Connelly
>>Victoria, B.C.
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com

Marty Connelly
Victoria, B.C.

More information about the dba-SQLServer mailing list