Solved

MSAccess Function within MSSQL query string

Posted on 2009-05-08
15
658 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
15 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24337203
Try this:

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

Author Comment

by:tonikgroup
ID: 24337265
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
ID: 24337384
Can you please post the complete SQL statement you are using? It will help to find the problem.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:tonikgroup
ID: 24337514
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
ID: 24337576
What is the error message you receive or does it just not work?
0
 

Author Comment

by:tonikgroup
ID: 24337628
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 24416515
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.
ID: 24435135
Any word on this?
0
 

Author Comment

by:tonikgroup
ID: 24440782
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.
ID: 24441104
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
ID: 24441367
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
ID: 24441424
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.
ID: 24573027
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 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