使用 vba/sql 比较访问中的所有字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22182057/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
compare all fields in access using vba/sql
提问by user1787114
I know I can compare values between two tables, but I have not needed to do it for more than 2 or 3 fields up to this point so comparing them each individually hasnt been an issue, I used code such as this:
我知道我可以比较两个表之间的值，但到目前为止我不需要对超过 2 或 3 个字段进行比较，因此单独比较它们并不是问题，我使用了如下代码：
DoCmd.RunSQL "INSERT INTO Issues
SELECT Eligibility.[Member Id]
, Eligibility.[Sex Code]
, Eligibility.State
FROM Eligibility LEFT JOIN Ref
ON Eligibility.[Sex Code] = Ref.[Sex Code]
WHERE (((Ref.[Sex Code]) Is Null));"
now however, i need to compare about 140 different fields. is there a better way to do this than writting 140 sql statements and running them all one by one?
然而，现在我需要比较大约 140 个不同的领域。有没有比编写 140 条 sql 语句并一一运行它们更好的方法呢？
i want it to find where fields dont contain the same info and then pull the entire row from both tables,or at the very least the value in the 5th column, member id, and then i can run another query to pull the entire row off of that value if need be (so i can look at both at the same time) and paste them into another table and highlight the cells where the mismatches occur.
我希望它找到字段不包含相同信息的位置，然后从两个表中提取整行，或者至少是第 5 列成员 ID 中的值，然后我可以运行另一个查询以提取整行如果需要（这样我可以同时查看这两个值）并将它们粘贴到另一个表格中并突出显示不匹配的单元格。
both tables are in the same database both tables have the same structure, but the second table might not have all of the values from the first, so i need to find a way to have it match the rows based on the member ID before it starts comparing the rows.
两个表都在同一个数据库中，两个表具有相同的结构，但是第二个表可能没有第一个表中的所有值，所以我需要找到一种方法让它在开始之前根据成员 ID 匹配行比较行。
采纳答案by enderland
You can compare using DAO pretty easily. Using the .Fields()argument on a recordset you get all the different fields in the actual recordset.
您可以很容易地使用 DAO 进行比较。使用.Fields()记录集上的参数可以获得实际记录集中的所有不同字段。
This lets you do something like:
这使您可以执行以下操作：
Sub exampleSQLComparison()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
'.... set those recordsets via SQL statements
For Each f In rs1.Fields
If rs1.Fields(f) <> rs2.Fields(f) Then
Debug.Print "Mismatch found for " + f
End If
Next f
End Sub
If your queries are similar and the only thing you are changing is a single field (for example an ID) you should be able to modify the logic accordingly.
如果您的查询相似并且您唯一更改的是单个字段（例如 ID），您应该能够相应地修改逻辑。
