[dba-SQLServer] Current Connections to SQL from Access

[David Emerson]

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