如何比较两个访问数据库以比较数据库记录

Question

如何比较两个MS ACCESS 2007数据库。两个数据库都包含具有相同表格的相同表格ad structure.i需要比较两个数据库之间的记录值以检测记录中的任何差异值。

      ACCESS 2007 Database1         

      serial no.  | NAME   | ADDRESS     
       1    smith   street 1         
       2    john   street 4         
       3    alix   street 8     


          ACCESS 2007 Database2 

       serial no.| NAME  | ADDRESS 
       1   smith  street 1    
       2   jhn  stret 4    
       3   alix  street 8 

我需要一个可以检测记录差异的ms访问的VBA代码，就像序列号为2的记录一样。

Answer 1

你应该做的第一件事是将其中一个表链接到另一个数据库，例如，将Database 2表链接到数据库1（这可以一起查询这两个表），然后你可以使用这个简单的例子连接来确定如果所有字段串在一起匹配基于序列号：

SELECT T1.*, T2.* 
FROM Table1 As T1, Table2 As T2 
WHERE T2.[serial no.] = T1.[serial no.] 
AND T2.[NAME] & T2.[ADDRESS] <> T1.[NAME] & T1.[ADDRESS] 

如果您愿意，您也可以指定具有各自条件的列。 注意：这是假设您只查找序列号不匹配的区别，如果您还需要识别可能出现在一个表中但不是另一个表中的记录，则需要使用“不匹配”查询，查询设计人员可以帮助您解决这个问题，或者回复并且我可以更新我的答案。

Answer 2

Option Compare Database 

Private Sub Command4_Click() 

Dim tablename1, tablename2 As String 
tablename1 = Text0.Value 
tablename2 = Text2.Value 

'On Error GoTo Err_cmdValidateGeneralInfo_Click 
Dim F As DAO.Field 
Dim rs As DAO.Recordset 
Dim rs1 As DAO.Recordset 
Set curDB = CurrentDb() 
'If Me.DateModified = Date Then 
    'Adds new employees to the TT_GeneralInfo table in the FTEI_PhoneBook.mdb - which is used thru out the AP databases. 
' DoCmd.OpenQuery "qryEmpData_TT_General" 

strsql = "Select * from " & tablename1 

Set rs = curDB.OpenRecordset(strsql) 

strsql1 = "Select * from " & tablename2 

    DoCmd.CopyObject , "Unmatched_records", acTable, tablename1 
    curDB.Execute "DELETE FROM Unmatched_records" 

Set rs1 = curDB.OpenRecordset(strsql1) 
    Do Until rs.EOF 
     For Each F In rs.Fields 
     If rs.Fields(F.Name) <> rs1.Fields(F.Name) Then 
      'rs.Edit 
      strsql = "Select * into test from " & tablename1 & " where " & F.Name & " = """ & rs.Fields(F.Name) & """" 
      DoCmd.RunSQL strsql 

      If DCount(F.Name, "test") <> 0 Then 
      GoTo append_unmatch 

      'appending unmacthed records 
append_unmatch: 

      strsql2 = "insert into Unmatched_records Select * from test" 
      DoCmd.RunSQL strsql2 

      'if record doesnt match move to next one 
      GoTo Nextrecord 
      End If 
     ' rs.Fields(F.Name) = rs1.Fields(F.Name) 
     ' rs.Update 
     End If 
     Next F 

Nextrecord: 
rs.MoveNext 
rs1.MoveNext 
    Loop 

    'To check whether tables matched or not 
    Dim rs2 As DAO.Recordset 
    strsql3 = "select * from Unmatched_records" 
    Set rs2 = curDB.OpenRecordset(strsql3) 
    For Each F In rs2.Fields 
    If DCount(F.Name, "Unmatched_records") <> 0 Then 
    MsgBox ("The two tables didnt match. Check table test for unmatching reocrds.") 
    Else 
    MsgBox ("Tables match!") 
    End If 
Exit Sub 
    Next F 
    rs2.Close 


    End Sub