Get the Jet User Roster using ADO.NET/OLEDB

690 views Asked by At

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?

2

There are 2 answers

3
Archlight On

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

0
xcr On

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