Solved

MSAccess Function within MSSQL query string

Posted on 2009-05-08
15
646 Views
Last Modified: 2013-11-27
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

Open in new window

0
Comment
Question by:tonikgroup
  • 5
  • 5
  • 3
15 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Try this:

SELECT ... GetBencharkReturn(..) AS BenchMark FROM ... WHERE.. etc
0
 

Author Comment

by:tonikgroup
Comment Utility
I have already tried this:

SELECT member_portfolio_interest.date, member_portfolio_interest.declared_interest, GetBenchmarkReturn([member_portfolio].[benchmark_id],[member_portfolio_interest].[date]) AS Benchmark FROM...
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Can you please post the complete SQL statement you are using? It will help to find the problem.
0
 

Author Comment

by:tonikgroup
Comment Utility
The  code below is the text string I pass to the MS_SQL_query sub above

This SQL query works fine when I run it within Access, but not when I pass it from Excel through my MS_SQL_query function in Excel.
SELECT member_portfolio_interest.date, member_portfolio_interest.declared_interest, GetBenchmarkReturn(member_portfolio.benchmark_id,member_portfolio_interest.date) AS Benchmark

FROM member_portfolio LEFT JOIN member_portfolio_interest ON member_portfolio.member_portfolio_id = member_portfolio_interest.member_portfolio_id

WHERE (((member_portfolio_interest.date)<=#1/31/2009#) AND ((member_portfolio.member_portfolio_id)=14))

ORDER BY member_portfolio_interest.date DESC;

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
What is the error message you receive or does it just not work?
0
 

Author Comment

by:tonikgroup
Comment Utility
Run-time error '-2147217900 (80040e14)':

Automation error

When in debug mode I get this error at:

' Open recordset table
    rst.Open sql_string, cnt

of the MS_SQL_query above.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I think the issue is the even though it is a "Public Function" -- it is only public to the internal Access DB. When you are trying to execute the code externally to Access -- it has no clue of the GetBenchmarkReturn() function because it isn't local to Excel.

What you will probably have to do is either re-create the module (and link it back to the Access DB) or turn it around to make a predone query with the function as subquery of the main one.

Maybe something like below.
SELECT member_portfolio_interest.date, member_portfolio_interest.declared_interest, 

Bench_Query.BenchMark

FROM member_portfolio folio

     LEFT JOIN member_portfolio_interest folio_int

       ON folio.member_portfolio_id = folio_int.member_portfolio_id

     LEFT JOIN Bench_Query 

       ON folio.member_portfolio_id = Bench_Query.member_portfolio_id  

       AND folio_int.date = Bench_Query 

WHERE folio_int.date<=#1/31/2009#

AND folio.member_portfolio_id = 14

ORDER BY member_portfolio_interest.date DESC;

------------------------------------

Bench_Query 

------------------------------------

SELECT member_portfolio.benchmark_id, member_portfolio_interest.date, GetBenchmarkReturn(member_portfolio.benchmark_id,member_portfolio_interest.date) As BenchMark

FROM member_portfolio folio

     LEFT JOIN member_portfolio_interest folio_int

       ON folio.member_portfolio_id = folio_int.member_portfolio_id

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Any word on this?
0
 

Author Comment

by:tonikgroup
Comment Utility
I'm not sure how one would:
"What you will probably have to do is either re-create the module (and link it back to the Access DB)"

I see that your bench_query solution could work - however the downside is that when ever I want to calculated the benchmark for a particular benchmark_id, the bench_query will run the GetBenchmarkReturn function for all benchmark_id's loaded in the database.

0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
The other thought -- do you have to do it from the Excel side. Could you do it Access and write to the spreadsheet?
0
 

Author Comment

by:tonikgroup
Comment Utility
I would have to do it from within Excel - users embed my tools in their spreadsheets.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
You should be able to recreate the GetBenchmarkReturn in Excel -- just change the DAO stuff to ADO and link to the tables that way.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now