[dba-SQLServer] Current Connections to SQL from Access

[Dan Waters]

Hi David,

I use this:  https://support.microsoft.com/en-us/kb/198755

It's quite reliable.

Good Luck!
Dan

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of David Emerson
Sent: Thursday, August 06, 2015 10:01 PM
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




_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com