Results 1 to 3 of 3
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Adding ORDER BY to stored procedure at run time (2000/any)

    I am rewriting a Word VBA routine to use queries stored in an Access .mdb rather than sending the query itself as the CommandText. However, I have run into a snag.

    One of my queries has an ORDER BY clause that is set based on user responses in a dialog; there are four possible combinations (involving date/time and text and numeric filds), and the possibility of not using an ORDER BY clause at all.

    I am having a lot of fun with parameters, but there does not appear to be a way to use a parameter as the ORDER BY criterion without creating a new field or expression, which seems to be more processing trouble than it would save.

    So my options seem to be: (1) create 5 versions of the query in the database; or (2) derive a new query dynamically at run time. The first probably better preserves the advantage of faster execution and simpler code. Is there any reason to explore the second option? If I were going to go that way, how would I retrieve the query to append the ORDER BY clause? Would there be any advantage to writing it back to the database?

    All comments appreciated.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding ORDER BY to stored procedure at run time (2000/any)

    Post your code. If you're using DAO, I think you're going to have to create the SQL and run it from Word, although within the Access application. If you're using ADO, you can actually use a command object to *change* the query in Access. Here's a sample I got from somewhere, either the Code Librarian, or one of my reference books. Since there's no accreditation in it, I assume it was Code Librarian, but I've tweaked the naming, etc..

    <pre>Sub ModifyQuery(strDBPath As String, _
    strQryName As String, _
    strSQL As String, _
    Optional blnRemoteDB As Boolean)

    Dim catDB As ADOX.Catalog
    Dim cmd As ADODB.Command

    <font color=448800>'Sample Call:

    'ModifyQuery "c:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb", _
    ' "Employees by Region", "PARAMETERS prmRegion Text;" _
    ' & "SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City;"

    'Note:

    ' You must use this construction:

    ' Set cmd = catDB.Procedures("Employees by Region").Command
    ' cmd.CommandText = "PARAMETERS prmRegion Text;" & _
    ' "SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City"
    ' Set catDB.Procedures("Employees by Region").Command = cmd

    ' rather than this:

    ' catDB.Procedures("Employees by Region").CommandText = _
    ' "PARAMETERS prmRegion Text;" & _
    ' "SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City" </font color=448800>

    Set catDB = New ADOX.Catalog
    <font color=448800> ' Open the Catalog object.</font color=448800>
    If blnRemoteDB Then
    <font color=448800>' Open the Catalog object on another database.</font color=448800>
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDBPath
    Else
    <font color=448800>' Open the Catalog object on the current database.</font color=448800>
    catDB.ActiveConnection = CurrentProject.Connection
    End If

    Set cmd = New ADODB.Command
    <font color=448800>' Get the query from the Procedures collection.</font color=448800>
    Set cmd = catDB.Procedures(strQryName).Command

    <font color=448800>' Update the query's SQL statement.</font color=448800>
    cmd.CommandText = strSQL

    <font color=448800>'Save the updated query.</font color=448800>
    Set catDB.Procedures(strQryName).Command = cmd

    Set catDB = Nothing
    End Sub</pre>

    Charlotte

  4. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Adding ORDER BY to stored procedure at run time (2000/any)

    My old code used DAO, there was a base query string and then a UserForm would return an ORDER BY clause consisting of three fields in the order desired by the user. I simply concatenated that to the base query and used it in an OpenRecordset call.

    I don't have new code yet, but after reviewing your sample, the closest approach appears to be reading out the current CommandText and concatenating the ORDER BY clause and not even saving it back to the database:

    Dim cmd2 As New ADODB.Command
    cmd2.ActiveConnection = {same as for cmd}
    cmd2.CommandText = cmd.CommandText & strOrderClause
    rst = cmd2.Execute

    But because this would add the overhead of using another type library (ADOX), and mentally juggling more Command objects, I think I'm just going to pre-create several queries in the database These queries won't change over time, so it's not the maintenance headache it might be in a more dynamic application. I'll keep this in mind, though, for future applications where I might not have the flexibility to add my own queries.

Posting Permissions

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