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

[jscher2000]

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.

[charlotte]

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>

[jscher2000]

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.