OpenCurrentDatabase method does not work

[vladk]

Hi, I am trying to import data from the workbook into existing Access table. The field data types in Access table I left the same as they were created when I designed the table using manual exporting of EXCEL sheet into Access. So the field data types should be fine.

I want to code in Excel importing of the data from one of the tabs into that Access and I hit the problem. When TransferSpreadsheet gets executed, the Data Link Properties window appears. When I hit "Test Connection" button, the message is generated, saying "Test Connection failed because of an error in initializing provider. You attempted open the database that is already opened exclusively ..." Below is the code.

Can anybody make this method work?

Thanks,

vladk

Private Sub cmdExport_LDF_Sheet_To_Access_Click()
Dim objAccess As New Access.Application
Dim strDataSource As String

'Here is the full path to Access db, including it's name
strDataSource = ThisWorkbook.Worksheets("Experiment").Range("LDF_Location").Value

'Here I delete all from the existing "LDF Table" table prior to importing new data into the table
With objAccess
.OpenCurrentDatabase strDataSource, False
.Visible = True
.CurrentDb.Execute "DELETE * FROM [LDF Table];"
End With

ThisWorkbook.Worksheets("LDF Table").Activate
'Here I try to import the data form the worksheet. The name of the sheet is "LDF Table", the same as the name of the Access table that should get the values from Excel and hit the problem
DoCmd.TransferSpreadsheet AcDataTransferType.acImport, _
AcSpreadSheetType.acSpreadsheetTypeExcel9, _
"LDF Table", strDataSource, True, "A2:H84"

End Sub