[dba-SQLServer] Current Connections to SQL from Access

[David Emerson]

Problem solved - I just needed permissions to see other users.

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of David Emerson
Sent: Friday, 7 August 2015 3:01 p.m.
To: AccessDSQL
Subject: [dba-SQLServer] Current Connections to SQL from Access

Hi Listers,

How do I find out who is connected to a SQL database using my Access 2010
front end?  Tables are connected using ODBC.

Connections done this way:

AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String,
stServer As String, stDatabase As String)

    Dim td As TableDef
    Dim stConnect As String
    
    stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" &
stDatabase & ";Trusted_Connection=Yes"
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD,
stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td



I have this code but it only shows me as connected and not the other users
connected via Access.

SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame
;

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand