Transfer Excel data to Access

1
votes

I'm following the instructions here http://software-solutions-online.com/excel-vba-export-worksheet-to-existing-access-table/ to transfer data from an Excel spreadsheet to an Access database. The script I have adapted is:

Sub MailMerge2()
Dim strPath As String
Dim objAccess As Access.Application
Dim strExcelPath As String

strPath = "C:...Documents\MailMerge2"
strExcelPath = Application.ActiveWorkbook.FullName
Set objAccess = New Access.Application
Call objAccess.OpenCurrentDatabase(strPath)
objAccess.Visible = True
Call objAccess.DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel8, "MyTable1", strExcelPath, _
True, "A1:D11")
End Sub

However, running this gives me an error saying:

Run-time error: 7866, Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file.

Any suggestions on which of these the problem is? I'm fairly new to Access, and haven't quite got the hang of the terminology yet.

1
excelvbams-access

1 Answers

3
votes

Found the problem. I left out .accdb in my Access db file names.