Microsoft Access VBA to edit SQL queries

Question

Hi guys,

I have an Access database with a single query in it. I can currently copy the query via VBA, using DoCmd.CopyObject . However, I need to be able to edit the SQL in each instance of the query individually. All the examples I have seen however, involve recordset, which is something I am not using.

Any ideas?

With thanks,

Will

Answer 1

The question is a bit thin on what kind of query you want to edit, but say it is a select query that doesn't need any dynamic parameters.

Then using the CopyObject method make a copy of the query as you did. Use the Catalog object (you will need to reference the ADO Ext). Then you can just change the SQL of the copy as below. Depending on your query it may be a View or a Procedure, but a select query should be listed as a View.

Hope this helps.

Siva

Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Dim sQueryName As String
Dim sSQL As String

Set cn = CurrentProject.Connection
Set catDB = New ADOX.Catalog

catDB.ActiveConnection = cn
DoCmd.CopyObject , "Query1_c", acQuery, "Query1"
sQueryName = "Query1_c"
sSQL = "SELECT a,b,c FROM Table1"

Set cmd = New ADODB.Command
Set cmd = catDB.Views(sQueryName).Command
cmd.CommandText = sSQL
Set catDB.Views(sQueryName).Command = cmd

Set catDB = Nothing
cn.Close

Answer 2

http://bytes.com/topic/access/answers/211550-query-vba

Answer 3

I do this kind of thing all the time! I've used VBA to rewrite Access queries as well as queries that "pass through" to a MySQL database. Rewriting a query in VBA depends on how complicated you want to get.

I've always used DAO, which is older technology (see comments below for clarifcation on DAO vs ADO), but it's worked for me in this instance. You'll need to add a reference to DAO in VBA by going to Tools > References, and then add "Microsoft DAO 3.6 Object Library."

You might write a procedure that looks like this:

Sub RewriteQuerySQL(strQueryName As String, strParameter As String)
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Set db = CurrentDb()
   Set qdf = db.QueryDefs(strQueryName)

   qdf.SQL = "SELECT [Table].Field1, [Table].Field2 " & vbCrLf & _
             "FROM [Table] " & vbCrLf & _
             "WHERE ([Table].Field1 = " & chr(34) & strParameter & chr(34) & ");"
End Sub

The above code is going to change the SQL for the query you specify with the query SQL in the VBA code with the strParameter embedded in the SQL query.

Here's another way of doing it. This is code I used in a sales reporting application to rewrite a query based on a salesperson's number:

Sub rewriteAccountsBySalesRepSortSQL(lngSalesRep As Long)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strOriginal As String
Dim strReplacePart As String
Dim strSQLReplace As String
Dim lngLocSalesperson As Long
Dim lngLocMyRoundUp As Long
Dim lngLocParen As Long
Dim lngLocEndParen As Long

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryS Accounts by Sales Rep Sorted by Diff DESC")
strSQL = qdf.SQL

lngLocSalesperson = InStr(strSQL, "Salesperson1)=")
lngLocEndParen = InStr(lngLocSalesperson + 14, strSQL, ")")
strOriginal = Mid(strSQL, lngLocSalesperson, lngLocEndParen - lngLocSalesperson)
'lngLocParen = InStrRev(strSQL, "(", lngLocSalesperson)

strReplacePart = "Salesperson1)=" & lngSalesRep

strSQLReplace = Replace(strSQL, strOriginal, strReplacePart)
qdf.SQL = strSQLReplace




End Sub

Instead of writing out the whole query, the code simply finds the current sales rep number and replaces it with the new number. I actually prefer the latter method. The latter method was used to send a query directly to MySQL, so the syntax with an Access query may be slightly different.

Answer 4

I would dissent from all the answers given here, even though they do address the question asked. I dissent because I think the question is based on incorrect assumptions -- there is no need to rewrite the SQL at all.

The question seems to assume that queries in Access have to be saved. They don't. You can execute any arbitrary SQL string at any time, either in code, or (for non-DML SQL) as the recordsource of a form or report. The SQL strings can be built on the fly and assigned as needed at runtime -- the only advantage of a saved QueryDef is if you need to use it in multiple locations.

A saved QueryDef is basically the same as a VIEW in server databases.

If the QueryDef has parameters, it's equivalent to a simple STORED PROCEDURE (i.e., ones that lack code, like IF/THEN or CASE SELECT branching).

If you would implement the SQL as a VIEW on a server database, save it as a QueryDef in Access. If you'd do it as an SPROC in your server database, implement it as a saved parameter query.

But first of all, determine if it needs to be saved at all.

For what it's worth, I've been programming professionally in Access since 1996 and I generally don't save a lot of queries, and particularly don't save criteria in queries. Criteria are specific to runtime context and should be supplied at runtime, rather than saved in the QueryDef. I use saved QueryDefs for complex SQL that I need to re-use or for defining "views" (particularly those with complex joins) that are used in more than one place in the app.

The original question does not identify the context in which changing the criteria is needed, so it's really impossible to suggest the best approach. This is a case where I would fault the question for foreclosing proper discussion as it proposes a specific SOLUTION and asks how to implement it, instead of describing the PROBLEM and asking for the range of workable solutions. In order to do the latter, we'd need to know about the context (is the SQL DML or a SELECT? is it being used in code or as the recordsource for a form or report? etc.), but that's completely lacking here, so a full range of solutions is never going to be offered.