Issues Importing Stored Function into Excel as ADO.RecordSet using VBA

[whiteheadw]

All,



I have a complicated query (called qryOrderCreditAll) that involves iif statements, cStr functions, and cLng functions. Because of this, when browsing my Access DB in C# using the DataBase explorer, qryOrderCreditAll is classified as a function, instead of a stored procedure or view, as far as ADO is concerned.

In Excel (using VBA), I have a recordset that I would like to fill using qryOrderCreditAll. Is this possible and if so then how.

Here is the code I currently have:

cADODB //This is a class I designed to manage most of my ADO connection needs--This way, I can easily convert to late-binding once debugged to make version-independent

Option Explicit

Private pConn As ADODB.Connection
Private pCmd As ADODB.Command
Private pRS As ADODB.RecordSet
Private pStrDBPath As String

Private Sub Class_Initialize()
    Set pConn = New ADODB.Connection
    Set pCmd = New ADODB.Command
End Sub

Private Sub Class_Terminate()
    pRS.Close
    pConn.Close
    Set pConn = Nothing
    Set pCmd = Nothing
    Set pRS = Nothing
End Sub

Public Property Let StoredQueryFlagJet(boolSP As Boolean)
    If boolSP Then pCmd.CommandType = adCmdStoredProc
End Property

Public Property Let Provider(strProvider As String)
    pConn.Provider = strProvider
End Property

Public Property Let DBPath(strDBPath As String)
    pStrDBPath = strDBPath
End Property

'Public Property Let ConnString(strConn As String)
'    pConn.ConnectionString = strConn
'End Property

Public Property Let CmdText(strCmd As String)
    pCmd.CommandText = strCmd
End Property

Public Sub QueryData()
    pConn.Mode = adModeRead
    pConn.Open "Provider=" & pConn.Provider & ";Data Source=" & pStrDBPath & ";"
    pCmd.ActiveConnection = pConn
    pCmd.CommandTimeout = 30
    Set pRS = pCmd.Execute 'I get a debug error here, "Method 'Execute' of object '_Command' failed"
End Sub

Public Property Get RecordSet() As ADODB.RecordSet
    Set RecordSet = pRS
End Property

Module1 //implements cADODB

Option Explicit

Private Const STR_FILE_LOC As String = "O:\SomeFolder\MyAwesomeDB.mdb"
Private Const STR_PROVIDER As String = "Microsoft.Jet.OLEDB.4.0"

Public Sub CreatePivotTable(rng As Range, strFileLoc As String, strProvider As String, strCmd As String, Optional boolSP As Boolean = False)
    Dim cADO As cADODB
    Dim pvtCache As PivotCache
    Dim wb As Workbook
    Set cADO = New cADODB
    cADO.Provider = strProvider
    cADO.DBPath = strFileLoc
    cADO.CmdText = strCmd
    cADO.StoredQueryFlagJet = boolSP 'In this example, boolSP is set to true, which based on cADODB command "If boolSP Then pCmd.CommandType = adCmdStoredProc" should allow me to import my data from this function (or whatever it's called)
    cADO.QueryData 'last line of cADO.QueryData throws an exception.  This is where I'm stuck.
    Set wb = rng.Worksheet.Parent
    Set pvtCache = wb.PivotCaches.Add(xlExternal)
    Set pvtCache.RecordSet = cADO.RecordSet
    pvtCache.CreatePivotTable rng
    Set pvtCache = Nothing
    Set cADO = Nothing
    Set wb = Nothing
End Sub

Private Sub Temp()
    CreatePivotTable ActiveSheet.Cells(1, 1), STR_FILE_LOC, STR_PROVIDER, "qryOrderCreditAll", True
End Sub

If it is not possible to import this kind of data using ADO, then is there a way to convert numbers to texts and texts to numbers WITHOUT using VBA code (like cLng and cStr), which I think is causing ADO to think this is a function instead of an SP?

Any help appreciated.

EDIT: I should add that there doesn't appear to be any issues with the connectionstring, as I'm able to execute other stored queries (that don't use cLng and cStr) just fine using the same piece of code.

EDIT: All,

After playing around with the code, I did find one solution that seems to work. Apparently, the issue is that I either don't know how to query a stored function in Jet using ADO recordsets or ADO recordsets do not support this function. What I have done for now is to query qryOrderCreditAll using DAO. However, since DAO recordsets cannot be used as a recordset object inside a PivotTable, I then (via iteration) had to create a virtual ADO recordset from the DAO recordset. Then, I applied this virtual ADODB.RecordSet to the PivotCache.RecordSet property.

While this works, I am a bit concerned about the memory footprint, as I'll basically be having two RSs in memory at the same time, and the data from this query can get rather big. So, while I have a solution that works for now (though a bit messy), I still am looking for something a bit more elegant. Any ideas?

Thanks.