webentwicklung-frage-antwort-db.com.de

Wie verwende ich Parameter in VBA in den verschiedenen Kontexten in Microsoft Access?

Ich habe viel über SQL-Injection und die Verwendung von Parametern aus Quellen wie bobby-tables.com gelesen. Ich arbeite jedoch mit einer komplexen Anwendung in Access, die viel dynamisches SQL mit Zeichenfolgenverkettung an allen möglichen Stellen enthält.

Die folgenden Dinge möchte ich ändern und Parameter hinzufügen, um Fehler zu vermeiden und Namen mit einfachen Anführungszeichen wie Jack O'Connel zu behandeln.

Es verwendet:

  • DoCmd.RunSQL zum Ausführen von SQL-Befehlen
  • DAO-Recordsets
  • ADODB-Recordsets
  • Formulare und Berichte, die mit DoCmd.OpenForm und DoCmd.OpenReport unter Verwendung der Zeichenfolgenverkettung im Argument WhereCondition geöffnet wurden
  • Domänenaggregate wie DLookUp, die Zeichenfolgenverkettung verwenden

Die Abfragen sind meistens so aufgebaut:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = " & Me.SomeTextbox

Welche Möglichkeiten habe ich, um Parameter für diese verschiedenen Arten von Abfragen zu verwenden?

Diese Frage ist als Ressource für den häufigen wie verwende ich Parameter Kommentar zu verschiedenen Beiträgen gedacht

9
Erik A

Es gibt viele Möglichkeiten, Parameter in Abfragen zu verwenden. Ich werde versuchen, Beispiele für die meisten von ihnen anzuführen und wo sie anwendbar sind.

Zuerst werden die Lösungen erläutert, die für Access spezifisch sind, wie z. B. Formulare, Berichte und Domänenaggregate. Dann sprechen wir über DAO und ADO.


Verwenden von Werten aus Formularen und Berichten als Parameter

In Access können Sie den aktuellen Wert von Steuerelementen in Formularen und Berichten in Ihrem SQL-Code direkt verwenden. Dies begrenzt den Bedarf an Parametern.

Sie können auf die Steuerelemente folgendermaßen verweisen:

Forms!MyForm!MyTextbox für ein einfaches Steuerelement in einem Formular

Forms!MyForm!MySubform.Form!MyTextbox für ein Steuerelement in einem Unterformular

Reports!MyReport!MyTextbox für ein Steuerelement in einem Bericht

Beispielimplementierung:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Forms!MyForm!MyTextbox" 'Inserts a single value
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = Forms!MyForm!MyTextbox" 'Inserts from a different table

Dies ist für folgende Zwecke verfügbar:

Bei Verwendung von DoCmd.RunSQL können normale Abfragen (in der GUI), Datensatzquellen für Formulare und Berichte, Formular- und Berichtsfilter, Domänenaggregate, DoCmd.OpenForm und DoCmd.OpenReport verwendet werden.

Dies ist nicht für die folgenden Zwecke verfügbar:

Beim Ausführen von Abfragen mit DAO oder ADODB (z. B. Öffnen von Recordsets, CurrentDb.Execute)


Verwenden von TempVars als Parameter

TempVars in Access sind global verfügbare Variablen, die in VBA oder über Makros festgelegt werden können. Sie können für mehrere Abfragen wiederverwendet werden.

Beispielimplementierung:

TempVars!MyTempVar = Me.MyTextbox.Value 'Note: .Value is required
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = TempVars!MyTempVar"
TempVars.Remove "MyTempVar" 'Unset TempVar when you're done using it

Die Verfügbarkeit für TempVars ist identisch mit der von Werten aus Formularen und Berichten: Nicht verfügbar für ADO und DAO, verfügbar für andere Zwecke.

Ich empfehle TempVars für die Verwendung von Parametern beim Öffnen von Formularen oder Berichten zum Verweisen auf Steuerelementnamen, da die TempVars verfügbar bleiben, wenn das Objekt, das es öffnet, geschlossen wird. Ich empfehle, für jedes Formular oder jeden Bericht eindeutige TempVar-Namen zu verwenden, um Unregelmäßigkeiten beim Aktualisieren von Formularen oder Berichten zu vermeiden. 


Benutzerdefinierte Funktionen (UDFs) als Parameter verwenden

Ähnlich wie bei TempVars können Sie eine benutzerdefinierte Funktion und statische Variablen verwenden, um Werte zu speichern und abzurufen.

Beispielimplementierung:

Option Compare Database
Option Explicit

Private ThisDate As Date


Public Function GetThisDate() As Date
    If ThisDate = #12:00:00 AM# Then
        ' Set default value.
        ThisDate = Date
    End If 
    GetThisDate = ThisDate
End Function


Public Function SetThisDate(ByVal NewDate As Date) As Date
    ThisDate = NewDate
    SetThisDate = ThisDate
End Function

und dann:

SetThisDate SomeDateValue ' Will store SomeDateValue in ThisDate.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeDateField] = GetThisDate()"

Außerdem kann eine single - Funktion mit einem optional -Parameter erstellt werden, um sowohl den Wert einer privaten statischen Variablen festzulegen als auch den Wert einer privaten statischen Variablen abzurufen:

Public Function ThisValue(Optional ByVal Value As Variant) As Variant
    Static CurrentValue As Variant
    ' Define default return value.
    Const DefaultValue  As Variant = Null

    If Not IsMissing(Value) Then
        ' Set value.
        CurrentValue = Value
    ElseIf IsEmpty(CurrentValue) Then
        ' Set default value
        CurrentValue = DefaultValue
    End If
    ' Return value.
    ThisValue = CurrentValue
End Function

So legen Sie einen Wert fest:

ThisValue "Some text value"

Um den Wert zu erhalten:

CurrentValue = ThisValue

In einer Abfrage:

ThisValue "SomeText"  ' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"

Verwenden von DoCmd.SetParameter

Die Verwendung von DoCmd.SetParameter ist eher begrenzt, daher werde ich mich kurz fassen. Sie können einen Parameter für die Verwendung in DoCmd.OpenForm, DoCmd.OpenReport und einigen anderen DoCmd-Anweisungen festlegen, er funktioniert jedoch nicht mit DoCmd.RunSQL, Filtern, DAO und ADO.

Beispielimplementierung

DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"

DAO verwenden

In DAO können wir das DAO.QueryDef-Objekt verwenden, um eine Abfrage zu erstellen, Parameter festzulegen und dann ein Recordset zu öffnen oder die Abfrage auszuführen. Sie legen zunächst die SQL der Abfragen fest und verwenden dann die QueryDef.Parameters-Sammlung, um die Parameter festzulegen.

In meinem Beispiel werde ich implizite Parametertypen verwenden. Wenn Sie sie explizit machen möchten, fügen Sie Ihrer Abfrage eine PARAMETERS-Deklaration hinzu.

Beispielimplementierung

'Execute query, unnamed parameters
With CurrentDb.CreateQueryDef("", "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ?p1 And Field2 = ?p2")
    .Parameters(0) = Me.Field1
    .Parameters(1) = Me.Field2
    .Execute
End With

'Open recordset, named parameters
Dim rs As DAO.Recordset
With CurrentDb.CreateQueryDef("", "SELECT Field1 FROM Table2 WHERE Field1 = FirstParameter And Field2 = SecondParameter")
    .Parameters!FirstParameter = Me.Field1 'Bang notation
    .Parameters("SecondParameter").Value = Me.Field2 'More explicit notation
    Set rs = .OpenRecordset
End With

Während dies nur in DAO verfügbar ist, können Sie viele Optionen für DAO-Recordsets festlegen, damit diese Parameter verwenden, z. B. Formular-Recordsets, Listbox-Recordsets und Combo-Box-Recordsets. Da Access jedoch beim Sortieren und Filtern den Text und nicht das Recordset verwendet, können sich diese Dinge als problematisch erweisen.


ADO verwenden

Sie können Parameter in ADO verwenden, indem Sie das ADODB.Command-Objekt verwenden. Verwenden Sie Command.CreateParameter, um Parameter zu erstellen, und hängen Sie sie an die Command.Parameters-Auflistung an.

Sie können die .Parameters-Sammlung in ADO verwenden, um Parameter explizit zu deklarieren, oder ein Parameterarray an die Command.Execute-Methode übergeben, um Parameter implizit zu übergeben.

ADO unterstützt keine benannten Parameter. Während Sie einen Namen übergeben können, wird er nicht verarbeitet.

Beispielimplementierung:

'Execute query, unnamed parameters
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?"
    .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) 'adVarWChar for text boxes that may contain unicode
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, 8, Me.Field2) 'adInteger for whole numbers (long or integer)
    .Execute
End With

'Open recordset, implicit parameters
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "SELECT Field1 FROM Table2 WHERE Field1 = @FirstParameter And Field2 = @SecondParameter"
     Set rs = .Execute(,Array(Me.Field1, Me.Field2))
End With

Es gelten die gleichen Einschränkungen wie beim Öffnen von DAO-Recordsets. Diese Methode ist zwar auf das Ausführen von Abfragen und das Öffnen von Recordsets beschränkt, Sie können diese Recordsets jedoch an anderer Stelle in Ihrer Anwendung verwenden.

20
Erik A

Ich habe eine ziemlich einfache Abfrage-Builder-Klasse erstellt, um das Durcheinander der String-Verkettung zu umgehen und das Fehlen benannter Parameter zu behandeln. Das Erstellen einer Abfrage ist ziemlich einfach.

Public Function GetQuery() As String

    With New MSAccessQueryBuilder
        .QueryBody = "SELECT * FROM tblEmployees"

        .AddPredicate "StartDate > @StartDate OR StatusChangeDate > @StartDate"
        .AddPredicate "StatusIndicator IN (@Active, @LeaveOfAbsence) OR Grade > @Grade"
        .AddPredicate "Salary > @SalaryThreshhold"
        .AddPredicate "Retired = @IsRetired"

        .AddStringParameter "Active", "A"
        .AddLongParameter "Grade", 10
        .AddBooleanParameter "IsRetired", False
        .AddStringParameter "LeaveOfAbsence", "L"
        .AddCurrencyParameter "SalaryThreshhold", [email protected]
        .AddDateParameter "StartDate", #3/29/2018#

        .QueryFooter = "ORDER BY ID ASC"
        GetQuery = .ToString

    End With

End Function

Die Ausgabe der ToString () -Methode sieht folgendermaßen aus:

SELECT * FROM tblEmployees WHERE 1 = 1 AND (StartDate> # 3/29/2018 # OR StatusChangeDate> # 3/29/2018 #) AND (StatusIndicator IN ('A', 'L') OR Grade> 10) AND (Gehalt> 9999.99) AND (Retired = False) ORDER BY ID ASC;

Jedes Prädikat wird in Parens eingeschlossen, um verknüpfte AND/OR-Klauseln zu behandeln, und Parameter mit demselben Namen müssen nur einmal deklariert werden. Den vollständigen Code finden Sie unter my github und unten wiedergegeben. Ich habe auch eine version für Oracle Passthrough-Abfragen, die ADODB-Parameter verwenden. Schließlich möchte ich beide in eine IQueryBuilder-Schnittstelle einschließen.


VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "MSAccessQueryBuilder"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
'@Folder("VBALibrary.Data")
'@Description("Provides tools to construct Microsoft Access SQL statements containing predicates and parameters.")

Option Explicit

Private Const mlngErrorNumber As Long = vbObjectError + 513
Private Const mstrClassName As String = "MSAccessQueryBuilder"
Private Const mstrParameterExistsErrorMessage As String = "A parameter with this name has already been added to the Parameters dictionary."

Private Type TSqlBuilder
    QueryBody As String
    QueryFooter As String
End Type

Private mobjParameters As Object
Private mobjPredicates As Collection
Private this As TSqlBuilder


' =============================================================================
' CONSTRUCTOR / DESTRUCTOR
' =============================================================================

Private Sub Class_Initialize()
    Set mobjParameters = CreateObject("Scripting.Dictionary")
    Set mobjPredicates = New Collection
End Sub


' =============================================================================
' PROPERTIES
' =============================================================================

'@Description("Gets or sets the query statement (SELECT, INSERT, UPDATE, DELETE), exclusive of any predicates.")
Public Property Get QueryBody() As String
    QueryBody = this.QueryBody
End Property
Public Property Let QueryBody(ByVal Value As String)
    this.QueryBody = Value
End Property

'@Description("Gets or sets post-predicate query statements (e.g., GROUP BY, ORDER BY).")
Public Property Get QueryFooter() As String
    QueryFooter = this.QueryFooter
End Property
Public Property Let QueryFooter(ByVal Value As String)
    this.QueryFooter = Value
End Property


' =============================================================================
' PUBLIC METHODS
' =============================================================================

'@Description("Maps a boolean parameter and its value to the query builder.")
'@Param("strName: The parameter's name.")
'@Param("blnValue: The parameter's value.")
Public Sub AddBooleanParameter(ByVal strName As String, ByVal blnValue As Boolean)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddBooleanParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, CStr(blnValue)
    End If
End Sub

' =============================================================================

'@Description("Maps a currency parameter and its value to the query builder.")
'@Param("strName: The parameter's name.")
'@Param("curValue: The parameter's value.")
Public Sub AddCurrencyParameter(ByVal strName As String, ByVal curValue As Currency)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddCurrencyParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, CStr(curValue)
    End If
End Sub

' =============================================================================

'@Description("Maps a date parameter and its value to the query builder.")
'@Param("strName: The parameter's name.")
'@Param("dtmValue: The parameter's value.")
Public Sub AddDateParameter(ByVal strName As String, ByVal dtmValue As Date)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddDateParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, "#" & CStr(dtmValue) & "#"
    End If
End Sub

' =============================================================================

'@Description("Maps a long parameter and its value to the query builder.")
'@Param("strName: The parameter's name.")
'@Param("lngValue: The parameter's value.")
Public Sub AddLongParameter(ByVal strName As String, ByVal lngValue As Long)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddNumericParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, CStr(lngValue)
    End If
End Sub

' =============================================================================

'@Description("Adds a predicate to the query's WHERE criteria.")
'@Param("strPredicate: The predicate text to be added.")
Public Sub AddPredicate(ByVal strPredicate As String)
    mobjPredicates.Add "(" & strPredicate & ")"
End Sub

' =============================================================================

'@Description("Maps a string parameter and its value to the query builder.")
'@Param("strName: The parameter's name.")
'@Param("strValue: The parameter's value.")
Public Sub AddStringParameter(ByVal strName As String, ByVal strValue As String)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddStringParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, "'" & strValue & "'"
    End If
End Sub

' =============================================================================

'@Description("Parses the query, its predicates, and any parameter values, and outputs an SQL statement.")
'@Returns("A string containing the parsed query.")
Public Function ToString() As String

Dim strPredicatesWithValues As String

    Const strErrorSource As String = "QueryBuilder.ToString"

    If this.QueryBody = vbNullString Then
        Err.Raise mlngErrorNumber, strErrorSource, "No query body is currently defined. Unable to build valid SQL."
    End If
    ToString = this.QueryBody

    strPredicatesWithValues = ReplaceParametersWithValues(GetPredicatesText)
    EnsureParametersHaveValues strPredicatesWithValues

    If Not strPredicatesWithValues = vbNullString Then
        ToString = ToString & " " & strPredicatesWithValues
    End If

    If Not this.QueryFooter = vbNullString Then
        ToString = ToString & " " & this.QueryFooter & ";"
    End If

End Function


' =============================================================================
' PRIVATE METHODS
' =============================================================================

'@Description("Ensures that all parameters defined in the query have been provided a value.")
'@Param("strQueryText: The query text to verify.")
Private Sub EnsureParametersHaveValues(ByVal strQueryText As String)

Dim strUnmatchedParameter As String
Dim lngMatchedPoisition As Long
Dim lngWordEndPosition As Long

    Const strProcedureName As String = "EnsureParametersHaveValues"

    lngMatchedPoisition = InStr(1, strQueryText, "@", vbTextCompare)
    If lngMatchedPoisition <> 0 Then
        lngWordEndPosition = InStr(lngMatchedPoisition, strQueryText, Space$(1), vbTextCompare)
        strUnmatchedParameter = Mid$(strQueryText, lngMatchedPoisition, lngWordEndPosition - lngMatchedPoisition)
    End If

    If Not strUnmatchedParameter = vbNullString Then
        Err.Raise mlngErrorNumber, mstrClassName & "." & strProcedureName, "Parameter " & strUnmatchedParameter & " has not been provided a value."
    End If

End Sub

' =============================================================================

'@Description("Combines each predicate in the predicates collection into a single string statement.")
'@Returns("A string containing the text of all predicates added to the query builder.")
Private Function GetPredicatesText() As String

Dim strPredicates As String
Dim vntPredicate As Variant

    If mobjPredicates.Count > 0 Then
        strPredicates = "WHERE 1 = 1"
        For Each vntPredicate In mobjPredicates
            strPredicates = strPredicates & " AND " & CStr(vntPredicate)
        Next vntPredicate
    End If

    GetPredicatesText = strPredicates

End Function

' =============================================================================

'@Description("Replaces parameters in the predicates statements with their provided values.")
'@Param("strPredicates: The text of the query's predicates.")
'@Returns("A string containing the predicates text with its parameters replaces by their provided values.")
Private Function ReplaceParametersWithValues(ByVal strPredicates As String) As String

Dim vntKey As Variant
Dim strParameterName As String
Dim strParameterValue As String
Dim strPredicatesWithValues As String

    Const strProcedureName As String = "ReplaceParametersWithValues"

    strPredicatesWithValues = strPredicates
    For Each vntKey In mobjParameters.Keys
        strParameterName = CStr(vntKey)
        strParameterValue = CStr(mobjParameters(vntKey))

        If InStr(1, strPredicatesWithValues, "@" & strParameterName, vbTextCompare) = 0 Then
            Err.Raise mlngErrorNumber, mstrClassName & "." & strProcedureName, "Parameter " & strParameterName & " was not found in the query."
        Else
            strPredicatesWithValues = Replace(strPredicatesWithValues, "@" & strParameterName, strParameterValue, 1, -1, vbTextCompare)
        End If
    Next vntKey

    ReplaceParametersWithValues = strPredicatesWithValues

End Function

' =============================================================================
1
FolkCoder