Excel QueryTable on Text File source fails with Jet OLEDB Connect String

Question

Using VBA, I am trying to create an Excel querytable to provide a subset of data from a text file. I would like to use a Jet OLEDB connection string as the querytable Connection. Why does this fail?

Here is the procedure.

Sub OledbTest1()  'FAILS.
'Create querytable with oledb connect string.
    Const strConn = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\RSJCB\Desktop\;" & _
        "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
    Dim wsht As Worksheet
    Set wsht = ThisWorkbook.Worksheets.Add()
    With wsht
        'The next line errors with 1004: Application-defined of object-defined error
        .QueryTables.Add strConn, .Range("A1"), "SELECT TOP 10 * FROM [TestFile.csv]"
        .QueryTables(1).Refresh
    End With
    Set wsht = Nothing
End Sub

The connection string works if I use it to create an ADO recordset, and then use that recordset as the querytable Connection.

Sub OledbTest2()  'SUCCEEDS.
'Create querytable with ado recordset opened using oledb connect string.
    Const strConn = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\RSJCB\Desktop\;" & _
        "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
    Const strSql = "SELECT TOP 10 * FROM [TestFile.csv]"
    Dim wsht As Worksheet
    Dim rst As New ADODB.Recordset
    rst.Open strSql, strConn
    Set wsht = ThisWorkbook.Worksheets.Add()
    With wsht
        'The next line errors with 1004: Application-defined of object-defined error
        .QueryTables.Add rst, .Range("A1")
        .QueryTables(1).Refresh
    End With
    Set wsht = Nothing
    Set rst = Nothing
End Sub

It also works if I use a Microsoft Text ODBC Driver connection string as the querytable Connection. However, this appears to work a little slower, and I'm reading 700K records in the actual code. Further, I've never been able to make an OLEDB connection to a querytable and would like to know how to do it.

Sub OdbcTestQtbl()  'SUCCEEDS.
'Create querytable with odbc connect string.
    Const strConn = _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=C:\Users\RSJCB\Desktop\;" & _
        "Extensions=asc,csv,tab,txt;"
    Dim wsht As Worksheet
    Set wsht = ThisWorkbook.Worksheets.Add()
    With wsht
        .QueryTables.Add "ODBC;" & strConn, .Range("A1"), "SELECT TOP 10 * FROM [TestFile.csv]"
        .QueryTables(1).Refresh
    End With
    Set wsht = Nothing
End Sub

Greatly appreciate any help on this.

Answer 1

You need to let Excel know that the driver is an OLEDB driver. Just append the (apparently undocumented) provider specifier to the start of the connection string:

.QueryTables.Add "OLEDB;" & strConn, .Range("A1"), "SELECT TOP 10 * FROM [TestFile.csv]"