如何在访问中运行查询循环? [英] How to run a loop of queries in access?
问题描述
我有一个数据库,其中的表充满了条件和错误消息,用于检查另一个数据库.
I have a database with a table which is full of conditions and error messages for checking another database.
我想运行一个循环,以便对照第二个数据库中的所有表检查所有这些条件,并生成一个给出错误的报告.
I want to run a loop such that each of these conditions is checked against all the tables in the second database and generae a report which gives the errors.
在ms访问中是否可行?
Is this possible in ms access.
例如,
querycrit表
querycrit table
id query error
1 speed<25 and speed>56 speed above limit
2 dist<56 or dist >78 dist within limit
我有超过400个类似这样的不同变量的查询.
I have more than 400 queries like this of different variables.
我要对其运行查询的表是
THe table against which I am running the queries is
记录表
id speed dist accce decele aaa bbb ccc
1 33 34 44 33 33 33 33
2 45 44 55 55 55 22 23
致谢 ttk
推荐答案
以下是一些示例代码.它说明了两种不同类型的记录集的用法.您可能希望阅读Allen Browne和
Here is some more sample code. It illustrates the use of two different types of recordsets. You may wish to read VBA Traps: Working with Recordsets by Allen Browne and List of reserved words in Access 2002 and in later versions of Access .
Dim rs As DAO.Recordset
Dim rs2 As ADODB.Recordset
Set rs = CurrentDb.OpenRecordset("querycrit")
Set rs2 = CreateObject("ADODB.Recordset")
rs2.ActiveConnection = CurrentProject.Connection
For Each tdf In CurrentDb.TableDefs
'EDIT: TableDefs includes Microsoft System tables and '
'these should never be tampered with. They all begin with Msys '
'so we can leave them out of the loop here. '
If Left(tdf.Name, 4) <> "msys" And tdf.Name <> "querycrit" Then
rs.MoveFirst
strSQL = "SELECT * From [" & tdf.Name & "] WHERE "
Do While Not rs.EOF
On Error Resume Next
Debug.Print tdf.Name
rs2.Open strSQL & " " & rs![query]
If Err.Number = 0 Then
On Error GoTo 0
If Not rs2.EOF Then
Debug.Print rs![Error]
Debug.Print rs2.GetString
End If
End If
Err.Clear
rs2.Close
rs.MoveNext
Loop
End If
Next
End Sub
这篇关于如何在访问中运行查询循环?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!