access database, user level protection and vb.net

Question

    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\itweb\Documents\Visual Studio 2010\WebSites\notitiae\databases\notitiae.accdb"
    Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

    Dim queryString As String = "SELECT [tblCounties].* FROM [tblCounties]"
    Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim DSCounties As System.Data.DataSet = New System.Data.DataSet
    dataAdapter.Fill(DSCounties)

Ok. So I’m using the above code to connect to an access 2007 DB. Now I have to add a linked table pointing to a DB which has user level protection. Research has shown me how to add a username and password to the connection string. Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\itweb\Documents\Visual Studio 2010\WebSites\notitiae\databases\notitiae.accdb; user name=user; password=pwd;"

I also learned that I need to add a reference to the workgroup file, “V:\DATABASE\WORKDIR\diodb.mdw”. my problem is that all the information online referencing workgroup files use an ADODB connection rather than my OleDb connection and jet 4 rather than ACE 12. What’s the syntax for adding the workgroup file? I tried to add an ADODB object to my Visual studio project but VS wouldn't add the reference for ActiveX Data Objects 2.5. That's why I'm using OleDB.

Gord Thompson Gord Thompson · Accepted Answer · 2013-05-03T14:35:06

The following Excel VBA code works, proving that ACE.OLEDB can still open an .mdb file with user-level security. Note that the attribute for the .mdw file still uses the Jet OLEDB: prefix.

Sub ulsTest()
Dim con As ADODB.Connection, rst As ADODB.Recordset
Set con = New ADODB.Connection
con.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\Public\ulsTest\ulsTest.mdb;" & _
        "Jet OLEDB:System Database=C:\Users\Public\ulsTest\Security.mdw;" & _
        "User ID=Gord;" & _
        "Password=obfuscated;"
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM ulsTable", con
Debug.Print rst(0).Value
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
End Sub

access database, user level protection and vb.net

2 Answers