ADODB recordset dosn't work in function or class module?!? (1 Viewer)

[basstard80]

Hi guys,
I'm new to this forum. I need the help of some expert!

I'm trying to build a report interface of an Access 2007 DB using excel and ADODB. All the stubs and tests I did using code found on the web worked fine. So I started a clean coding.

1. What I did before (testing time):
A sub triggered by a button, using ADODB.Command object. It worked fine, the parameter query I called gave back the right result data.

2. What I did after:
A function (so I could pass query parameter stored in other spreadsheet cell) which would have copied the result into a range (also passed as argument). Result: it doesn't work. The function went into error when calling ADODB.Recordeset.MoveFirst method, or ActiveWorkbook.Sheets("Sheet1").Cells(1,1) = rs(0).Value.

I thought the problem was accessing data while excel holds the cell calling the function...

3. The workaround
I tryed working around this. I thought the problem was using the Recordset object into a function so I stored the parameter in global variable and I called a sub to update data using the Calculate event trigger. It worked.
So I wanted to clean the code and I made a class to store the parameters and the method to update data in the spreadsheet. And this time, a sub in a class module, it didn't work.

I can't image any other workaround or solution. This seems to be crazy.
I know this post is borderline among Access, databases and Excel, but as I did this after have thought to create an report interface for my db, I'm pretty sure people here have already coded something similar or at least tryed as I did...

I hope somebody can give me an help, or at least give me a scientific explaination on why this VBA seem to give a lot of limitation.

Thanks in advance to all the readers and replyers!

Here below the piece of code which doesn't work:

CLASS MODULE

Public StartDate As Date
Public FinishDate As Date
Public Interval As String
Public Accounts As Range
Public Destination As Range
Public BalanceType As String
Public Orientation As String
Public isDirty As Boolean

Public Sub getData()
    
    On Error GoTo ErrHandler
    
    Dim cn As ADODB.Connection
    Dim com As ADODB.Command
    Dim rs As ADODB.Recordset
    
    Dim dbpathname As String
    Dim QueryName As String
    Dim formatString As String
    Dim plotH As Boolean
    Dim k As Long
    Dim j As Long
    
    ' ------------------------------
    ' -- Setup process parameters --
    ' ------------------------------
    
    ' INTERVAL
    Select Case Interval
    Case "D"
        formatString = "yyyy/mm/dd"
    Case "M"
        formatString = "yyyy/mm"
    Case "Q"
        formatString = "yyyy/qq"
    Case "H"
        formatString = ""
    Case "Y"
        formatString = "yyyy"
    End Select
    
    ' BALANCE TYPE
    Select Case BalanceType
    Case "SUBTOTAL"
        QueryName = "qryInterface_TransactionBalanceByAccount_Subtotal"
    Case "TOTAL"
        QueryName = "qryInterface_TransactionBalanceByAccount_Total"
    End Select
    
    ' ORIENTATION
    Select Case Orientation
    Case "H"
        plotH = True
    Case "V"
        plotH = False
    End Select
    
    ' -------------------------------
    ' -- Start database processing --
    ' -------------------------------
    
    ' Retrieve database path
    dbpathname = "D:\myAccounting\myAccounting.accdb"
    
    'Create the connection string and open the connection
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpathname & ";Persist Security Info=False;"
    cn.Open cn.ConnectionString
    cn.CursorLocation = adUseClient
    
    ' Create and setup the command object
    Set com = New ADODB.Command
    com.CommandType = adCmdStoredProc
    com.ActiveConnection = cn.ConnectionString
    com.CommandText = QueryName
    
    ' Setup query parameters
    com.Parameters.Append com.CreateParameter("FormatString", adVarChar, adParamInput, 255)
    com.Parameters(0).Value = formatString
    
    com.Parameters.Append com.CreateParameter("startdate", adDBDate, adParamInput)
    com.Parameters(1).Value = StartDate
    
    com.Parameters.Append com.CreateParameter("finishdate", adDBDate, adParamInput)
    com.Parameters(2).Value = FinishDate
    
    ' Execute the command and get result into a recordset
    Set rs = com.Execute
    
    ' Copy result into excel worksheet
    'ActiveWorkbook.Sheets("Sheet3").Cells(10, 2).CopyFromRecordset rs

    ' Copy results with a loop (less performant)
    rs.MoveFirst
    j = 1
    Do While Not rs.EOF
        For k = 0 To rs.Fields.Count - 1
            ActiveWorkbook.Sheets("Sheet1").Cells(j, k + 1) = rs(k).Value
        Next
        rs.MoveNext
        j = j + 1
    Loop
 
    Exit Sub

ErrHandler:
    If Err.Number <> 0 Then MsgBox _
        "CustomerName- Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
End Sub

This is the error arose
#-2147467259
"Operation is not supported for this type of object"