Access Stored Procedure Automation Error

Hi All

I have a stored query procedure that executes fine in MS Access, but when I use the ADO library to execute said stored query procedure, and return the results to Excel, I receive an 'Automation error unspecified error -2147467259 (800004005)'.

It appears to be when it tries to open the recordset (rst. open Source:=...etc), I've included both the VBA that calls the open procedure and the open recordset procedure code itself.

Any help would be greatly appreciated.

Cheers.

Code:

Sub retirve_query(query_name As String)

    Set process_simba_ws = Sheet6
    Set checks_data_ws = Sheet5
    header_rw = 1
    fst_col = 1
    Dim header_rng As Range

    On Error GoTo ERROR_RETRIEVEQUERY
    Application.Interactive = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Clear previous data
    checks_data_ws.Rows.EntireRow.Delete

    create_db_connection
    open_record_set query_name
    
    ' Populate column headers
    For Each fld In rst.Fields
        checks_data_ws.Cells(header_rw, fst_col).Offset(0, i) = fld.Name
        i = i + 1
    Next fld
    
    ' Populate query data
    checks_data_ws.Cells(header_rw + 1, fst_col).CopyFromRecordset rst
    
    ' Format columns
    With checks_data_ws
        .Columns.EntireColumn.AutoFit
        .Rows(1).Font.Bold = True
        .Activate
    End With
    
    ' Update header's formatting
    With checks_data_ws
        lst_col = .Cells(header_rw, .Columns.Count).End(xlToLeft).Column
        Set header_rng = .Range(.Cells(header_rw, fst_col), .Cells(header_rw, lst_col))
        header_rng.Font.Bold = True
        header_rng.Interior.Color = 13553360
        .Cells(header_rw, fst_col).Activate
    End With

    close_rst
    close_cnn

EXIT_RETRIEVEQUERY:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.Interactive = True
On Error GoTo 0
Exit Sub

ERROR_RETRIEVEQUERY:
MsgBox Err.Description
GoTo EXIT_RETRIEVEQUERY
End Sub

Open recordset code:

Function open_record_set(query_name As String)

    ' Create a new recordset
    Set rst = CreateObject("ADODB.Recordset")
    ' Define recordset's attributes
    rst.CursorLocation = 3 'adUseClient
    rst.Open Source:=query_name, _
             ActiveConnection:=cnn, _
             CursorType:=2, _
             LockType:=3, _
             Options:=4 'adCmdStoredProc
             
    Set open_record_set = rst

End Function