Could Not Find Installable ISAM Excel 2010

Question

I have this sub in Excel 2010 that's supposed to copy in a table from Access 2010. When I try to run the code, it gives me a 'Could not find installable ISAM'. When I debug, it highlights objDB.Execute.

Private Sub btnGetData_Click()

Unload ParameterMenu
formWait.Show

'Save workbook
ActiveWorkbook.Save

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database

strExcelFile = "X:\Form.xlsm"
strWorksheet = "RawDates"
strDB = "X:\Tables.accdb"
strTable = "Dates"

Set objDB = OpenDatabase(strDB)

objDB.Execute _ 'Error occurs here.
  "SELECT * INTO [Excel 14.0;DATABASE=" & strExcelFile & _
   "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"

objDB.Close
Set objDB = Nothing

Unload formWait
FinishDialog.Show

End Sub

I'm not overly experienced with VBA, so any assistance would be greatly appreciated.

Answer 1

The correct format is 'Excel 12.0 Macro' rather than 'Excel 14.0':

objDB.Execute _ 'Error occurs here.
  "SELECT * INTO [Excel 12.0 Macro;DATABASE=" & strExcelFile & _
   "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"

Edit:

Perhaps try ADO instead:

Private Sub btnGetData_Click()

Unload ParameterMenu
formWait.Show

'Save workbook
ActiveWorkbook.Save

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Object
Dim rs As Object

strExcelFile = "X:\Form.xlsm"
strWorksheet = "RawDates"
strDB = "X:\Tables.accdb"
strTable = "Dates"

Set objDB = CreateObject("ADODB.Connection")
With objDB
    .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strDB & ";"
      .Open
   End With

Set rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM " & "[" & strTable & "]", objDB, 3, 1, 1

If Not rs.EOF Then _
ThisWorkbook.Worksheets(strWorksheet).Cells(Rows.Count, "A").End(xlUp).Offset(1).CopyFromRecordset rs

rs.Close
objDB.Close
Set objDB = Nothing

Unload formWait
FinishDialog.Show

End Sub