Hi,
I am trying to use VBA to open up an access database, enter values into a record from a series of defined names into a temporary table, then eventually run an append/update query to copy this record into the main table.
At the moment I am struggling with the first step, which is giving me the error:
runtime error 214721783(80040e4d) Authentication failed
This is the code:
The database will eventually be split with the back-end on a network drive, but at the moment I am getting this error even when it's located on my own desktop.
Any suggestions? It's the .Open line that is highlighted by the debugger. I have tried both changing selectDB() from a sub to a function and removing it completely (replacing PEMdb with a path & filename) but to no avail...
Thanks,
Duncan
I am trying to use VBA to open up an access database, enter values into a record from a series of defined names into a temporary table, then eventually run an append/update query to copy this record into the main table.
At the moment I am struggling with the first step, which is giving me the error:
runtime error 214721783(80040e4d) Authentication failed
This is the code:
Code:
Sub SelectDB()
'Select Database file location
PEMdb = Application.GetOpenFilename(FileFilter:="Access Files (*.mdb), *.mdb", Title:="Please select the location of the Personnel & Equipment Management Database")
If PEMdb = False Then
' They pressed Cancel
MsgBox "The operation was cancelled", vbInformation, "Import Cancelled"
Exit Sub
Else
Workbooks.Open Filename:=PEMdb
End If
End Sub
Sub Validate_Import_1()
'Validate Data before importing
End Sub
Sub Import_Stage_1()
'Export data from excel into Access DB
Call SelectDB
Dim cn As ADODB.Connection
Dim cmPerDet As ADODB.Command
Set cn = New ADODB.Connection
Set cmPerDet = New ADODB.Command
With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & PEMdb & "; Persist Security Info=False"
.Open
End With
With cmPerDet
.CommandText = "Insert into tbl_TEMP_Personnel_Details(Employee ID, First Name, Last Name) Values ('" + Range("Employee_ID").Text + "','" + Range("First_Name").Text + "','" + Range("Last_Name").Text + "')"
.ActiveConnection = cn
End With
End Sub
The database will eventually be split with the back-end on a network drive, but at the moment I am getting this error even when it's located on my own desktop.
Any suggestions? It's the .Open line that is highlighted by the debugger. I have tried both changing selectDB() from a sub to a function and removing it completely (replacing PEMdb with a path & filename) but to no avail...
Thanks,
Duncan