Set rs = db.OpenRecordset("[SQL statement with the fields you want compared]")
For Each fld In rs.Fields
' Write a SQL string to update all the records in this column
' where the data doesn't match
strSQL = "[constructed SQL here]"
db.Execute strSQL, dbFailOnError
Next fld
现在，这里的主要复杂性是每个字段的 WHERE 子句必须不同 - 文本字段需要与数字和数据字段区别对待。 因此，您可能需要一个 SELECT CASE 来根据字段类型编写 WHERE 子句：
Select Case fld.Type
Case dbText, dbMemo
Case Else
End Select
Public Sub ImportMembers(strSQL As String, strTmpDB As String)
Const STR_QUOTE = """"
Dim db As Database
Dim rsSource As Recordset '
Dim fld As Field
Dim strUpdateField As String
Dim strZLS As String
Dim strSet As String
Dim strWhere As String
' EXTENSIVE CODE LEFT OUT HERE
Set db = Application.DBEngine(0).OpenDatabase(strTmpDB)
' UPDATE EXISTING RECORDS
Set rsSource = db.OpenRecordset(strSQL)
strSQL = "UPDATE qdfNewMembers INNER JOIN qdfOldMembers ON "
strSQL = strSQL & "qdfNewMembers.EntityID = qdfOldMembers.EntityID IN '" _
& strTmpDB & "'"
If rsSource.RecordCount <> 0 Then
For Each fld In rsSource.Fields
strUpdateField = fld.Name
'Debug.Print strUpdateField
If InStr(strUpdateField, "ID") = 0 Then
If fld.Type = dbText Then
strZLS = " & ''"
Else
strZLS = vbNullString
End If
strSet = " SET qdfOldMembers." & strUpdateField _
& " = varZLStoNull(qdfNewMembers." & strUpdateField & ")"
strWhere = " WHERE " & "qdfOldMembers." & strUpdateField & strZLS _
& "<>" & "qdfNewMembers." & strUpdateField & strZLS _
& " OR (IsNull(qdfOldMembers." & strUpdateField _
& ")<>IsNull(varZLStoNull(qdfNewMembers." _
& strUpdateField & ")));"
db.Execute strSQL & strSet & strWhere, dbFailOnError
'Debug.Print strSQL & strSet & strWhere
End If
Next fld
End If
End Sub
函数 varZLSToNull() 的代码：
Public Function varZLStoNull(varInput As Variant) As Variant
If Len(varInput) = 0 Then
varZLStoNull = Null
Else
varZLStoNull = varInput
End If
End Function
我不知道这是否太复杂而没有意义，但也许它会对某人有所帮助。
I've done this kind of thing in code many, many times, mostly in cases where a local MDB needed to have updates applied to it drawn from data entered on a website. In one case the website was driven by an MDB, in others, it was a MySQL database. For the MDB, we just downloaded it, for MySQL, we ran scripts on the website to export and FTP text files.
Now, the main point is that we wanted to compare data in the local MDB to the data downloaded from the website and update the local MDB to reflect changes made on the website (no, it wasn't possible to use a single data source -- it was the first thing I suggested, but it wasn't feasible).
Let's call MDB A your local database, and MDB B the one you're downloading for comparison. What you have to check for is:
records that exist in MDB A but not in MDB B. These may or may not be candidates for deletion (this will depend on your particular data).
records that exist in MDB B but not in MDB A. These you will append from MDB B to MDB A.
records that exist in both, which will need to be compared field by field.
Steps #1 and #2 are fairly easily accomplished with queries that use an outer join to find the missing records. Step 3 requires some code.
The principle behind the code is that the structure of all the tables in both MDBs are identical. So, you use DAO to walk the TableDefs collection, open a recordset, and walk the fields collection to run a SQL statement on each column of each table that either updates the data or outputs a list of the differences.
The basic structure behind the code is:
Set rs = db.OpenRecordset("[SQL statement with the fields you want compared]")
For Each fld In rs.Fields
' Write a SQL string to update all the records in this column
' where the data doesn't match
strSQL = "[constructed SQL here]"
db.Execute strSQL, dbFailOnError
Next fld
Now, the major complexity here is that your WHERE clause for each field has to be different -- text fields need to be treated differently from numeric and data fields. So you'll probably want a SELECT CASE that writes your WHERE clause based on the field type:
Select Case fld.Type
Case dbText, dbMemo
Case Else
End Select
You'll want to use Nz() to compare the text fields, but you'd use Nz(TextField,'') for that, while using Nz(NumericField,0) for numeric fields or date fields.
My example code doesn't actually use the structure above to define the WHERE clause because it's limited to fields that work very well comparing concatenated with a ZLS (text fields). What's below is pretty complicated to read through, but it's basically an expansion on the above structure.
It was written for efficiency of updates, since it executes a SQL UPDATE for each field of the table, which is much more efficient than executing a SQL UPDATE for each row. If, on the other hand, you don't want to do an update, but want a list of the differences, you might treat the whole thing differently. But that gets pretty complicated depending on the output,
If all you want to know is if two MDBs are identical, you would first check the number of records in each table first, and if you have one non-match, you quit and tell the user that the MDBs aren't the same. If the recordcounts are the same, then you have to check field by field, which I believe is best accomplished with column-by-column SQL written dynamically -- as soon as one of the resulting SQL SELECTS returns 1 or more records, you abort and tell your user that the MDBs are not identical.
The complicated part is if you want to record the differences and inform the user, but going into that would make this already-interminable post even longer!
What follows is just a portion of code from a larger subroutine which updates the saved query qdfOldMembers (from MDB A) with data from qdfNewMembers (from MDB B). The first argument, strSQL, is a SELECT statement that is limited to the fields you want to compare, while strTmpDB is the path/filename of the other MDB (MDB B in our example). The code assumes that strTmpDB has qdfNewMembers and qdfOldMembers already created (the original code writes the saved QueryDef on the fly). It could just as easily be direct table names (the only reason I use a saved query is because the fieldnames don't match exactly between the two MDBs it was written for).
Public Sub ImportMembers(strSQL As String, strTmpDB As String)
Const STR_QUOTE = """"
Dim db As Database
Dim rsSource As Recordset '
Dim fld As Field
Dim strUpdateField As String
Dim strZLS As String
Dim strSet As String
Dim strWhere As String
' EXTENSIVE CODE LEFT OUT HERE
Set db = Application.DBEngine(0).OpenDatabase(strTmpDB)
' UPDATE EXISTING RECORDS
Set rsSource = db.OpenRecordset(strSQL)
strSQL = "UPDATE qdfNewMembers INNER JOIN qdfOldMembers ON "
strSQL = strSQL & "qdfNewMembers.EntityID = qdfOldMembers.EntityID IN '" _
& strTmpDB & "'"
If rsSource.RecordCount <> 0 Then
For Each fld In rsSource.Fields
strUpdateField = fld.Name
'Debug.Print strUpdateField
If InStr(strUpdateField, "ID") = 0 Then
If fld.Type = dbText Then
strZLS = " & ''"
Else
strZLS = vbNullString
End If
strSet = " SET qdfOldMembers." & strUpdateField _
& " = varZLStoNull(qdfNewMembers." & strUpdateField & ")"
strWhere = " WHERE " & "qdfOldMembers." & strUpdateField & strZLS _
& "<>" & "qdfNewMembers." & strUpdateField & strZLS _
& " OR (IsNull(qdfOldMembers." & strUpdateField _
& ")<>IsNull(varZLStoNull(qdfNewMembers." _
& strUpdateField & ")));"
db.Execute strSQL & strSet & strWhere, dbFailOnError
'Debug.Print strSQL & strSet & strWhere
End If
Next fld
End If
End Sub
Code for function varZLSToNull():
Public Function varZLStoNull(varInput As Variant) As Variant
If Len(varInput) = 0 Then
varZLStoNull = Null
Else
varZLStoNull = varInput
End If
End Function
I don't know if that's too complex to make sense, but maybe it will help somebody.
You can try AccessDiff (paid product). It has the ability to compare the schema, the data, and also access objects. It has a GUI and also a command line interface.
Take text dumps of database tables and simply compare the dumped text files using BeyondCompare (or any other text comparison tool). Crude but can work!
I've added "table diff" feature to my accdbmerge utility not so long time ago. I beleive that this answer will not help to solve original question, but it may be helpful for someone faced with the same problem in the future.
If you want to know if the files are identical then
fc file1.mdb file2.mdb
on a DOS command line.
If the files aren't identical but you suspect they contain the same tables and records then the easiest way would be quickly write a small utility that opens both databases and cycles through the tables of both performing a heterogeneous query to extract the Diff between the two files.
There are some tools out there which will do this for you, but they all appear to be shareware.
发布评论
评论（7）
我已经在代码中做过很多次这样的事情，主要是在本地 MDB 需要从网站上输入的数据中提取更新的情况下。 在一种情况下，该网站由 MDB 驱动，在其他情况下，它是 MySQL 数据库。 对于 MDB，我们只是下载它，对于 MySQL，我们在网站上运行脚本来导出和 FTP 文本文件。
现在，要点是我们想要将本地 MDB 中的数据与从网站下载的数据进行比较，并更新本地 MDB 以反映网站上所做的更改（不，不可能使用单个数据源 - - 这是我建议的第一件事，但这是不可行的）。
我们将 MDB A 称为您的本地数据库，将 MDB B 称为您要下载用于比较的数据库。 您必须检查的是：
MDB A 中存在但 MDB B 中不存在的记录。这些记录可能会也可能不会被删除（这取决于您的特定数据）。
存在于 MDB B 但不存在于 MDB A 中的记录。您将从 MDB B 追加到 MDB A。
两者都存在的记录，需要逐字段比较。
通过使用外连接查找缺失记录的查询，可以相当轻松地完成步骤 #1 和 #2。 第 3 步需要一些代码。
代码背后的原理是两个 MDB 中所有表的结构都是相同的。 因此，您可以使用 DAO 遍历 TableDefs 集合，打开一个记录集，然后遍历 fields 集合，以在每个表的每一列上运行 SQL 语句，以更新数据或输出差异列表。
代码背后的基本结构是：
现在，这里的主要复杂性是每个字段的 WHERE 子句必须不同 - 文本字段需要与数字和数据字段区别对待。 因此，您可能需要一个 SELECT CASE 来根据字段类型编写 WHERE 子句：
您需要使用 Nz() 来比较文本字段，但您可以使用 Nz(TextField,'') 来进行比较，对数字字段或日期字段使用 Nz(NumericField,0) 时。
我的示例代码实际上并未使用上面的结构来定义 WHERE 子句，因为它仅限于与 ZLS（文本字段）连接相比效果很好的字段。 下面的内容读起来相当复杂，但它基本上是上述结构的扩展。
它是为了更新效率而编写的，因为它对表的每个字段执行 SQL UPDATE，这比对每行执行 SQL UPDATE 高效得多。 另一方面，如果您不想进行更新，而是想要差异列表，则您可能会以不同的方式对待整个事情。 但是根据输出，这会变得相当复杂，
如果您想知道两个 MDB 是否相同，您首先要检查每个表中的记录数，如果有一个不匹配，则退出并告诉用户认为 MDB 不相同。 如果记录计数相同，那么您必须逐个字段进行检查，我认为这最好通过动态编写的逐列 SQL 来完成 - 一旦结果 SQL SELECTS 返回 1 个或多个记录，您就中止并告诉您的用户 MDB 不相同。
复杂的部分是，如果您想记录差异并通知用户，但深入研究会使这篇本来就很冗长的帖子变得更长！
下面只是一个较大子例程的代码的一部分，该子例程使用 qdfNewMembers（来自 MDB B）的数据更新已保存的查询 qdfOldMembers（来自 MDB A）。 第一个参数 strSQL 是一条 SELECT 语句，仅限于要比较的字段，而 strTmpDB 是另一个 MDB（在我们的示例中为 MDB B）的路径/文件名。 该代码假设 strTmpDB 已经创建了 qdfNewMembers 和 qdfOldMembers（原始代码动态写入保存的 QueryDef）。 它也可以很容易是直接表名（我使用保存的查询的唯一原因是因为它所编写的两个 MDB 之间的字段名不完全匹配）。
函数 varZLSToNull() 的代码：
我不知道这是否太复杂而没有意义，但也许它会对某人有所帮助。
I've done this kind of thing in code many, many times, mostly in cases where a local MDB needed to have updates applied to it drawn from data entered on a website. In one case the website was driven by an MDB, in others, it was a MySQL database. For the MDB, we just downloaded it, for MySQL, we ran scripts on the website to export and FTP text files.
Now, the main point is that we wanted to compare data in the local MDB to the data downloaded from the website and update the local MDB to reflect changes made on the website (no, it wasn't possible to use a single data source -- it was the first thing I suggested, but it wasn't feasible).
Let's call MDB A your local database, and MDB B the one you're downloading for comparison. What you have to check for is:
records that exist in MDB A but not in MDB B. These may or may not be candidates for deletion (this will depend on your particular data).
records that exist in MDB B but not in MDB A. These you will append from MDB B to MDB A.
records that exist in both, which will need to be compared field by field.
Steps #1 and #2 are fairly easily accomplished with queries that use an outer join to find the missing records. Step 3 requires some code.
The principle behind the code is that the structure of all the tables in both MDBs are identical. So, you use DAO to walk the TableDefs collection, open a recordset, and walk the fields collection to run a SQL statement on each column of each table that either updates the data or outputs a list of the differences.
The basic structure behind the code is:
Now, the major complexity here is that your WHERE clause for each field has to be different -- text fields need to be treated differently from numeric and data fields. So you'll probably want a SELECT CASE that writes your WHERE clause based on the field type:
You'll want to use Nz() to compare the text fields, but you'd use Nz(TextField,'') for that, while using Nz(NumericField,0) for numeric fields or date fields.
My example code doesn't actually use the structure above to define the WHERE clause because it's limited to fields that work very well comparing concatenated with a ZLS (text fields). What's below is pretty complicated to read through, but it's basically an expansion on the above structure.
It was written for efficiency of updates, since it executes a SQL UPDATE for each field of the table, which is much more efficient than executing a SQL UPDATE for each row. If, on the other hand, you don't want to do an update, but want a list of the differences, you might treat the whole thing differently. But that gets pretty complicated depending on the output,
If all you want to know is if two MDBs are identical, you would first check the number of records in each table first, and if you have one non-match, you quit and tell the user that the MDBs aren't the same. If the recordcounts are the same, then you have to check field by field, which I believe is best accomplished with column-by-column SQL written dynamically -- as soon as one of the resulting SQL SELECTS returns 1 or more records, you abort and tell your user that the MDBs are not identical.
The complicated part is if you want to record the differences and inform the user, but going into that would make this already-interminable post even longer!
What follows is just a portion of code from a larger subroutine which updates the saved query qdfOldMembers (from MDB A) with data from qdfNewMembers (from MDB B). The first argument, strSQL, is a SELECT statement that is limited to the fields you want to compare, while strTmpDB is the path/filename of the other MDB (MDB B in our example). The code assumes that strTmpDB has qdfNewMembers and qdfOldMembers already created (the original code writes the saved QueryDef on the fly). It could just as easily be direct table names (the only reason I use a saved query is because the fieldnames don't match exactly between the two MDBs it was written for).
Code for function varZLSToNull():
I don't know if that's too complex to make sense, but maybe it will help somebody.
您可以尝试AccessDiff（付费产品）。 它能够比较模式、数据以及访问对象。 它有一个 GUI 和一个命令行界面。
披露：我是这个工具的创建者。
You can try AccessDiff (paid product). It has the ability to compare the schema, the data, and also access objects. It has a GUI and also a command line interface.
Disclosure: I am the creator of this tool.
获取数据库表的文本转储，并使用 BeyondCompare（或任何其他文本比较工具）简单地比较转储的文本文件。 简陋但可以工作！
Take text dumps of database tables and simply compare the dumped text files using BeyondCompare (or any other text comparison tool). Crude but can work!
我对跨数据库比较器有非常好的经验。 它能够比较结构和/或数据。
I have very good experience with Cross-Database Comparator. It is able to compare structure and/or data.
请参阅 Microsoft Access 第三方实用程序、产品、工具、模块中的比较 Access 数据库部分，我网站上的等页面。
See the Compare Access databases section at the Microsoft Access third party utilities, products, tools, modules, etc. page at my website.
我已将“表差异”功能添加到我的 accdbmerge 实用程序中很久以前了。
我相信这个答案无助于解决原来的问题，但可能对将来面临同样问题的人有所帮助。
I've added "table diff" feature to my accdbmerge utility not so long time ago.
I beleive that this answer will not help to solve original question, but it may be helpful for someone faced with the same problem in the future.
如果您想知道文件是否相同，请
使用 DOS 命令行。
如果文件不相同，但您怀疑它们包含相同的表和记录，那么最简单的方法是快速编写一个小实用程序，打开两个数据库并循环遍历两个数据库的表，执行异构查询以提取两者之间的差异文件。
有一些工具可以为您执行此操作，但它们似乎都是共享软件。
If you want to know if the files are identical then
on a DOS command line.
If the files aren't identical but you suspect they contain the same tables and records then the easiest way would be quickly write a small utility that opens both databases and cycles through the tables of both performing a heterogeneous query to extract the Diff between the two files.
There are some tools out there which will do this for you, but they all appear to be shareware.