Get the Jet User Roster using ADO.NET/OLEDB

Question

I have an old legacy VB6 application that I'm finally updating to .NET but I've hit a bit of a stumbling block: one of the things it does is provide a list of current connections to the database, using ADODB with a provider-specific schema rowset issued with this GUID specified by Microsoft. Here is the working ADODB code

Set RS = CN.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

Now I know I could still use this ADODB method if I were to add a reference to the latest COM ActiveX Data Objects library, but I would really like to avoid that, if at all possible, and find a way of doing it using OLEDB.

I've created the function below, and tried various combinations of GetOleDbSchemaTable & GetSchema – both with and without restrictions and with the GUID passed as a string, but it always either errors or returns an empty table.

'Get a list of users connected to the core database
Public Function GetUserRoster() As DataTable
    Dim connString As String = GetConnString(coreDB)
    If String.IsNullOrEmpty(connString) Then Return Nothing
    Using conn As New OleDbConnection(connString)
        Try
            conn.Open()
            Dim oGUID As New Guid("{947bb102-5d43-11d1-bdbf-00c04fb92675}")
            Dim restrictions() As String = {Nothing, Nothing, Nothing, "Table"}
            Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(oGUID, restrictions)
            Return schemaTable
        Catch ex As Exception
            logger.Error("Failed to evaluate the database user roster. {0}{1}", vbCrLf, ex.ToString)
        End Try
    End Using
    Return Nothing
End Function

So, is this even possible or do I have no other choice than to use the old COM ADODB functions?

Is this something similar? http://msdn.microsoft.com/en-us/library/kcax58fh.aspx — Yuriy Galanter, Dec 03 '13 at 19:31
@YuriyGalanter Yes, I've read through that article – and dozens of others – over the last two days, and I'm still none the wiser. Unfortunately, all the examples I've found only show how to retrieve table information... which I already knew how to do. — Antagony, Dec 03 '13 at 20:21
I looked a bit too, apparently there's not direct correlation in .NET and you may have to use ADODB via COM interop — Yuriy Galanter, Dec 03 '13 at 20:40
@YuriyGalanter I'm sadly coming to the same conclusion. But it's so annoying to have to use old, supposedly 'inferior' methods to accomplish what one would think ought to be a relatively straightforward task. — Antagony, Dec 03 '13 at 21:02

score 1 · Answer 1 · answered Oct 17 '22 at 21:02

Almost 9 years late but just in case anyone else needs this like I did today, the code below is working for me. @Antagony was very close. It seems all that needed to be done was put a 'Nothing' as the second parameter (restrictions) in the call to GetOleDbSchemaTable

Public Function GetUserRoster() As DataTable
     Dim connString As String = GetConnString(coreDB)
     If String.IsNullOrEmpty(connString) Then Return Nothing
     Using conn As New OleDbConnection(connString)
         Try
             conn.Open()
             Dim oGUID As New Guid("947bb102-5d43-11d1-bdbf-00c04fb92675")
             Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(oGUID, Nothing)
             Return schemaTable
         Catch ex As Exception
             logger.Error("Failed to evaluate the database user roster. {0}{1}", vbCrLf, ex.ToString)
         End Try
     End Using
     Return Nothing
End Function

score 0 · Answer 2 · answered Jan 10 '14 at 08:18

0

This you cant do as simply as we used to do.

That said you can actually read the access lock file .ldb

there is a dll from microsoft that does that too.

How to Read .LDB Files

answered Jan 10 '14 at 08:18

Archlight

2,019
2
21
34

1

I just noticed also that there is an answer that reads a ldb file here on stack overflow. http://stackoverflow.com/questions/15899930/how-to-see-who-is-using-my-access-2003-database-in-network – Archlight Jan 10 '14 at 08:21
Yeah I'd already looked at that as a possibility, but unfortunately it only lists the computers connected, not the users. It's also unreliable as it doesn't automatically remove a listed computer if one user disconnects while another is still connected. It only gets removed (or rather, overwritten) if another user connects. In the end I decided to manage user connections in a "register' table with a polled `Time` field that connected users regularly update. Thanks for your efforts though. :) – Antagony Jan 12 '14 at 15:30
1

Add that as an answer. It's quite good to know for the next man. – Archlight Jan 13 '14 at 08:02

Get the Jet User Roster using ADO.NET/OLEDB

2 Answers2