Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Modifying an Access Pass through Query using Querydefs

    Hi there!

    I have looked high and low in order to try and resolve this, but am getting nowhere.

    I have a pass through query which I want to add a parameter to the end.

    The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.

    I do not want to retype or try and paste all of this code into a vba module.

    Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?

    A simple way of looking at this could be

    querytemplate:-

    "set nocount off

    Select UWREF
    from policy
    where UWREF like '"

    parameter:- (picked up from a text box on a form)
    "ABC123"

    additional text to follow:-
    "%' set nocount on"

    In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.

    This is driving me nuts as I can only seem to find examples where the code is manually entered!

    Any help pointers would be greatly appreciated as part of the problem here maybe information overload and I can't believe this that difficult to achieve!

    Cheers,

    Niven

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    An approach we have used is to create the Pass Through query in VBA, as you suggest. The following VBA creates such a query
    Code:
    240           Set qrySearch = CurrentDb.QueryDefs("qryMemberSearch")
    250           If Me.chkIncludeDead = True Then
    260           strSQL = "SELECT tblMembers.lngMemberNumber, [strLastName] & ', ' & Trim([strFirstName] & ' ' & [strMiddleName] & ' ' & [strSuffix]) AS fName " & _
                          "FROM tblPeople INNER JOIN tblMembers ON tblPeople.lngPersonID = tblMembers.lngPersonID " & _
                          "WHERE (((tblPeople.strLastName) Like '" & Me.txtLastName & "*') AND ((tblPeople.strFirstName) Like '" & Me.txtFirstName & "*')) " & _
                          "ORDER BY [strLastName] & ', ' & Trim([strFirstName] & ' ' & [strMiddleName] & ' ' & [strSuffix])"
    270           Else
    280               strSQL = "SELECT tblMembers.lngMemberNumber, [strLastName] & ', ' & Trim([strFirstName] & ' ' & [strMiddleName] & ' ' & [strSuffix]) AS fName " & _
                          "FROM tblPeople INNER JOIN tblMembers ON tblPeople.lngPersonID = tblMembers.lngPersonID " & _
                          "WHERE (((tblPeople.dtDeceasedDate) Is Null) AND ((tblPeople.strLastName) Like '" & Me.txtLastName & "*') AND ((tblPeople.strFirstName) Like '" & Me.txtFirstName & "*')) " & _
                          "ORDER BY [strLastName] & ', ' & Trim([strFirstName] & ' ' & [strMiddleName] & ' ' & [strSuffix])"
    290           End If
    300           qrySearch.SQL = strSQL
    310           qrySearch.Close
    However, you should be able to pull in the existing querydef SQL String and edit it to change parameters using VBA as well. Neither approach is very elegant if your query is much more complicated that the example above.

    Another approach is to create a stored procedure - then you can pass parameters to the sproc and have all the T-SQL stored in the SQL Server database. However if you want the process to return data to you, things get more ugly, as the data is read-only. At this point, I would choose to either create a temporary table in SQL Sever that you have an ODBC link to, or have a view that you linke to. Hope this helps.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi WendellB,

    I haven't been on here much recently, so good to see you're still around!

    Many thanks your reply. Looking at my sample say the first set of code:-

    set nocount off

    Select UWREF from policy where UWREF like '

    is in a query template called qrytemplate

    using your example Set qrySearch = CurrentDb.QueryDefs("qryTemplate")
    can I then do something like "qrySearch.SQL = qrySearch (however it works) & [parameter from text box on Form] & "%' set nocount on'"?

    Basically take what goes into qrySearch.SQL and append the rest of the query, which includes the parameter?

    I'm probably being quite dense, but I've got information overload from trawling the net and can't see the wood for the trees.

    The other thing as well is, I don't want to overwrite the template, just call it each time the query is run with the new parameter attached.

    Cheers

    Niven

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you modify the SQL then you will overwrite your template.
    So you need two queries: qryTemplate and another one that gets changed each time, lets say qryAction
    (aircode)
    Dim strSQL as string
    strSQL= currentdb.querydefs("qryTemplate").SQL
    strSQL = SQL & your parameters
    Currentdb.querydefs("qryAction").sql = strSQL
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,

    Many thanks for that snippet of code, I've been able to work out what to do now.

    Incidentally, I don't know if you or WendellB have looked at another post I made - "Pass through queries in Access 2010 Reports", but as you've both been a great help here I wonder if you have any comments to make about it? It's only that a lot of people have looked at it, but no one has commented.

    Cheers,

    Niven )

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Hi Niven

    I did see that post, but I did not reply as I did not have anthing useful to say. I have very little experience of working with SQLServer backends.
    WendellB is much more likely to be able to help you.
    Regards
    John



  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In case anyone else tries to use the aircode I posted earlier, (as I am doing just now) there are a few slight complcations.

    The SQL returned by:
    strSQL= currentdb.querydefs("qryTemplate").SQL
    will have a semicolon at the end, that needs to removed before extra Where conditions can be added. But the ; is not quite at the end. There seem to be some spaces after it, that were not removed with a Trim.

    So I ended up using
    Dim intPosition as integer
    intPosition = instr(1,strSQL,";")
    strSQL = left(strSQL,intPosition-1)

    Another issue to remember is that the template query can't do any sorting. The Where clause must come at the end of the SQL.
    And there needs to be at least one Criteria in the query. If not you would need to add the word "Where" when you add the parameters.
    Regards
    John



  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Niven,

    I have a table I call "tsysSQL". In it I have 2 fields: QueryName (text) and QuerySQL (memo field). I store the SQL for queries here. In the WHERE clause I put "1=1". I read the table for the SQL, replace "1=1" with whatever I want, then update the querydef with the SQL.

    (let's suppose query is something simple like: SELECT tblClient.* FROM tblClient WHERE 1=1)

    Here is an example of how the code might look like:

    dim strQuery as string
    dim strSQL as string

    strQuery="qryGetClients"
    strSQL= getSQLstring(strQuery) 'my own function for reading the table and returning SQL
    If strsql="" then exit sub 'just in case

    strSQL = Replace(strSQL, "1=1", "State='FL' AND ActiveStatus = True")
    currentdb.querydefs(strQuery).sql = strSQL

    That's it! You can of course make the substitution much more complicated than I showed. In some cases I have complicated queries with subqueries, and I may use 2=2, 3=3 etc. in the subqueries.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •