Access中的当前作用域中存在重复声明错误

Question

我正在编写VBA代码，以便在记录集循环中逐个提交记录。遇到这个错误说有一个"；当前作用域中的重复声明"；。我以前偶然遇到过重复的变量，但这次我不明白为什么会发生这种情况。我想这可能与脚本的布局有关，也许它试图重复声明它？也许我可以通过在模块中声明变量来解决这个问题？不确定。

Private Sub submitButton_Click()
Const VmfgConnStr = "Connection string is here"
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("")
Dim sqlString As String
sqlString = "INSERT INTO dbo.TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS, COMPETENCY, APPROVED, TYPE) " & _
"SELECT '" & rst![EMPLOYEE_ID] & "', '" & rst![DOCUMENT_ID] & "', '" & rst![LATEST_REV] & "', '" & dtTrained & "', '" & sprTrained & "', 'T', 'Not Verified', 'NO', 'Internal'"
Set objAD = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objAD.UserName)
strDisplayName = objUser.DisplayName
Dim dtTrainedMsg As String
Dim sprTrainedMsg As String
Dim rst As Recordset
dtTrained = InputBox("Enter date trained as 'mm/dd/yyyy':", "", Format(Date, "mm/dd/yyyy"))
Debug.Print dtTrained
If StrPtr(dtTrained) = 0 Then
Exit Sub
Else
sprTrained = InputBox("Trained By:", "", strDisplayName)
Debug.Print sprTrained
If StrPtr(sprTrained) = 0 Then
Exit Sub
Else
Dim ConfirmMsg, ConfirmStyle, ConfirmTitle, ConfirmResponse
ConfirmMsg = "Continue?"
ConfirmStyle = vbYesNo
ConfirmTitle = " "
ConfirmResponse = MsgBox(ConfirmMsg, ConfirmStyle, ConfirmTitle)
If ConfirmResponse = vbYes Then
recSelect = "SELECT EMPLOYEE_ALL.EMPLOYEE_ID, TRAINING_DOCS_ALL.DOCUMENT_ID, TRAINING_DOCS_ALL.LATEST_REV " & _
"FROM TRAINING_DOCS_ALL, EMPLOYEE_ALL " & _
"WHERE EMPLOYEE_ALL.SELECTED = -1 AND TRAINING_DOCS_ALL.SELECTED = -1"
Set rst = CurrentDb.OpenRecordset(recSelect)
rst.MoveFirst
Do Until rst.EOF
Debug.Print rst![EMPLOYEE_NAME]; rst![DOCUMENT_ID]

qdf.sql = sqlString

qdf.ReturnsRecords = False

qdf.Connect = VmfgConnStr

qdf.Execute

rst.MoveNext
Loop
CurrentDb.Execute "DELETE * FROM TRAINING_RECORDS"
CurrentDb.Execute "INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS) " & _
"SELECT * FROM uSysTRAINING_RECORDS " & _
"WHERE EMPLOYEE_ID = '" & EMPLOYEE_ID.Value & "'"
CurrentDb.Execute "DELETE FROM TRAINING_NEEDED " & _
"WHERE EMPLOYEE_ID LIKE '" & EMPLOYEE_ID.Value & "' AND DOCUMENT_ID LIKE '" & DOCUMENT_ID.Value & "'"
Else
End If
End If
End If
End Sub

Answer 1

考虑VBA和SQL的最佳实践，特别是MS Access：

VBA提示

1. 在每个模块的顶部使用Option Explicit。事实上，在IDE(Tools\Options\Require Variable Declaration(中将其设置为全局设置，该设置在所有VBA工程中声明行。如果变量在其相应的Dim调用之前使用，则此选项将引发编译错误，因为发布的代码中出现了许多实例。

   与SQL的词法顺序不同于逻辑顺序(即，第一个编写的子句SELECT通常是最后一个运行的子句(，VBA与许多其他语言一样，按照行的编写顺序运行代码。因此，Dim必须位于Set或=赋值之前。

2. 与上述内容相关，将所有Dim调用作为顶层行放在任何应用程序代码之前。这样读起来更好，并且避免了在使用前声明变量的问题。

   Const VmfgConnStr = "Connection string is here"
   Dim rst As Recordset
   Dim qdf As QueryDef
   Dim recSelect As String, sqlString As String
   Dim dtTrainedMsg As String, dtTrained As String
   Dim sprTrainedMsg As String, sprTrained As String
   Dim ConfirmMsg As String, ConfirmStyleAs String 
   Dim ConfirmTitle As String, ConfirmResponse As String
   Dim objAd, objUser As Object
   Dim strDisplayName As String
   Dim Employee_ID_value As String, DOCUMENT_ID_value As String
   

3. 使用任何块(如If、With、For、Do等(一致地缩进代码。这有助于可读性和可维护性。

   If StrPtr(dtTrained) = 0 Then
   ...
   Else
   ...        
   If StrPtr(sprTrained) = 0 Then
   ...
   Else
   ...        
   If ConfirmResponse = vbYes Then        
   ...
   Do Until rst.EOF   ' REDUNDANT WITH insert-select
   ...
   Loop                
   ...        
   Else                   ' REDUNDANT IF EMPTY BLOCK
   End If        
   End If       
   End If
   

SQL提示

1. 使用表别名以避免重写长标识符。

2. 如果可能的话，避免循环查询并运行insert-select。(如果dtTrained和sprTrained是VBA变量，则是否需要参数化，请参见下文(。

   INSERT INTO dbo.TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, 
   TRAINED_BY, STATUS, COMPETENCY, APPROVED, TYPE)
   SELECT e.EMPLOYEE_ID, t.DOCUMENT_ID, t.LATEST_REV, 'dtTrained', 'sprTrained', 
   'T', 'Not Verified', 'NO', 'Internal'
   FROM TRAINING_DOCS_ALL t, EMPLOYEE_ALL e
   WHERE e.SELECTED = -1 
   AND e.SELECTED = -1
   

3. 由于笛卡尔乘积在大表中可能存在性能问题，因此要小心上面使用的交叉联接。(例如1000行X 1000行＝1000000个结果行(。

4. 如果未使用通配符(*(，则不要使用LIKE运算符，因为它会运行不同的、甚至更慢的查询过程。相反，使用相等=。

5. 避免使用SELECT * FROM选择所有列(尤其是在追加查询中(。请参阅为什么SELECT*被认为是有害的？。

MS访问提示

• 避免在VBA中编写SQL字符串脚本。而是使用带有参数化的存储查询。这样做可以避免混乱的串联和引号的标点符号。这有助于提高代码的可读性和可维护性，甚至在查询编译器优化保存的查询而不是动态运行的字符串查询时提高效率。

  具体而言，用以下设置替换CurrentDb.Execute ...。

  SQL (另存为存储对象-无串联或换行(

  PARAMETERS [e_id_param] TEXT;
  INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, 
  DATE_TRAINED, TRAINED_BY, STATUS)
  SELECT EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS
  FROM uSysTRAINING_RECORDS
  WHERE EMPLOYEE_ID = [e_id_param]
  

  PARAMETERS [e_id_param] TEXT, [doc_id_param] TEXT;
  DELETE FROM TRAINING_NEEDED 
  WHERE EMPLOYEE_ID = [e_id_param] AND DOCUMENT_ID = [doc_id_param]
  

  VBA(调用QueryDefs、绑定参数并执行-代码中不显示SQL(

  Set qdef = CurrentDb.QueryDefs("mySavedAppendQuery")
  qdef![e_id_param] = EMPLOYEE_ID.Value
  qdef.Execute
  Set qdef = Nothing
  
  Set qdef = CurrentDb.QueryDefs("mySavedDeleteQuery")
  qdef![e_id_param] = EMPLOYEE_ID.Value
  qdef![doc_id_param] = DOCUMENT_ID.Value
  qdef.Execute
  Set qdef = Nothing