Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 34

Dynamic Stored Procedure in a Pass-thru procedure

  1. #16
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    OK, let me think about this. I need to take care of a couple business issues first. QODBC has its own way of communicating and I do not understand all of their API's. Actually, unless it is totally intuitive (no pun intended) I will not know what the heck. But, I believe I understand the hurdle you are faced with. Just not sure what the solution is at this moment.

  2. #17
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Okay, I read through the thread again to make sure I had a good understanding of what is going on. I have a couple of questions still. However, I think you should be able to get what you need by creating a Query Object. At least, you can get the query object built and then decide if you want to use DAO, later.
    You want a pass through SELECT query that uses an API offerd by QODBC. Here is an example of a standard SELECT query that you can paste into SQL view of a Pass Through. As long as there is a public function named MyFunction, you should be able to save it.
    Code:
    SELECT tblEquip.EquipDesc
    FROM tblEquip
    WHERE ((tblEquip.EquipSize)=MyFunction());
    I know this is not what you are after, exactly. But look at the syntax. It would be this way for number types or text types. Put the qualifiers in the function, if needed.
    MyFunction = "'Some text'"

    Now, see if Access gets mad if you paste your SQL into a pass through. So something like


    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren =MyFunction1(), SummarizeColumnsBy =MyFunction2()

  3. #18
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    146
    That's exactly what I did earlier and I received "ODBC--call failed...[QODBC] Unexpected extra token: ((#11017)", so I thought it may have been an issue with concatenating strings.

  4. #19
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    That error looks like a connection string error to me. I could be wrong, though. Did you adjust the ODBC Connection Str property of your new query? Maybe you can look at a linked table's Description property as an example.

    You mentioned you, previously, created a query that worked. Was this query a Pass Through?

  5. #20
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    146
    Yes, the past-through query worked. I just wanted to make it dynamic with some parameters changed, hence I started this thread Below is the SQL.
    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Jones', SummarizeColumnsBy = 'TotalOnly'
    Where the 'Jones' string, I want that to be dynamic like a function.

  6. #21
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    If you can use this in a query object
    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Jones', SummarizeColumnsBy = 'TotalOnly'
    Then you should be able to replace the SQL with this
    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren =MyFunction1(), SummarizeColumnsBy =MyFunction2()
    If you get an error it is because you do not have a function named MyFunction1 and or a function named MyFunction2. Another possibility would be a type mismatch or Null. So if you check the value of your function in the immediate window you should see the single quotes.
    Code:
    ?MyFunction2
    'TotalOnly'

  7. #22
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    146
    Unfortunately I have already tried all your suggestions. When I type in ?MyFunction1() in the Immediate Window, it returns "Jones" (without the quotes), so I know the function is returning correctly, but when I insert the function into the SQL, I get the error message. I thought perhaps I need to add the single quotes in the string so when MyFunction1() "'Jones'", it still doesn't work.

  8. #23
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,782
    I think the direction you wanted was addressed in posts 3 & 6. ItsMe in posts 2 & 7 has posted the code, but the only parts you really want are the parts that create the SQL variable and set the QueryDef SQL property to that variable (IOW, you don't want to execute it, just change its SQL). Then, do whatever you normally do with the pass through.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #24
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    I may have derailed the thread, but I think the fact that LonghronJ is unable to create a query object is an important point to consider. As for the Execute thing, yes, that is not necessary. I typically use it when running stored procedures and did not take the extra time to correct that issue when I grabbed an example. Hopefully, it is not a distraction at this point.

  10. #25
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,782
    The suggestion in post 17/21 would also not work (unless Quickbooks works differently than SQL Server). The nature of a pass through query is that processing happens on the back end. Passing SQL that included a VBA function call would make it barf. You could certainly use the function while building the SQL for a variable, but the finished SQL can't include it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #26
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    Yeah, as I was driving in traffic, it finally dawned on me why the user defined function was not working within the query object.

    I started down the query object path when I couldn't figure out what to do with the return records property in DAO. I am guessing the execute line is what derailed things.

    According to the QODBC website, that call is a simple SELECT query with a WHERE clause. Naming a temp query def for use in a procedure and then deleting it may be the answer.

  12. #27
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,782
    I've never tried to connect to Quickbooks, but it sounds reasonable (a client just asked about it, so I may be trying to learn how soon). I use SQL Server a lot, and I typically have one or more saved pass-through queries that I just manipulate the SQL of. I like that better than creating/deleting them. Can't say why, it just feels more efficient.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #28
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    @LonghronJ
    The easiest way to approach this is to edit the SQL of an existing query object via VBA and DAO. So the first thing you need to do is create a Query Object using
    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Jones', SummarizeColumnsBy = 'TotalOnly'
    We will call this query, qryCost. With that, you can use code to edit the SQL. The following code uses two local variables that can be replaced with a function or whatever.
    Code:
    Dim strSQL As String
    Dim strName As String
    Dim strSummary As String
    strName = "Jones"
    strSummary = "TotalOnly"
    strSQL = "sp_report Actuals show AmountActualCost_Title, " & _
             "AmountActualRevenue_Title, " & _
             "AmountDifferenceActual_Title, " & _
             "Text, " & _
             "Label, " & _
             "AmountActualCost, " & _
             "AmountActualRevenue, " & _
             "AmountDifferenceActual " & _
             "parameters DateMacro = 'All', " & _
             "EntityFilterFullNameWithChildren = '" & strName & "'," & _
             "SummarizeColumnsBy = '" & strSummary & "'"
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
                     
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryCost")
        qdf.SQL = strSQL
        
        'Do stuff here with your edited query object.
        
    Set qdf = Nothing
    Set db = Nothing

  14. #29
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    146
    What do I put in 'Do stuff here with your edited query object? I want to run the query. Does "qdf.SQL = strSQL" not run the query of the strSQL, or do I need something like .execute to run the query.

  15. #30
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    The execute thing would be for an Action query. For instance, you want to UPDATE or Append stuff. This can be a common thing for stored procedures. In your case, you are retrieving data without editing anything or appending anything. So, instead of executing a query, you would use the query object as a RecordSource for something.

    One way might be to use the query as a recordsource of a report object.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. write stored procedure with 'if else'
    By shital in forum Access
    Replies: 9
    Last Post: 04-11-2018, 11:47 AM
  2. Replies: 13
    Last Post: 01-30-2013, 03:05 PM
  3. Replies: 4
    Last Post: 01-03-2012, 08:11 PM
  4. Replies: 0
    Last Post: 05-12-2010, 09:41 AM
  5. Pass image parameter to stored procedure
    By Kencao in forum Programming
    Replies: 3
    Last Post: 04-28-2010, 11:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums