Opening a password encrypted access database using DAO VB.NET

Question

I create a database like this:

Sub Main()

        Dim wrkDefault As Workspace
        Dim dbE As DBEngine
        Dim dbs As Database

        'Get default Workspace.
        dbE = New DBEngine
        wrkDefault = dbE.Workspaces(0)

        'Set the database filename
        Dim DBFilename As String
        DBFilename = "c:\mydb.mdb"

        'Make sure there isn't already a file with the same name of
        'the new database file.
        If Dir(DBFilename) <> "" Then
            MsgBox("File already exists!")
            Exit Sub
        End If

        'Create a new encrypted database with the specified
        'collating order.
        'lock database with the password 'hello'
        dbs = wrkDefault.CreateDatabase(DBFilename, _
              LanguageConstants.dbLangGeneral & ";pwd=hello;", DatabaseTypeEnum.dbEncrypt)

        dbs.Close()

    End Sub

How do I open this database again in VB.NET using DAO?

Answer 1

Dim cn As New ADODB.Connection
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=c:\db1.mdb"
cn.Properties("Jet OLEDB:Database Password") = "mypwd"
cn.Open

For DAO, connection strings are most likely going to look either like this:

ODBC;DSN=DSNname;DATABASE=DBname;UID=SQLUser;PWD=SQLpassword;

or like this:

Driver={Microsoft Access Driver (*.mdb)};" & _
         "DBQ=C:\...\NWind.mdb;" & _
         "UID=admin;PWD=password;"

For the first string, you will have to create a Data Source Name (DSN) file, using Control Panel/Administrative Tools/ODBC Sources. The second string does not require a DSN connection. Now you know why they invented ADO.NET.