Access-Datenbank-Schema Vergleichen

Können Sie Freie Datenbank Vergleichen, 2.0 (überprüfen) von StarInix Software zu vergleichen.

Können Sie Lesen Sie die system-Tabellen in der access-Datenbank, die standardmäßig ausgeblendet sind) gerade die Weise, die Sie würde in einer SQL Server-Datenbank, sondern mit dem ODBC-Treiber.

Option Compare Database

Private Sub Command14_Click()

Dim tablename1, tablename2, field_date As String
tablename1 = Text10.Value
tablename2 = Text13.Value
flter_date = Text16.Value
length = Len(tablename1) - Len("gahpu00d_")
field_date = Right(tablename1, length) & "_posted_dt"

'On Error GoTo Err_cmdValidateGeneralInfo_Click

 Dim F As DAO.Field
 Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Set curDB = CurrentDb()

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


 'to calculate count of filtered data in staged

strsql = "Select * from " & tablename1 & " where cdate(" & field_date & ") = """ & flter_date & """ order by " & field_date & ""
DoCmd.RunSQL "Select * into filter_data1 from " & tablename1 & " where cdate(" & field_date & ") = """ & flter_date & """"
count_data1 = DCount(field_date, "filter_data1")
Text18.Enabled = True
Text18.Value = count_data1

'to calculate count of filtered data in production

strsql1 = "Select * from " & tablename2 & " where cdate(" & field_date & ") = """ & flter_date & """ order by " & field_date & ""
DoCmd.RunSQL "Select * into filter_data2 from " & tablename2 & " where cdate(" & field_date & ") = """ & flter_date & """"
count_data2 = DCount(field_date, "filter_data2")
Text20.Enabled = True
Text20.Value = count_data2

If count_data1 <> count_data2 Then
If count_data1 > count_data2 Then
  MsgBox "The number of records in the table didnt match." & tablename1 & " has more records than " & tablename2
  Exit Sub

  Else
  MsgBox "The number of records in the table didnt match." & tablename2 & " has more records than " & tablename1
  Exit Sub

End If
End If

Set rs = curDB.OpenRecordset(strsql)
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
final:
    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 Unmatched_records for unmatching reocrds.")
    Else
    MsgBox ("Tables match!")
    End If
Exit Sub
   Next F
    rs2.Close


End Sub

Private Sub Form_Load()
Text20.Enabled = False
Text18.Enabled = False
Text18.Value = ""
Text20.Value = ""
End Sub