我可以比较两个ms访问文件吗？

I want to compare two ms-access .mdb files to check that the data they contain is same in both.

我想比较两个ms访问.mdb文件,以检查它们包含的数据是否相同。

How can I do this?

我怎样才能做到这一点?

7 个解决方案

#1

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.

我已经在代码中做了很多次这样的事情,主要是在本地MDB需要从网站上输入的数据中应用更新的情况下。在一个案例中,该网站由MDB驱动,在其他情况下,它是一个MySQL数据库。对于MDB,我们只是下载它,对于MySQL,我们在网站上运行脚本以导出和FTP文本文件。

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).

现在,重点是我们想要将本地MDB中的数据与从网站下载的数据进行比较,并更新本地MDB以反映在网站上所做的更改(不,不可能使用单个数据源 - - 这是我建议的第一件事,但这不可行)。

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:

我们将MDB A称为您的本地数据库,将MDB B称为您要下载的用于比较的数据库。您需要检查的是:

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).

存在于MDB A但不存在于MDB B中的记录。这些记录可能是也可能不是删除的候选者(这取决于您的特定数据)。
records that exist in MDB B but not in MDB A. These you will append from MDB B to MDB A.

存在于MDB B但不存在于MDB A中的记录。这些记录将从MDB B附加到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.

使用外部联接查找缺失记录的查询可以非常轻松地完成步骤#1和#2。第3步需要一些代码。

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.

代码背后的原理是两个MDB中所有表的结构是相同的。因此,您使用DAO遍历TableDefs集合,打开记录集,并遍历字段集合以在每个表的每列上运行SQL语句,以更新数据或输出差异列表。

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:

现在,这里的主要复杂性是每个字段的WHERE子句必须不同 - 文本字段需要与数字和数据字段区别对待。因此,您可能需要一个基于字段类型编写WHERE子句的SELECT CASE:

  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.

您将要使用Nz()来比较文本字段,但是您可以使用Nz(TextField,''),同时将Nz(NumericField,0)用于数字字段或日期字段。

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.

我的示例代码实际上并没有使用上面的结构来定义WHERE子句,因为它仅限于与ZLS(文本字段)连接比较的字段。下面的内容通过阅读非常复杂,但它基本上是对上述结构的扩展。

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,

它是为了更新效率而编写的,因为它为表的每个字段执行SQL UPDATE,这比为每行执行SQL UPDATE更有效。另一方面,如果您不想进行更新,但想要一个差异列表,则可能会以不同的方式处理整个事情。但根据输出,这变得相当复杂,

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.

如果您只想知道两个MDB是否相同,则首先要检查每个表中的记录数,如果有一个不匹配,则退出并告诉用户MDB不相同。如果记录计数是相同的,那么你必须逐字段检查,我认为最好用动态编写的逐列SQL完成 - 只要其中一个结果SQL SELECTS返回1个或多个记录,就中止并告诉您的用户MDB不相同。

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).

以下是来自较大子例程的代码的一部分,该子例程使用来自qdfNewMembers(来自MDB B)的数据更新保存的查询qdfOldMembers(来自MDB A)。第一个参数strSQL是一个SELECT语句,仅限于您要比较的字段,而strTmpDB是另一个MDB的路径/文件名(在我们的示例中为MDB B)。该代码假定strTmpDB已经创建了qdfNewMembers和qdfOldMembers(原始代码动态写入保存的QueryDef)。它可以很容易地直接表名(我使用保存的查询的唯一原因是因为字段名在它为其编写的两个MDB之间不完全匹配)。

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():

函数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.

我不知道这是否太复杂有意义,但也许它会帮助某人。

#2

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.

您可以尝试AccessDiff(付费产品)。它能够比较模式,数据以及访问对象。它有一个GUI和一个命令行界面。

Disclosure: I am the creator of this tool.

披露:我是这个工具的创造者。

#3

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!

获取数据库表的文本转储,并使用BeyondCompare(或任何其他文本比较工具)简单地比较转储的文本文件。原油但可以工作!

#4

I have very good experience with Cross-Database Comparator. It is able to compare structure and/or data.

我对跨数据库比较器有很好的经验。它能够比较结构和/或数据。

#5

See the Compare Access databases section at the Microsoft Access third party utilities, products, tools, modules, etc. page at my website.

请参阅我的网站上Microsoft Access第三方实用程序,产品,工具,模块等页面上的比较Access数据库部分。

#6

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.

不久前我在accdbmerge实用程序中添加了“表差异”功能。我相信这个答案无助于解决原始问题,但对将来遇到同样问题的人可能会有所帮助。

#7

-4

If you want to know if the files are identical then

如果你想知道文件是否相同那么

fc file1.mdb file2.mdb

on a DOS command line.

在DOS命令行上。

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.

有一些工具会为你做这件事,但它们似乎都是共享软件。

#1