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
