Arthur Fuller
artful at rogers.com
Tue Nov 29 23:52:49 CST 2005
You can just open Query Analyzer and run sp_who against the database of
interest. You will probably see multiple connections per user. I could be
wrong, but I think that this is because Access might open multiple
connections to deal with, say, a form that has several data-driven
combo-boxes as well as its basic record source. Nevertheless, you could
always write a table UDF that selects distinct, based on the results of the
sp_who stored procedure. To view this procedure, just open the master
database, find it and view its properties. You can then copy and paste to a
new sproc or UDF and make your desired changes.
HTH,
Arthur
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
MartyConnelly
Sent: November 28, 2005 7:35 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Who Has adp/ade open?
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
>>>
>>>__
>>>
>>>
>>>
>>>
>>>
>>>
>>--
>>Marty Connelly
>>Victoria, B.C.
>>Canada
>>
>>
>>
>>_______________________________________________
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>http://www.databaseadvisors.com
>>
>>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com