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?

Which part are you not happy with? building SQL or executing it? — krish KM
– krish KM, Commented Sep 10, 2018 at 13:40

this · Accepted Answer · 2017-11-06 20:56:09Z

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.

Stack Exchange Network

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

1 Answer 1

You must log in to answer this question.

Hot Network Questions

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

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions