Thanks to all who offered suggestions. Unfortunately, none of them worked. I eventually concluded that the 'not updateable" problem related to records I was pulling from Access into Excel in earlier code. Because of this, Access apparently saw Excel as a user, and locked its tables.
I found another way to get the data imported into excel from access, and this solved the problem.
However, I remain stuck. My current code is as follows:
Public Sub ExportWsToAccessTable()
Dim AdoCon As ADODB.connection
Dim AccessFilePath As String
Dim AccessFileName As String
Dim strRange As String
Dim Range As Range
Dim DestTable As String
Dim HasFieldNames As Boolean
Dim WorksheetName As String
Dim password As String
Dim Connection1 As String
Dim Connection2 As String
Dim strSQL As String
Set AdoCon = New ADODB.connection
AccessFilePath = "S:\NEURO\PUBLIC\IMATCH\Database\"
AccessFileName = "IMATCH outcomes database.accdb"
password = "xxxxx"
DestTable = "tblKpImportPre"
WorksheetName = "Pre"
HasFieldNames = True
strRange = "a1:eu9"
Set myRange = ActiveWorkbook.Worksheets(WorksheetName).Range(strRange)
Connection1 = "Data Source = " & AccessFilePath & AccessFileName & ";"
Connection2 = Connection1 & "JET OLEDB:Database Password= " & password & ";"
strSQL = "INSERT INTO " & DestTable & " " _
& "SELECT * FROM " & ActiveWorkbook.FullName & "].[" & WorksheetName & "$]"
.Provider = "Microsoft.ACE.oledb.12.0"
.Properties("Extended Properties") = "Excel 15.0 Xml;HDR=YES;IMEX=2;ACCDB=YES"
If IsNull(password) Then
.Properties("Data Source") = Connection1
.Properties("Data Source") = Connection2
When I attempt to run this, I get an "authentication failed" error on the .Open line indicated in red. I've been beating my head against the wall
for several days, but I can't figure out what's wrong with my syntax. The password I'm feeding to Access works fine if I open the database manually.