어떻게 Access에서 서로 다른 상황에서 VBA에서 매개 변수를 사용합니까?

나는 SQL 주입에 대해 많이 읽고, bobby-tables.com 같은 소스에서, 매개 변수를 사용했습니다. 그러나, 나는 장소의 모든 종류의에서 문자열 연결과 동적 SQL을 많이 가지고 Access에서 복잡한 응용 프로그램과 함께 일하고 있어요.

그것은 다음과 같은 일 내가 변화를 원하고 오류를 방지하기 위해 매개 변수를 추가하고 나 잭 오코넬과 같은 작은 따옴표로 이름을 처리 할 수 있습니다.

그것은 사용

쿼리는 대부분 다음과 같이 구성되어 있습니다 :

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

쿼리의 서로 다른 종류의 매개 변수를 사용하여 내 옵션은 무엇입니까?

이 질문은 내가 여러 게시물에 매개 변수를 주석을 사용하여 어떻게 자주를 들어, 자원위한 것입니다

해결법

==============================
1.조회에 매개 변수를 사용하는 방법에는 여러 가지가 있습니다. 나는 그들의 대부분에 대한 예제를 제공하기 위해 노력할 것입니다, 그들은 해당하는 곳이다.

조회에 매개 변수를 사용하는 방법에는 여러 가지가 있습니다. 나는 그들의 대부분에 대한 예제를 제공하기 위해 노력할 것입니다, 그들은 해당하는 곳이다.

첫째, 우리는 폼, 보고서 및 도메인 집계 등의 접근에 고유 한 솔루션을 논의 할 것이다. 그런 다음, 우리는 DAO 및 ADO에 대해 이야기 할 것이다.

Access에서, 당신은 직접 SQL 코드에서 폼과 보고서의 컨트롤의 현재의 값을 사용할 수 있습니다. 이 매개 변수에 대한 필요성을 제한합니다.

다음과 같은 방법으로 컨트롤을 참조 할 수 있습니다 :

양식! MyForm을!합니다 MyTextBox 폼의 간단한 제어를위한

양식! 하위 폼의 컨트롤에 대한 MyForm을! MySubform.Form!합니다 MyTextBox

보고서의 컨트롤에 대한 보고서! MyReport를!합니다 MyTextBox

샘플 구현 :
```
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
```
이것은 다음과 같은 용도로 사용할 수 있습니다 :

DoCmd.RunSQL, 일반 (GUI의) 쿼리, 폼 및 보고서의 레코드 원본, 양식 및 보고서 필터, 도메인 집계 DoCmd.OpenForm 및 DoCmd.OpenReport를 사용하는 경우

이것은 다음과 같은 용도로 사용할 수 없습니다 :

DAO 또는 ADODB하여 쿼리 실행할 때 (예를 들면 개구 레코드를 CurrentDb.Execute)

Access에서 TempVars 전 세계적으로 VBA에서 설정하거나 매크로의를 사용할 수있는 사용 가능한 변수입니다. 그들은 여러 쿼리를 다시 사용할 수 있습니다.

샘플 구현 :
```
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
```
ADO와 DAO를 사용할 수 없습니다, 다른 용도로 사용할 수 : TempVars에 대한 가용성 폼과 보고서의 값의 그것과 동일하다.

나는 개체의 개방이 종료되면, TempVars 사용할 수있어 이후, 제어 이름을 참조를 통해 폼이나 보고서를 열 때 매개 변수를 사용 TempVars하는 것이 좋습니다. 나는 양식이나 보고서를 새로 고칠 때 불확실성을 피하기 위해, 모든 폼 또는 보고서에 대해 고유 한 TempVar 이름을 사용하는 것이 좋습니다.

많은 TempVars처럼, 당신은 저장소에 사용자 정의 기능과 정적 변수를 사용하여 값을 검색 할 수 있습니다.

샘플 구현 :
```
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
```
그리고:
```
SetThisDate SomeDateValue ' Will store SomeDateValue in ThisDate.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeDateField] = GetThisDate()"
```
또한, 선택적인 매개 변수를 갖는 단일 기능의 설정과 전용 정적 변수의 값을 얻는 모두 생성 될 수있다 :
```
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
```
값을 설정하려면 :
```
ThisValue "Some text value"
```
값을 얻으려면 :
```
CurrentValue = ThisValue
```
쿼리에서 :
```
ThisValue "SomeText"  ' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"
```
나는 간단한 수 있습니다 있도록 DoCmd.SetParameter의 사용은 오히려 제한됩니다. 그것은 당신이 DoCmd.OpenForm, DoCmd.OpenReport 및 다른 DoCmd 문에서 사용할 수있는 매개 변수를 설정할 수 있습니다,하지만 DoCmd.RunSQL, 필터, DAO 및 ADO로 작업을하지 않습니다.

샘플 구현
```
DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"
```
DAO에서, 우리는 설정 매개 변수를 쿼리를 만들 수 DAO.QueryDef 객체를 사용하여 다음 중 레코드를 열거 나 쿼리를 실행할 수 있습니다. 먼저 다음 매개 변수를 설정 QueryDef.Parameters 모음을 사용하여 쿼리 'SQL을 설정합니다.

내 예제에서는 암시 적 매개 변수 유형을 사용하는거야. 당신이 그 (것)들을 명시 적으로 확인하려면 쿼리에 매개 변수 선언을 추가합니다.

샘플 구현
```
'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
```
이 DAO에서만 사용할 수 있지만, 당신은 그들이 같은 형태의 레코드, 목록 상자 레코드와 콤보 상자 레코드와 같은 매개 변수를 사용하기 위해 DAO 레코드 집합에 많은 일을 설정할 수 있습니다. 액세스 텍스트, 그리고 레코드를 사용하기 때문에 정렬 및 필터링 할 때 당신이 할 경우, 그 상황이 문제가 될 수 있습니다.

당신은의 ADODB.Command 개체를 사용하여 ADO에서 매개 변수를 사용할 수 있습니다. 사용 Command.CreateParameter 매개 변수를 만든 다음 Command.Parameters의 컬렉션에 추가합니다.

명시 적으로 매개 변수를 선언하는 ADO에서 또한 .Parameters 모음을 사용하거나 암시 적으로 매개 변수를 전달에 Command.Execute 방법에 매개 변수 배열을 전달할 수 있습니다.

ADO는 명명 된 매개 변수를 지원하지 않습니다. 당신이 이름을 통과 할 수 있지만,이 처리되지 것.

샘플 구현 :
```
'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
```
개구 DAO의 레코드와 동일한 제한이 적용된다. 이 방법은 쿼리 및 열기 레코드 집합을 실행에 국한되는 동안, 당신은 당신의 응용 프로그램의 다른 곳에서 그 레코드를 사용할 수 있습니다.

==============================

2.나는 문자열 연결의 혼란을 해결하기 위해 명명 된 매개 변수의 부족을 처리하기 위해 상당히 기본적인 쿼리 빌더 클래스를 구축했다. 쿼리를 생성하는 것은 매우 간단하다.

나는 문자열 연결의 혼란을 해결하기 위해 명명 된 매개 변수의 부족을 처리하기 위해 상당히 기본적인 쿼리 빌더 클래스를 구축했다. 쿼리를 생성하는 것은 매우 간단하다.

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", 9999.99@
        .AddDateParameter "StartDate", #3/29/2018#

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

    End With

End Function

추천 toString () 메서드 외모의 출력 :

각 술어는 연결 및 / 또는 절을 처리하기 위해 괄호에 싸여와 같은 이름을 가진 매개 변수는 한 번만 선언 할 필요가있다. 전체 코드는 내 GitHub의에 있으며 아래의 재현. 또한 ADODB 매개 변수를 사용하여 오라클 통과 쿼리에 대한 버전이있다. 결국, 나는 IQueryBuilder 인터페이스 모두를 포장하고 싶습니다.

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

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

from https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 무엇 T-SQL 문에서 접두사 N의 의미는 무엇이고 언제 사용해야합니까? (0)	2020.03.09
[SQL] 어떻게 SQL 서버에서 중복 행을 삭제하려면? (0)	2020.03.09
[SQL] MySQL은 : ORDER BY RAND 대안 () (0)	2020.03.09
[SQL] 어떻게 카테고리 별 최신 네 개의 항목을 선택하려면? (0)	2020.03.09
[SQL] SQL 다중 열 정렬 (0)	2020.03.09

복붙노트

[SQL] 어떻게 Access에서 서로 다른 상황에서 VBA에서 매개 변수를 사용합니까?

어떻게 Access에서 서로 다른 상황에서 VBA에서 매개 변수를 사용합니까?

해결법

1.조회에 매개 변수를 사용하는 방법에는 여러 가지가 있습니다. 나는 그들의 대부분에 대한 예제를 제공하기 위해 노력할 것입니다, 그들은 해당하는 곳이다.

2.나는 문자열 연결의 혼란을 해결하기 위해 명명 된 매개 변수의 부족을 처리하기 위해 상당히 기본적인 쿼리 빌더 클래스를 구축했다. 쿼리를 생성하는 것은 매우 간단하다.

'SQL' 카테고리의 다른 글

티스토리툴바