通过VBA运行Access 2007中相同单词开头的多个查询

1 回答

• 1

  以下代码是一个开始（取决于什么类型的查询）...

  创建一个模块并粘贴以下代码 . 然后执行子程序'Test_it'

  Option Compare Database
  Option Explicit
  
  
  Sub Test_it()
  Dim strPrefix As String
      ' Ask user for query prefix
      strPrefix = InputBox("Please enter query prefix", "Qry Prefix")
      MsgBox "Executed " & Run_Queries(strPrefix) & " queries.", vbOKOnly, "COunt of Queries"
  End Sub
  
  Function Run_Queries(strPrefix As String) As Integer
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim strQryName  As String
  Dim iQryCt      As Integer
  Dim iQryRan     As Integer
  Dim i           As Integer
  
      Set dbs = CurrentDb
      iQryCt = dbs.QueryDefs.Count
      i = Len(strPrefix)
      iQryRan = 0
  
      For Each qdf In dbs.QueryDefs
          strQryName = qdf.Name
          Debug.Print strQryName
          If LCase(left(strQryName, i)) = LCase(strPrefix) Then
              Debug.Print "    ** Run: " & strQryName & vbTab & qdf.Type
              If qdf.Type = 0 Then                ' Select query
                  ' Open this query to view results
                  DoCmd.OpenQuery strQryName
              ElseIf qdf.Type = 48 Then           ' Update query
                  dbs.Execute strQryName
              End If
              iQryRan = iQryRan + 1
          Else
  
          End If
      Next qdf
      Set qdf = Nothing
      Set dbs = Nothing
      Run_Queries = iQryRan
  End Function
  

  回复于 2024-04-29T03:10:12+08:00