MSAccess Function within MSSQL query string

Hi

I've written a function in MS Access called: GetBenchmarkReturn

I've been sending SQL Strings from Excel to Access which then returns the results of this SQL query to Excell.  

All has been working fine with typical SQL statements (SELECT .. FROM ... WHERE... etc).  But the minute I include the a function written in Access in the SQL string (SELECT ... GetBencharkReturn(..) FROM ... WHERE.. etc) i get an 'automation' error at:

' Open recordset table
    rst.Open sql_string, cnt

in the sub below

**********************************************************************************************
The following is an function written in Access for use in Access SQL queries
**********************************************************************************************
 
Public Function GetBenchmarkReturn(benchmark_id As Long, myDate As Date) As Variant
      
    Dim rs As DAO.Recordset
   
    Set rs = CurrentDb.OpenRecordset("SELECT benchmarks.k1, benchmarks.portfolio_id1, benchmarks.k2, benchmarks.portfolio_id2, benchmarks.k3, benchmarks.portfolio_id3, benchmarks.k4, benchmarks.portfolio_id4, benchmarks.k5, benchmarks.portfolio_id5, benchmarks.k6, benchmarks.portfolio_id6, benchmarks.k7, benchmarks.portfolio_id7, benchmarks.k8, benchmarks.portfolio_id8, benchmarks.k9, benchmarks.portfolio_id9, benchmarks.k10, benchmarks.portfolio_id10, benchmarks.constant " & _
                                        "FROM Benchmarks " & _
                                        "WHERE (((benchmarks.benchmark_id)=" & benchmark_id & "));")
   
    If Not rs.EOF Then
        GetBenchmarkReturn = ((1 + rs("k1").Value * GetPortfolioGrossReturn(rs("portfolio_id1"), myDate) + rs("k2").Value * GetPortfolioGrossReturn(rs("portfolio_id2"), myDate) + rs("k3").Value * GetPortfolioGrossReturn(rs("portfolio_id3"), myDate) + rs("k4").Value * GetPortfolioGrossReturn(rs("portfolio_id4"), myDate) + rs("k5").Value * GetPortfolioGrossReturn(rs("portfolio_id5"), myDate) + rs("k6").Value * GetPortfolioGrossReturn(rs("portfolio_id6"), myDate) + rs("k7").Value * GetPortfolioGrossReturn(rs("portfolio_id7"), myDate) + rs("k8").Value * GetPortfolioGrossReturn(rs("portfolio_id8"), myDate) + rs("k9").Value * GetPortfolioGrossReturn(rs("portfolio_id9"), myDate) + rs("k10").Value * GetPortfolioGrossReturn(rs("portfolio_id10"), myDate)) ^ (12) + rs("constant").Value) ^ (1 / 12) - 1
    Else
        GetBenchmarkReturn = Null
    End If
    
    rs.Close
End Function
 
Public Function GetPortfolioGrossReturn(portfolio_id As Long, myDate As Date) As Variant
      
    Dim rs As DAO.Recordset
    
    If ((Not IsNull(portfolio_id)) And IsDate(myDate)) Then
        
         Set rs = CurrentDb.OpenRecordset("SELECT portfolio_returns.gross_return " & _
                                             "FROM portfolio_returns " & _
                                             "WHERE (((portfolio_returns.date)=#" & myDate & "#) AND ((portfolio_returns.portfolio_id)=" & portfolio_id & "));")
         If Not rs.EOF Then
             GetPortfolioGrossReturn = rs("gross_return")
         Else
             GetPortfolioGrossReturn = Null
         End If
    
         rs.Close
    End If
End Function
 
Public Sub updateBenchmarks()
Dim SQL
 
SQL = "UPDATE portfolio_details LEFT JOIN portfolio_returns ON portfolio_details.portfolio_id = portfolio_returns.portfolio_id SET portfolio_returns.benchmark_return = GetBenchmarkReturn([benchmark_id],[date]) " & _
        "WHERE (((portfolio_returns.date)>=#" & Format(myStartPeriod, "MM/DD/YYYY") & "#) AND ((portfolio_returns.portfolio_id)=" & myPortfolio & "));"
 
CurrentDb.Execute (SQL)
 
End Sub
 
*****************************************************************************
The following code sends the SQL query from excel to Access
*****************************************************************************
Sub MS_SQL_query(sql_string As String, target_sheet As String, target_cell As String)
 
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    Dim xlWs As Object
    Dim strDB As String
   
    ' Set the string to the path of the Access database
    strDB = Workbooks("Interface - reports.xls").Worksheets("Index").Range("database_location").Value
  
    ' Open connection to the database
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";"
    
    ' Open recordset table
    rst.Open sql_string, cnt
    
    Set xlWs = Workbooks("Interface - reports.xls").Worksheets(target_sheet)
    
    xlWs.Range(target_cell).CopyFromRecordset rst   'Note: CopyFromRecordset will fail if the recordset contains an OLE object field or array data such as hierarchical recordsets
 
   ' Close ADO objects
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    
    ' Release Excel references
    Set xlWs = Nothing
 
End Sub

Select allOpen in new window