Update pass-through query from a form (an alternative to stored procedures)

Question

I've adapted a script from the book Microsoft Access Developer's Guide to SQL Server that I use to update a pass-through query via a form.

One way to pass parameters to a pass-through query is to modify the QueryDef object's SQL property in code before running it, concatenating the parameter values you want to send.

I've chosen this method, rather than using stored procedures, because I do not have adequate privileges (create function) for creating stored procedures in the Oracle database.

---

I'm familiar with scripting, but I don't pretend to be an expert coder.

I figure I might as well get some input from those who know what they're doing, learn from you, and get off on the right foot. I've heard that non-experts who attempt to program in Access often end-up with these barely working, horribly designed applications that bring dread into the hearts of developers .

Obviously, I'd prefer to avoid that. Here's what I've got:

---

'SPT stands for SQL Pass-through (query). My pass-through query is called sptROAD_INSP
Private Sub cmdSubmit_Click()
 AssembleSptROAD_INSP Me.txtStartYear, Me.txtEndYear
End Sub

---

Option Compare Database
Option Explicit
Public Sub AssembleSptROAD_INSP( _
 intStartYear As Integer, _
 intEndYear As Integer)
 Dim strSQL As String
 Dim strSPTName As String
 strSPTName = "sptROAD_INSP"
 strSQL = "select " _
 & "b.insp_id, " _
 & "b.road_id, " _
 & "b.insp_date, " _
 & "b.Condition " _
 & "from " _
 & "user1.road_insp b " _
 & "where " _
 & "b.insp_date = ( select max(insp_date) from user1.road_insp a " _
 & "where a.road_id = b.road_id " _
 & "and extract(year from insp_date) between " _ 
 & intStartYear & " and " & intEndYear & ");"
 Call ModifyPassThrough(strQdfName:=strSPTName, strSQL:=strSQL)
 DoCmd.Close acQuery, "sptROAD_INSP"
 DoCmd.OpenQuery strSPTName
End Sub

---

Public Sub ModifyPassThrough( _
 ByVal strQdfName As String, _
 ByVal strSQL As String, _
 Optional varConnect As Variant, _
 Optional fRetRecords As Boolean = True)
 ' Modifies pass-through query properties
 ' Inputs:
 ' strQdfName Name of the query
 ' strSQL New SQL string
 ' varConnect Optional connection string
 ' fRetRecords Optional returns records--
 ' defaults to True (Yes)
 Dim db As DAO.Database
 Dim qdf As DAO.QueryDef
 Dim strConnect As String
 Set db = CurrentDb
 Set qdf = db.QueryDefs(strQdfName)
 If IsMissing(varConnect) Then
 strConnect = qdf.Connect
 Else
 strConnect = CStr(varConnect)
 End If
 qdf.Connect = strConnect
 qdf.ReturnsRecords = fRetRecords
 qdf.SQL = strSQL
End Sub

---

What can I do to make this as sustainable and robust as possible?

• krish KM: Which part are you not happy with? building SQL or executing it?

Answer 1

The first thing I would suggest is to avoid as much as possible depending on the DoCmd object. The DoCmd object is essentially replicating the actual user action as if you had clicked on a button via the UI, with all the dialogs, etc. that comes with it.

For the purpose of executing a SQL, I recommend that you use the Database.Execute method, remembering to use dbFailOnError flag and very likely dbSeeChanges if your Oracle tables uses something equivalent to auto-increment for its primary key. So instead of

DoCmd.Close acQuery, "sptROAD_INSP"
DoCmd.OpenQuery strSPTName

You should want to do something like this:

CurrentDb.Execute strSPTName, dbFailOnError Or dbSeeChanges

This does essentially the same thing but without all the extraneous UI baggages that the DoCmd brings to the table. It will NOT prompt you to confirm if you want to make changes, and does NOT require you to use DoCmd.SetWarnings to suppress the unwanted UI messages associated with OpenQuery or RunSQL methods which are member of the DoCmd object.

However, Execute method is only appropriate when it's in fact a statement that is not intended to return results. If you expect results (e.g. the qdf.ReturnRecords is set to true), you would use OpenRecordset method. Therefore, you could revise your function so that it does this (untested aircode):

If qdf.ReturnRecords Then
 Set Results = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
Else
 qdf.Execute dbFailOnError Or dbSeeChanges
End If

assuming that you have a variable named Results that is a `DAO.Recordset type.

Next, you are writing SQL raw. Unfortunately, a passthrough query cannot be properly parameterized. For best protection against SQL injection, you might want to consider using ADO instead of DAO, and if possible use OLEDB provider for Oracle (note that there are multiple providers, some by Oracle themselves, other by Microsoft - I can't say which is better; experimenting is needed). Note that you are not restricted to using ADO exclusively; you can use ADO in VBA code and DAO for Access form binding.

If for some reasons you cannot use ADO and must use DAO, then you should at least consider writing functions to prepare the parameters so that all strings are properly delimited, and that empty values in a textbox will result in a NULL or maybe '', depending on your needs.