通过 DAO 记录集修改查询

时　间:2022-12-10 08:03:23
作　者:杨雪   ID：42182  城市：南京
摘　要:通过 DAO 记录集修改查询。
正　文:

Sub AddQuery() 
 
Dim dbsNorthwind As DAO.Database 
Dim qdfSalesReps As DAO.QueryDef 
Dim rstSalesReps As DAO.Recordset 
 
On Error GoTo ErrorHandler 
 
   Set dbsNorthwind = CurrentDb 
 
   Set qdfSalesReps = dbsNorthwind.CreateQueryDef("SalesRepQuery") 
   qdfSalesReps.SQL = "Select * FROM Employees Where Title = " & _ 
                      "'Sales Representative'" 
 
   Set rstSalesReps = qdfSalesReps.OpenRecordset() 
 
   ' Call the function to add a constraint. 
   AddQueryFilter rstSalesReps 
 
   ' Return database to original. 
   dbsNorthwind.QueryDefs.Delete "SalesRepQuery" 
 
   rstSalesReps.Close 
   qdfSalesReps.Close 
   dbsNorthwind.Close 
 
   Set rstSalesReps = Nothing 
   Set qdfSalesReps = Nothing 
   Set dbsNorthwind = Nothing 
 
   Exit Sub 
 
ErrorHandler: 
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description 
End Sub 

Sub AddQueryFilter(rstData As Recordset) 
 
Dim qdfData As DAO.QueryDef 
Dim strNewFilter As String 
Dim strRightSQL As String 
 
On Error GoTo ErrorHandler 
 
   Set qdfData = rstData.CopyQueryDef 
 
   ' Try "LastName LIKE 'D*'". 
   strNewFilter = InputBox("Enter new criteria") 
 
   strRightSQL = Right(qdfData.SQL, 1) 
 
   ' Strip characters from the end of the query, 
   ' as needed. 
   Do While strRightSQL = " " or strRightSQL = ";" or _ 
                          strRightSQL = vbCR or strRightSQL = vbLF 
      qdfData.SQL = Left(qdfData.SQL, Len(qdfData.SQL) - 1) 
      strRightSQL = Right(qdfData.SQL, 1) 
   Loop 
 
   qdfData.SQL = qdfData.SQL & " AND " & strNewFilter 
   rstData.Requery qdfData         'Requery the Recordset. 
   rstData.MoveLast               'Populate the Recordset. 
 
   ' "Lastname LIKE 'D*'" should return 2 records. 
   MsgBox "Number of records found:  " & rstData.RecordCount & "." 
 
   qdfData.Close 
   Set qdfData = Nothing 
 
   Exit Sub 
 
ErrorHandler: 
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description 
End Sub

备   注：

若要使用 Requery 方法，必须将 Recordset 对象的 Restartable 属性设置为 True。 如果 Recordset 是基于 Access 数据库中的表通过查询而不是通过交叉表查询创建的，则 Restartable 属性始终设置为 True。 不能重新启动 SQL 传递查询。 您可能能够对另一个数据库格式的链接表重新启动查询，也可能不能够这样做。 要确定 Recordset 对象能否重新运行其查询，请检查 Restartable 属性。