at our office we take daily backup of ASP.net application ms access backend
for the next few days we need to evaluate the changes made to records in the database tables
at the end of each day i want to compare 2 access databases first database is the backup of yesterday and second database is the backup of today
i thought of the following algorithm, please read carefully and tell me how to proceed to compare the datatables / gridviews
i need to display th rows / cells containing the differences / updates / deleted data
Imports System.Data
Imports System.Data.OleDb
Partial Class MoKoTrack
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim myDB = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|database.mdb;Persist Security Info=True")
Session("CurrentDB") = myDB
myDB.open()
Dim mytables = myDB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {})
Dim CurrentTable As String
Dim ee As Integer = mytables.Rows.Count
Dim OriginalTables(ee) As String
Dim BackupTables(ee) As String
Dim X As Integer = 0
For i = 1 To mytables.Rows.Count
CurrentTable = mytables.Rows(i - 1).Item(2).ToString
If mytables.Rows(i - 1).Item(3).ToString = "TABLE" Or mytables.Rows(i - 1).Item(3).ToString = "VIEW" Then
If CurrentTable.Contains("Backup") Then
BackupTables(X) = CurrentTable
Else
OriginalTables(X) = CurrentTable
End If
X = X + 1
End If
Next i
For i = 0 To BackupTables.Count - 1
If Not BackupTables(i) = "" Then
CompareTable(BackupTables(i))
End If
Next
myDB.Close()
End Sub
Sub CompareTable(ByVal BackupTableName As String)
Dim OriginalTable As New DataTable
Dim BackupTable As New DataTable
Dim ModificationsTable As New DataTable
Dim myDB = Session("CurrentDB")
Dim FinalSQLString = "SELECT * FROM [" + BackupTableName + "]"
Dim myDBCommand = New OleDbCommand(FinalSQLString, myDB)
'Generate a temporary reader to get the number of cases
Dim myReader As IDataReader = myDBCommand.ExecuteReader()
'Dim myColumns = myReader.GetSchemaTable
'For I = 1 To myColumns.Rows.Count
' OriginalTable.Columns.Add(myColumns.Rows(I - 1).Item(0).ToString())
'Next I
BackupTable.Load(myReader)
Dim OriginalTableName = Left(BackupTableName, Len(BackupTableName) - 6)
Dim FinalSQLString2 = "SELECT * FROM [" + BackupTableName + "]"
Dim myDBCommand2 = New OleDbCommand(FinalSQLString, myDB)
'Generate a temporary reader to get the number of cases
Dim myReader2 As IDataReader = myDBCommand.ExecuteReader()
OriginalTable.Load(myReader2)
'Dim myGrid As New GridView
'myGrid.DataSource = OriginalTable
'myGrid.DataBind()
'Me.form1.Controls.Add(myGrid)
'Dim myGrid2 As New GridView
'myGrid2.DataSource = BackupTable
'myGrid2.DataBind()
'Me.form1.Controls.Add(myGrid2)
For i = 0 To OriginalTable.Rows.Count - 1
For t = 0 To OriginalTable.Columns.Count - 1
Next
Next
End Sub
End Class
i am using the following VBA code to rename backup database tables into "tablebackup"
Private Sub Command0_Click()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
tdf.Name = tdf.Name & "backup"
End If
Next
End Sub
