从访问表中获取查询的名称，然后运行查询

3 回答

• 1

  您的代码将循环，即使您找到了您的查询并且您没有将Query-Name传递给OpenQuery命令...这将无效...

  集合 CurrentDb.QueryDefs 知道所有现有查询，但没有"Exists"或"Contains"方法 .

  所以：方法是循环（当你尝试它）或错误处理 .

  我已经很久以前用VBA编码了，但我想你可以试试：

  On Error Resume Next
  DoCmd.OpenQuery "YourQueryName"
  If Err Then
      MsgBox("The query does not exist!")
      Err.Clear
  End If
  On Error Goto 0
  

  回复于 2024-05-15T13:44:29+08:00
• 1

  我建议在VBA中使用完整的DAO来实现您的目标 . DoCmd.OpenQuery 实际上是一个模仿宏 RunQuery 动作的VBA函数 . 您没有太多控制或真正的错误处理能力 .

  这是一个完整的代码函数

  • 给您一个如何从表中选择列出查询的全部或部分记录的示例，包括仅选择"Active"记录的能力，甚至可以按特定的执行顺序对它们进行排序

  • 处理表中查询名称不存在的实例

  • 允许您显示有关发生的任何错误的消息

  • 允许您将退出代码返回给调用过程，以便您可以根据运行这些查询的结果执行操作（例如，如果此函数遇到任何类型的错误，则选择不执行代码中的下一步（返回非零值）

  这是代码 . 您将需要修改SQL语句以获取正确的表名和字段名，但这应该是一个很好的示例，可以帮助您顺利完成 .

  Public Function lsProcessQuerySet() As Long
  On Error GoTo Err_PROC
      Dim ErrMsg As String
      Dim db As DAO.Database
      Dim rstEdits As DAO.Recordset
      Dim qdf As DAO.QueryDef
      Dim mssql As String
      Dim ReturnCode As Long
  
      Set db = CurrentDb()
      '============================
      'Select the list of Queries you want to process
      '============================
      mssql = "SELECT tblQueryList.ID, tblQueryList.QueryName, "
      mssql = mssql & "tblQueryList.QueryShortDesc "
      mssql = mssql & "FROM tblQueryList "
      mssql = mssql & "WHERE tblQueryList.QueryActive = True "
      mssql = mssql & "ORDER BY tblQueryList.SortOrder;"
      Set rstEdits = db.OpenRecordset(mssql, dbOpenDynaset)
      DoCmd.Hourglass True
      '============================
      'Execute each query, allowing processing to continue
      'if the query does not exist (an error occurs)
      '============================
      Do While Not rstEdits.EOF
          Set qdf = db.QueryDefs(rstEdits("QueryName"))
          qdf.Execute dbSeeChanges
  ResumeNextEdit:
          rstEdits.MoveNext
      Loop
      rstEdits.Close
  
  Exit_PROC:
      lsProcessQuerySet = ReturnCode
      Set qdf = Nothing
      Set rstEdits = Nothing
      db.Close
      Set db = Nothing
      DoCmd.Hourglass False
      Exit Function
  
  Err_PROC:
      Select Case Err.Number
          Case 3265   'Item Not Found in this Collection
              ReturnCode = Err.Number
              ErrMsg = "Query Not Found:" & vbCrLf & vbCrLf
              ErrMsg = ErrMsg & rstEdits("QueryName")
              DoCmd.Hourglass False
              MsgBox ErrMsg, vbOKOnly + vbCritical, "Function lsProcessQuerySet"
              Resume ResumeNextEdit
          Case Else
              ReturnCode = Err.Number
              ErrMsg = "Error: " & Err.Number & vbCrLf
              ErrMsg = ErrMsg & Err.Description
              DoCmd.Hourglass False
              MsgBox ErrMsg, vbOKOnly + vbCritical, "Function lsProcessQuerySet"
              Resume Exit_PROC
      End Select
  
  End Function
  

  回复于 2024-05-15T13:44:29+08:00
• 1

  @Shnugo的答案已经很好了 . 只是为了给你一个完整的VBA功能，这应该适合你 .

  Public Sub MySub()
  On Error GoTo err_mySub 
  Dim db as Database
  Dim vol as Recordset
  
  Set db = CurrentDb()
  Set vol = db.OpenRecordset("FacilityVolume", dbOpenDynaset) ' I don't know if you want to run all queries of the table "FacilityVolume". 
  'So maybe you could also use something like "SELECT Volume FROM FacilityVolume WHERE Volume LIKE ""*10*"""
  
  Vol.MoveFirst
  Do Until vol.EOF = True
      DoCmd.OpenQuery vol!Volume
      vol.MoveNext
  Loop
  
  exit_MySub:
  Exit Sub
  
  err_MySub:
  If Err.Number = 7874 Then
      MsgBox "The Query """ & Vol!Volume & """ wasn't found."
      Resume Next
  Else
      MsgBox Err.Description
      Resume exit_MySub
  End If
  End Sub
  

  回复于 2024-05-15T13:44:29+08:00