Geek Answers Handbook

Can I compare two ms-access access files?

I want to compare two .msd access files .mdb to verify that the data they contain is the same for both.

How can i do this?

+5
source share
7 answers

I have done such things in the code many times, most often in cases where the local MDB needed to apply updates to it obtained from the data entered on the website. In one case, the site was managed by MDB, in others it is a MySQL database. For MDB, we just downloaded it; for MySQL, we ran scripts on a website for export and FTP text files.

, MDB , -, MDB, , - (, - , , ).

MDB A , MDB B - , . :

  • , MDB A, MDB B. ( ).

  • , MDB B, MDB A. MDB B MDB A.

  • , , .

№1 №2 , . 3 .

, MDB . , DAO TableDefs, , SQL , , .

:

  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

Nz() , Nz (TextField, ''), Nz (NumericField, 0) .

, , WHERE, , ZLS. , , , .

, SQL UPDATE , , SQL UPDATE . , , , , -. ,

, , - , MDB , , , , MDB . , , , , - SQL, - SQL SELECTS 1 , , MDB .

, , !

, qdfOldMembers ( MDB A) qdfNewMembers ( MDB B). 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

varZLSToNull():

Public Function varZLStoNull(varInput As Variant) As Variant
  If Len(varInput) = 0 Then
     varZLStoNull = Null
  Else
     varZLStoNull = varInput
  End If
End Function

, , , , -.

+5

AccessDiff ( ). , . , .

: .

+4

BeyondCompare ( ). , !

+2

- . / .

+1

. Access Access , , , .. .

+1

"table diff" accdbmerge . , , , .

0

, ,

fc file1.mdb file2.mdb

DOS.

, , , , , Diff .

, , shareware.

-4

More articles:


All Articles