VBA-ADO recordset problem pulling from Access

[JimSnyder]

I am getting "Run-time error '-2147287038'(80030002)" when the code getting to populating the recordset. I can always fall back on embedded SQL, but want to introduce my team to faster techniques.

What I thought of trying was to open the connection, the use QueryTable.Add to bypass the recordset. Has anyone tried this or does anyone know if this can be done?

Additional information:
Excel & Access: Microsoft Office Professional Plus 2013
Platform: Windows 7 Enterprise
References: Microsoft ActiveX Data Objects Recordset 2.8 Library
Microsoft ActiveX Data Objects 6.0 Library
Microsoft Office 15.0 Object Library

Option Explicit ' Must declare variables - clean programming technique


Public Function OpenAccessConnection(ByRef adoConn As ADODB.Connection, _
    ByRef recordSet As ADODB.recordSet, _
    ByVal tablePath As String, _
    ByVal sqlQuery As String) As Boolean
    
    Dim adoCommand As ADODB.Command
    OpenAccessConnection = True
    
'    On Error GoTo OnError
    Set adoConn = New ADODB.Connection
    Set recordSet = New ADODB.recordSet
    
    With adoConn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Mode = adModeRead      ' SQL injection protection
        .CursorLocation = adUseClient
        .Open tablePath
    End With
    
'    adoConn.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & tablePath
    
'    recordSet.Open sqlQuery, adoConn, adOpenStatic, adLockBatchOptimistic, adCmdFile
    recordSet.Open "SELECT * FROM EPIC_Denial_Data", adoConn, adOpenStatic, adLockBatchOptimistic, adCmdFile
    On Error GoTo 0
    Exit Function
    
OnError:
    RemoveConnection recordSet, adoConn
    OpenAccessConnection = False
End Function

Business problem being solved:
My company has outside vendors who supply some processing. When their data comes back, it needs to be reconciled with the original sources, so I am looking for unmatched records. Internally, we have two hospital management systems that are producing the data being sent to the outside vendor. All three sources are imported into Access tables.
I am using Excel VBA to access the tables and do the compares, then build workbooks of non-matching records that will be sent to three different sets of people to be worked on. The file moving and emailing are being done through a tool called 'Automate' and are not part of the macro.

 

[JimSnyder]

SOLVED:
By using just a QueryTable with a query for the connection, this works well.

    ' Create a querytable based on a query to the Access table
    With epicSheet.QueryTables.Add( _
        Connection:=connStr, _
        Destination:=Range("A1"), _
        Sql:=sqlQuery)
        .BackgroundQuery = True
        .Refresh
    End With