Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Dynamic Stored Procedure in a Pass-thru procedure


    I can run the below Stored Procedure fine with a pass-through query. However, I want to be able to change the parameter of EntityFilterFullNameWithChildren to something other than "Jones". I'd presume I need to create a function and run DoCmd.RunSQL xxx, where I am able to set xxx to below. Can someone create the entire function that would allow me to run a pass-through query along with a stored procedure with the required connection syntax?

    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Jones', SummarizeColumnsBy = 'TotalOnly'

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If you are using the above code in a Module it should be as easy as employing a variable within your string.

    Code:
    dim strSQL as string
    dim strName as string
    strName = "Jones"
    
    strSQL = "sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = '" & strName & "', SummarizeColumnsBy = 'TotalOnly'"

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To use with a pass through query, you'd use the above and a DAO QueryDef and set the pass through query's SQL to the variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Thanks for the quick reply. I guess I was not clear with my request. I'm actually running the stored procedure via ODBC connection to run a pass-through query. Therefore, I need connection instructions. Do you know what that may be?

  5. #5
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Thanks, pbaldy. Your response must have come in when I was composing the last message. I'm not an expert. Do you know how to proceed with the steps of DAO QueryDef and such?

  6. #6
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Quote Originally Posted by LonghronJ View Post
    Thanks, pbaldy. Your response must have come in when I was composing the last message. I'm not an expert. Do you know how to proceed with the steps of DAO QueryDef and such?
    This requires a bit of research, so you'll need to invest in reviewing the DAO QueryDef object structure, and be comfortable using VBA to update strings.

    1. Make a usable pass through query.
    2. Use DAO to access the pass through query's QueryDef
    3. Read the current SQL string from the pass through query's QueryDef to a variable string.
    4. Update the variable to replace the value you want to change.
    5. Write the variable back to the query's QueryDef
    6. Execute the pass through query as normal

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Here is some code that I extracted from one of my DB's. I create custom functions and stuff in order to avoid repeating code. So the example is not exactly how it looks in my DB's.
    Code:
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
                         
        Set db = CurrentDb
        Set qdf = db.CreateQueryDef("")
            
            qdf.Connect = connectionStringODBC
            qdf.SQL = p_SQL
            qdf.ReturnsRecords = False
            qdf.Execute dbFailOnError + dbSeeChanges
    
        Set qdf = Nothing
        Set db = Nothing
    connectionStringODBC is a string literal that = the connection string. So you will need to create that. I usually depend on DSN'less connections and SQL native client driver.
    https://www.accessforums.net/sql-ser...ere-52914.html

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by LonghronJ View Post
    Thanks, pbaldy. Your response must have come in when I was composing the last message. I'm not an expert. Do you know how to proceed with the steps of DAO QueryDef and such?
    Yes I do.

    Most of my apps include a function that does most of the steps described in InsuranceGuy's post. It takes the SQL and the query name as input arguments.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by ItsMe View Post
    Here is some code that I extracted from one of my DB's. I create custom functions and stuff in order to avoid repeating code. So the example is not exactly how it looks in my DB's.
    Code:
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
                         
        Set db = CurrentDb
        Set qdf = db.CreateQueryDef("")
            
            qdf.Connect = connectionStringODBC
            qdf.SQL = p_SQL
            qdf.ReturnsRecords = False
            qdf.Execute dbFailOnError + dbSeeChanges
    
        Set qdf = Nothing
        Set db = Nothing
    When I get to
    Code:
    qdf.SQL = p_SQL
    I get "Pass-through query must contain at least one character. Was I supposed insert/swap out something to pertains my purpose?

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by LonghronJ View Post
    ... Was I supposed insert/swap out something to pertains my purpose?
    Yes. In my code example, p_SQL, is a local variable. This is where the actual query would be.
    Code:
    qdf.SQL = "My SQL Statement"
    I cannot speak for everyone, but I do not know what information to provide to you. Perhaps you do not know of any good questions to ask. The code example is creating a temp query and executing it. It is likely you will need to change the code a little to execute your SQL. For instance, you may want to use qdf.ReturnsRecords = True rather than use qdf.ReturnsRecords = False. Also, you still need to construct your connection string.

    What is the backend you are using? Do you have any linked tables or anything that you can get a connection string from? Can you describe what you have, as far as what your Access file has inside it and what it is connecting to?

  11. #11
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    I thought I had replied, but apparently I didn't. I'm connecting to QuickBooks data file that is a copy and it's residing on my local drive. I believe I have the connection string working, at least it steps through without an error. I have not substituted
    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Jones', SummarizeColumnsBy = 'TotalOnly'
    with "My SQL Statement" as you suggested. Which line tells me to run the query?

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    This is the line that executes.
    qdf.Execute dbFailOnError + dbSeeChanges

    However I would try it without the arguments to start.
    qdf.Execute

    If you are connecting to QuickBooks I believe you have to have a valid QuickBooks install. I think there may be a Sandbox version of QuickBooks available for download/install if you are an Intuit Partner. Support for desktop is becoming more scarce within the Intuit network.

    I have some examples of connecting somewhere but don't have quickbooks in front of me right now to test any snippets.

  13. #13
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    I do have a valid QuickBooks on my desktop. When I step down to qdf.Execute, I get "ODBC--call failed" error message. I changed "qdf.ReturnsRecords = False" to "qdf.ReturnsRecords = True", then I get "Cannot execute a select query". Perhaps I should tackle this a easier way. Right now, when I create a pass-through query through query design with below SQL,
    Code:
    Code:
    sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Jones', SummarizeColumnsBy = 'TotalOnly'
    it works perfectly fine, but the string for EntityFilterFullNameWithChildren is hard coded. I would like to change the parameter dynamically, thus, I'm trying to create a function that runs a pass-through query. Can I just change the SQL to accept a global variable? I tried to pass the variable with a function by changing it to EntityFilterFullNameWithChildren = '" & MyFunction () &"', it returned "ODBC--call failed". Perhaps I'm not concatenating it correctly?

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I do have one question, right now. Regardless of the answer to my question, you can dynamically parameterize a Named and Saved Query Object. For instance, In a SELECT query, you can create criteria that references a control in an open form. you would add this criteria to the grid at the bottom of the query designer window. The syntax would look like ...
    Froms!NameOfForm!NameOfControl

    In the row within the grid, 'Criteria' ... type Forms and hit the Tab Key on your keyboard. Then, begin typing the form name. Type the entire name or use your arrow keys before hitting the tab key. Repeat for the control name.

    So, my question is, "Are you using the third party tool QODBC or something similar"?

  15. #15
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    I'm using MS Access with QODBC, which is a third party driver that supports QuickBooks. I'm familiar with queries and I understand what you were suggesting, but it's my first time using a pass-through query. It only gives me SQL and datagrid view. Therefore, I do not have the rows in the grid to allow me to put in parameters.

Page 1 of 3 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
  •  
Other Forums: Microsoft Office Forums