Database comparing

[Kristaps]

Hi everyone!

I have some questions, maybe someone can help me...
I write script for table comparing, but it works wery slovly. There is the script, can anyone give some tip how can I make this process faster and if it is possible in VB.
The idea is to compare two tables and if there is some distinction, then is written in third table. Some tables contains more than 200 000 rows and each row contain approximatelly 30 parameeters (columns).
*************** *************** *************** *************** *************** *************** ***
Option Compare Database

Sub Parametru_Izmai nas()
Dim bssSiemens As Database
Dim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As Recordset
Dim rstKonf As Recordset
Dim n As Long, tx As Integer, a As Integer
Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
Dim TableNew As Variant, TableOld As String, TableList As Variant
Dim strCriteria As String, strCriteriaSect As String
Dim NewParam As Variant, OldParam As Variant
Set bssSiemens = CurrentDb
Set rstParamChange = bssSiemens.Open Recordset("Izma inas", dbOpenDynaset)
Set rstKonf = bssSiemens.Open Recordset("Konf iguracija", dbOpenDynaset)

For a = 2 To 5
'If a = 1 Then
' TableList = Array("Bsc")
ElseIf a = 2 Then
TableList = Array("Btsm")
ElseIf a = 3 Then
TableList = Array("Bts")
ElseIf a = 4 Then
TableList = Array("AdjC")
ElseIf a = 5 Then
TableList = Array("Chan")
End If

For Each TableNew In TableList
TableOld = TableNew & "Old"
Set rstTableNew = bssSiemens.Open Recordset(Table New)
Set rstTableOld = bssSiemens.Open Recordset(Table Old, dbOpenDynaset)
If rstTableNew.Rec ordCount = 0 Then GoTo 300
rstTableNew.Mov eFirst
fname1 = rstTableNew.Fie lds(0).Name
fname2 = rstTableNew.Fie lds(1).Name
fname3 = rstTableNew.Fie lds(2).Name
fname4 = rstTableNew.Fie lds(3).Name
fname5 = rstTableNew.Fie lds(4).Name
Do While rstTableNew.EOF = False
NewParam = rstTableNew.Get Rows(1)
id1 = NewParam(0, 0)
id2 = NewParam(1, 0)
id3 = NewParam(2, 0)
id4 = NewParam(3, 0)
id5 = NewParam(4, 0)

If a = 2 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2
ElseIf a = 2 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3
ElseIf a = 4 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4
ElseIf a = 4 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4 & " AND [" & fname5 & "]=" & id5
Else
strCriteria = "[" & fname1 & "]=" & id1

End If
strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3
rstKonf.FindFir st strCriteriaSect
rstTableOld.Fin dFirst strCriteria

If rstTableOld.NoM atch Then
rstParamChange. AddNew
rstParamChange! Date = Date - 1
rstParamChange! id1 = id1
rstParamChange! id2 = id2
If a = 3 Then rstParamChange! id3 = id3
If a = 4 Then rstParamChange! id3 = id3
If a = 4 Then rstParamChange! id4 = id4
If a = 5 Then rstParamChange! id3 = id3
If a = 5 Then rstParamChange! id4 = id4
If a = 5 Then rstParamChange! id5 = id5
rstParamChange! Table = TableNew
rstParamChange. Update
rstParamChange. Requery
GoTo 100
Else
OldParam = rstTableOld.Get Rows(1)
For n = 0 To rstTableNew.Fie lds.Count - 1
If NewParam(n, 0) = OldParam(n, 0) Then
tx = 1

Else
If IsNull(NewParam (n, 0)) And IsNull(OldParam (n, 0)) Then GoTo 200
rstParamChange. AddNew
rstParamChange! SectorName = rstKonf.Fields( "SectorName ")
rstParamChange! Date = Date - 1
rstParamChange! id1 = NewParam(0, 0)
rstParamChange! id2 = NewParam(1, 0)
rstParamChange! id3 = NewParam(2, 0)
rstParamChange! id4 = NewParam(3, 0)
rstParamChange! id5 = NewParam(4, 0)
rstParamChange! Table = TableNew
rstParamChange! Parameter = rstTableNew.Fie lds(n).Name
rstParamChange! new = NewParam(n, 0)
rstParamChange! old = OldParam(n, 0)
rstParamChange. Update
rstParamChange. Requery
End If

200
Next n
End If

100
Loop

300
Next
Next a
End Sub

[Killer42]

I apologise in advance, as I haven't read through your code in detail yet. But I would like to get a couple of questions answered first.

• Can you post the table definition? I think we'll need to know about indexes, especially.
• Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields?
• Have you tried using the Wizard supplied with Access, to build a "find duplicates query"?

I'm going to re-post your code with tags around it for readability. I've underlined some of the If/ElseIf conditions in the code which don't seem to make sense. Can you check them please?

Option Compare Database

Sub Parametru_Izmainas()
Dim bssSiemens As Database
Dim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As Recordset
Dim rstKonf As Recordset
Dim n As Long, tx As Integer, a As Integer
Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
Dim TableNew As Variant, TableOld As String, TableList As Variant
Dim strCriteria As String, strCriteriaSect As String
Dim NewParam As Variant, OldParam As Variant
Set bssSiemens = CurrentDb
Set rstParamChange = bssSiemens.OpenRecordset("Izmainas", dbOpenDynaset)
Set rstKonf = bssSiemens.OpenRecordset("Konfiguracija", dbOpenDynaset)

For a = 2 To 5
'If a = 1 Then
'        TableList = Array("Bsc")
    ElseIf a = 2 Then
        TableList = Array("Btsm")
    ElseIf a = 3 Then
        TableList = Array("Bts")
    ElseIf a = 4 Then
        TableList = Array("AdjC")
    ElseIf a = 5 Then
        TableList = Array("Chan")
End If

For Each TableNew In TableList
TableOld = TableNew & "Old"
Set rstTableNew = bssSiemens.OpenRecordset(TableNew)
Set rstTableOld = bssSiemens.OpenRecordset(TableOld, dbOpenDynaset)
If rstTableNew.RecordCount = 0 Then GoTo 300
rstTableNew.MoveFirst
fname1 = rstTableNew.Fields(0).Name
fname2 = rstTableNew.Fields(1).Name
fname3 = rstTableNew.Fields(2).Name
fname4 = rstTableNew.Fields(3).Name
fname5 = rstTableNew.Fields(4).Name
Do While rstTableNew.EOF = False
    NewParam = rstTableNew.GetRows(1)
    id1 = NewParam(0, 0)
    id2 = NewParam(1, 0)
    id3 = NewParam(2, 0)
    id4 = NewParam(3, 0)
    id5 = NewParam(4, 0)
    
    If [B][U]a = 2[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2
    ElseIf [B][U]a = 2[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2 & " AND [" & fname3 & "]=" & id3
    ElseIf [B][U]a = 4[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4
    ElseIf [B][U]a = 4[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" _
        & id4 & " AND [" & fname5 & "]=" & id5
    Else
        strCriteria = "[" & fname1 & "]=" & id1

    End If
    strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3
    rstKonf.FindFirst strCriteriaSect
    rstTableOld.FindFirst strCriteria
    
    If rstTableOld.NoMatch Then
          rstParamChange.AddNew
      rstParamChange!Date = Date - 1
      rstParamChange!id1 = id1
      rstParamChange!id2 = id2
      If a = 3 Then rstParamChange!id3 = id3
      If a = 4 Then rstParamChange!id3 = id3
      If a = 4 Then rstParamChange!id4 = id4
      If a = 5 Then rstParamChange!id3 = id3
      If a = 5 Then rstParamChange!id4 = id4
      If a = 5 Then rstParamChange!id5 = id5
      rstParamChange!Table = TableNew
      rstParamChange.Update
      rstParamChange.Requery
            GoTo 100
    Else
        OldParam = rstTableOld.GetRows(1)
        For n = 0 To rstTableNew.Fields.Count - 1
            If NewParam(n, 0) = OldParam(n, 0) Then
                tx = 1
                
              Else
                If IsNull(NewParam(n, 0)) And IsNull(OldParam(n, 0)) Then GoTo 200
                rstParamChange.AddNew
                rstParamChange!SectorName = rstKonf.Fields("SectorName")
                rstParamChange!Date = Date - 1
                rstParamChange!id1 = NewParam(0, 0)
                rstParamChange!id2 = NewParam(1, 0)
                rstParamChange!id3 = NewParam(2, 0)
                rstParamChange!id4 = NewParam(3, 0)
                rstParamChange!id5 = NewParam(4, 0)
                rstParamChange!Table = TableNew
                rstParamChange!Parameter = rstTableNew.Fields(n).Name
                rstParamChange!new = NewParam(n, 0)
                rstParamChange!old = OldParam(n, 0)
                rstParamChange.Update
                rstParamChange.Requery
            End If
            
200
        Next n
    End If
    
100
Loop

300
Next
Next a
End Sub

[Kristaps]

I apologise in advance, as I haven't read through your code in detail yet. But I would like to get a couple of questions answered first.

• Can you post the table definition? I think we'll need to know about indexes, especially.
• Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields?
• Have you tried using the Wizard supplied with Access, to build a "find duplicates query"?

I'm going to re-post your code with tags around it for readability. I've underlined some of the If/ElseIf conditions in the code which don't seem to make sense. Can you check them please?

Option Compare Database

Sub Parametru_Izmainas()
Dim bssSiemens As Database
Dim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As Recordset
Dim rstKonf As Recordset
Dim n As Long, tx As Integer, a As Integer
Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
Dim TableNew As Variant, TableOld As String, TableList As Variant
Dim strCriteria As String, strCriteriaSect As String
Dim NewParam As Variant, OldParam As Variant
Set bssSiemens = CurrentDb
Set rstParamChange = bssSiemens.OpenRecordset("Izmainas", dbOpenDynaset)
Set rstKonf = bssSiemens.OpenRecordset("Konfiguracija", dbOpenDynaset)

For a = 2 To 5
'If a = 1 Then
'        TableList = Array("Bsc")
    ElseIf a = 2 Then
        TableList = Array("Btsm")
    ElseIf a = 3 Then
        TableList = Array("Bts")
    ElseIf a = 4 Then
        TableList = Array("AdjC")
    ElseIf a = 5 Then
        TableList = Array("Chan")
End If

For Each TableNew In TableList
TableOld = TableNew & "Old"
Set rstTableNew = bssSiemens.OpenRecordset(TableNew)
Set rstTableOld = bssSiemens.OpenRecordset(TableOld, dbOpenDynaset)
If rstTableNew.RecordCount = 0 Then GoTo 300
rstTableNew.MoveFirst
fname1 = rstTableNew.Fields(0).Name
fname2 = rstTableNew.Fields(1).Name
fname3 = rstTableNew.Fields(2).Name
fname4 = rstTableNew.Fields(3).Name
fname5 = rstTableNew.Fields(4).Name
Do While rstTableNew.EOF = False
    NewParam = rstTableNew.GetRows(1)
    id1 = NewParam(0, 0)
    id2 = NewParam(1, 0)
    id3 = NewParam(2, 0)
    id4 = NewParam(3, 0)
    id5 = NewParam(4, 0)
    
    If [B][U]a = 2[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2
    ElseIf [B][U]a = 2[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2 & " AND [" & fname3 & "]=" & id3
    ElseIf [B][U]a = 4[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4
    ElseIf [B][U]a = 4[/U][/B] Then
        strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
        & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" _
        & id4 & " AND [" & fname5 & "]=" & id5
    Else
        strCriteria = "[" & fname1 & "]=" & id1

    End If
    strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3
    rstKonf.FindFirst strCriteriaSect
    rstTableOld.FindFirst strCriteria
    
    If rstTableOld.NoMatch Then
          rstParamChange.AddNew
      rstParamChange!Date = Date - 1
      rstParamChange!id1 = id1
      rstParamChange!id2 = id2
      If a = 3 Then rstParamChange!id3 = id3
      If a = 4 Then rstParamChange!id3 = id3
      If a = 4 Then rstParamChange!id4 = id4
      If a = 5 Then rstParamChange!id3 = id3
      If a = 5 Then rstParamChange!id4 = id4
      If a = 5 Then rstParamChange!id5 = id5
      rstParamChange!Table = TableNew
      rstParamChange.Update
      rstParamChange.Requery
            GoTo 100
    Else
        OldParam = rstTableOld.GetRows(1)
        For n = 0 To rstTableNew.Fields.Count - 1
            If NewParam(n, 0) = OldParam(n, 0) Then
                tx = 1
                
              Else
                If IsNull(NewParam(n, 0)) And IsNull(OldParam(n, 0)) Then GoTo 200
                rstParamChange.AddNew
                rstParamChange!SectorName = rstKonf.Fields("SectorName")
                rstParamChange!Date = Date - 1
                rstParamChange!id1 = NewParam(0, 0)
                rstParamChange!id2 = NewParam(1, 0)
                rstParamChange!id3 = NewParam(2, 0)
                rstParamChange!id4 = NewParam(3, 0)
                rstParamChange!id5 = NewParam(4, 0)
                rstParamChange!Table = TableNew
                rstParamChange!Parameter = rstTableNew.Fields(n).Name
                rstParamChange!new = NewParam(n, 0)
                rstParamChange!old = OldParam(n, 0)
                rstParamChange.Update
                rstParamChange.Requery
            End If
            
200
        Next n
    End If
    
100
Loop

300
Next
Next a
End Sub

OK...
I have two tables, in bouth tables are some fields with identificators and other fields with some parameters. Identificators can not been changed they come from other table. One of table are updated every morning (new) other table contain information abouth yesterday (old). I'm interesting to find out if in "new" table some of parameters are changed, maybe there is some new records or maybe some records are deleted. All this infomation I try to put in third table.
The parameters moustly are numbers.

Abouth If/Elseif object... yes these are wrong. Correct is - first elseif "a = 3" and last is "a = 5".

[Kristaps]

Maybe I need to find another script to do this...?!

[Killer42]

Maybe I need to find another script to do this...?!

Sorry, been very busy. It may be a few hours yet before I have a chance to look into this properly. But, I think you should be able to do three fairly simple SQL queries to find new, removed and modified records.

I'm not expert in SQL. I'll check with an expert or two from the Access forum, and one or more of us will get back to you.

[NeoPa]

I can't commit any time to this tonight as it's too late already for me.
I thought I'd register the thread though anyway to remind me to look tomorrow.
When I look again tomorrow it would be nice to find clear answers to Killer's original questions posted in here.

[Kristaps]

Ifsomeone give e-mail adress, then I can send screenshots of all three tables... maybe it will be easy... it's hard to tell how the tables are look like.

Yes, I try to use these access wizards, but it didn't help... :(

[NeoPa]

Ifsomeone give e-mail adress, then I can send screenshots of all three tables... maybe it will be easy... it's hard to tell how the tables are look like.

Yes, I try to use these access wizards, but it didn't help... :(

No. Sending stuff via e-mail defeats the purpose of these forums.
Please post the information in a form similar to this :
Posting Table/Dataset MetaData

[b]Table Name=tblStudent[/b]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

[Killer42]

When I had a go at explaining your situation to mmccarthy (let's hope I got it right), here was her response (edited slightly)...

The easiest way to do this is with three recordsets. This pseudo code is DAO based, you'll have to translate it to ADO if necessary.

rs1 - Set to TableNew (todays records)
rs2 - Set to TableOld (yesterdays records)
rs3 - Set to transaction table to record differences

Dim recordChanged As Boolean 

rs.MoveFirst
Do Until rs1.EOF
  recordChanged = False
  rs2.FindFirst "[Unique_ID_Field]=" & rs1![Unique_ID_Field] 
  If rs2!Field1 <> rs1.Field1 Then
    recordChanged = True
  ElseIf rs2!Field2 <> rs1.Field2 Then
    recordChanged = True
  Else .....
  End If

  If recordChanged = True Then
    rs3.AddNew
    ..
    ..
    ..
    rs3.Update
  End If
  rs1.MoveNext
Loop

[Killer42]

I'm still of the opinion that you should not need to do the work in your code. It should be enough to execute a simple(?) SQL string for each of the three operations.

I just used the Find Unmatched Query Wizard to produce this query, which lists all the records from "yesterday" which have been deleted "today". It appear to work OK. Wouldn't you say this could be easily adapted to append these records to a "difference s" table rather than simply displaying them?

SELECT * FROM tbYesterday
  LEFT JOIN tbToday ON tbYesterday.KeyField = tbToday.KeyField
  WHERE (((tbToday.KeyField) Is Null));

Finding records which were added should be a simple matter of reversing this "unmatched query" to find records today which didn't exist yesterday. I'll attempt it here...

SELECT * FROM tbToday
  LEFT JOIN tbYesterday ON tbToday.KeyField = tbYesterday.KeyField
  WHERE (((tbYesterday.KeyField) Is Null));

As for the modified records, hm... I'll get back to you.

[Killer42]

Ok, I built a query which returns all records from today's file which existed yesterday but have been modified in any way. It's not pretty (and I hope I didn't stuff it up while changing field names to protect the innocent) but here goes...

SELECT tbToday.*, "U" As [Action] FROM tbToday
  INNER JOIN tbYesterday ON tbToday.JobNum = tbYesterday.JobNum
  WHERE ((([tbToday].[Field1] & [tbToday].[Field2] & [tbToday].[Field3])
  <>([tbYesterday].[Field1] & [tbYesterday].[Field2] & [tbYesterday].[Field3])));

Once again, this is just a SELECT query, but it shouldn't be too hard to adapt it to append the modified records to another table. (Note that just for fun, I've included an "action" field with a constant value of "U" for Update, so if this was being appended to an actions table you would be able to tell why. Something similar could be done to flag Added and Deleted records.)

Since this is actually fairly simple stuff, I'm guessing there must be a reason why the resident Access experts don't think it's the way to go. Anyone care to elaborate? I realise that while it appears to work, that doesn't automatically make it good design.

[NeoPa]

Like you Killer I'm of the opinion that SQL would work well for the basic concept. I'm a little less tolerant of members expecting the world when they can't even post replies to questions posted in their thread however. I've held off giving any time to this until at least the OP gets involved.
I didn't like to see you out on a limb though - hence my brief visit.

[Killer42]

Like you Killer I'm of the opinion that SQL would work well for the basic concept. I'm a little less tolerant of members expecting the world when they can't even post replies to questions posted in their thread however. I've held off giving any time to this until at least the OP gets involved.
I didn't like to see you out on a limb though - hence my brief visit.

Thanks NeoPa.

We just have to be patient, I guess. Not everyone has the time to hang around TheScripts every day.

[NeoPa]

That's not a problem Killer - I never expect that. I haven't unsubscribed from the thread or anything - just waiting patiently for a response. If they're not there they won't be missing an answer ;) Also I have plenty of other threads that do need more immediate attention.

[Kristaps]

Good morning ;)

Thanks, I will try these examples today... I hope so...
I newly begin new work, and there I meet thing what I never do before - programming. It is not my prior task of work, but...

Thanks a lot for helping... :)