CODEWITHSUNDEEP
excelvbams-accessdao
hutchy
hutchy

Reputation: 13

How to stop password prompt using DAO in Excel VBA and Access

I am using DAO to run queries on a password protected Access database using vba in Excel, occasionly while running the sub an instance of Access is opened up along with a window asking for the database password, not entering a password and pressing cancel makes no difference, the query still runs with the output displayed, is there any way to stop access opening up and asking for a password?

Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim DB_Name As String
Dim cond As String
Dim pWord As String

Dim wb As Workbook: Set wb = ThisWorkbook
With wb

    On Error GoTo ErrHandler:

    clearRange.Value = ""

    DB_Name = DataBname()
    pWord = pwd()

    Set MyDatabase = DBEngine.Workspaces(0).OpenDatabase(DB_Name, False, True, pWord)
    Set MyQueryDef = MyDatabase.QueryDefs(queryName) 'Query Name

    Set MyRecordset = MyQueryDef.OpenRecordset 'Open the query
    pasteRange.CopyFromRecordset MyRecordset

    failRange.Value = False

My_Exit:

    If MyRecordset Is Nothing Then
        'Do Nothing
    Else
        MyRecordset.Close
        Set MyRecordset = Nothing
    End If
    If MyDatabase Is Nothing Then
        'Do Nothing
    Else
        MyDatabase.Close
        Set MyDatabase = Nothing
    End If

End With

Exit Sub

ErrHandler:

MsgBox Err.Description
failRange.Value = True
Resume My_Exit

End Sub

Function pwd() As String

pwd = "MS Access;PWD=password"

End Function

Upvotes: 0

Views: 513

Answers (2)

user10331689
user10331689

Reputation: 16

To fix the issue, you must change the reference in your workbook from DAO.36 to Microsoft Office XX.0 Access database engine Object Library.

I have tested the code with Access 2010 and Access 2013, and it works fine.

best regards

Upvotes: 0

user10327845
user10327845

Reputation: 1

There is nothing wrong inside your code. Tested with Access 2003 Backend. May be if you use Access 2007 and higher, you must set in the DB options the handling for password to legacy.

best regards

Upvotes: 0

Related Questions