How can i compare two MS ACCESS 2007 databases.Both databases contain same tables with same feilds ad structure.i need to compare the record values between two databases to detect any difference in record values.

                           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

I need a VBA code for ms access that can detect the differece of records,just as the records at serial number two.

• ms-access

First thing you should do is link in one of the tables to the other database, e.g link the Database 2 table into database one (this allows both to be queried together) then you could use this simple example with concatenation to determine if all the fields strung together match based on the serial number:

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]

You could also specify the columns with each of their own condition if you prefer. NOTE: This is assuming you are only looking for differences where the serial no matches, if you also need to identify records that may appear in one table but not the other then you will need to use an "Un-matched" query, the query designer can help you with this or post back and I can update my answer.

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