comparing two tables and via vba

**webisti** · 03-14-2014, 09:44 AM

Hi I am doing a test on finding out errors or changes done in a table.

The table is practically the same table, once imported into access as Before the load (B) and then as After the load(A)
another person in my team is updating one or more of the fields (thousands of records).
These changes are reflected on the copy of the table as A (after the load)
In the table I do have 218 Fields(columns).
Is it possible via a module to create a comparison between the tables B (before) and A (after) and find out all the records where the change has occurred?
sample code of the query

Code:

[SELECT A.ORDERID, A.VENDOR, IIf([B]![ORDER_ID]=[A]![ORDERID],"OK","CHANGED") AS 1R, IIf([B]![VENDOR]=[A]![VENDOR],"OK","CHANGED") AS 2R,IIf([B]![CRITICAL]=[A]![CRITICAL],"OK","CHANGED") AS 3R,
IIf([B]![PRICE]=[A]![PRICE],"OK","CHANGED") AS 4R
FROM A LEFT JOIN B ON (A.ORDERID = B.ORDERID) AND (A.VENDOR = B.VENDOR);

I just do not want to do the same manual sql for the rest of the 218 fields.
Any ideas on how to do that?

Thanks a lot

Webisti

**June7** · 03-14-2014, 11:03 AM

218 fields is a LOT of fields for one table.

You don't want to build an IIf expression for every pair?

VBA could do the comparison.

**orange** · 03-14-2014, 11:28 AM

Fields in a table have an index. If you have 218 fields, I think the fields index will be 0 to 217.
You could automate the creation of the sql or recordset.

Here's a sample vba routine to listFields by Index. It should give you some ideas.
This sample just deals with table names beginning with "b".

Code:

'---------------------------------------------------------------------------------------
' Procedure : ListFieldsByIndexNumberInTables
' Author    : Jack
' Date      : 01/03/2014
' Purpose   : Sample code to look at fields in tables. Print Fields by Index
'---------------------------------------------------------------------------------------
'
Sub ListFieldsByIndexNumberInTables()
          Dim I As Integer
          Dim tbf As DAO.TableDef
          Dim fld As DAO.Field
          Dim db As DAO.Database
10       On Error GoTo ListFieldsByIndexNumberInTables_Error

20        Set db = CurrentDb
30        For Each tbf In db.TableDefs
40            If Left(tbf.name, 1) = "b" Then
50                Debug.Print tbf.name
60                For I = 0 To tbf.Fields.Count - 1
70                    Debug.Print vbTab & "fields(" & I & ")  " & tbf.Fields(I).name
80                Next
90            End If
100       Next

110      On Error GoTo 0
120      Exit Sub

ListFieldsByIndexNumberInTables_Error:

130       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ListFieldsByIndexNumberInTables of Module Module1"
End Sub

**webisti** · 03-17-2014, 03:13 AM

I am lost now :-(

How would you implement that as a whole. Is it possible to have it as a sample?

Webisti

**orange** · 03-17-2014, 06:43 AM

Where exactly are you "lost"?

To use this code as an example:
This is a vba procedure.
You put this procedure in a module that you would create.
You go to the procedure, put your cursor anywhere in that procedure and press F5.

----------as identified in the procedure, the current code is limited to tables beginning with "b"

If you don't know what vba is, that's a whole different story.

Can you look at the code line by line and "make an educated guess" as to what it means/represents?

For vba, Start here.

**webisti** · 03-17-2014, 07:01 AM

I managed to dig some code in vba but not sure if this will work

Code:

Sub CompareTablesQuery(strTable1 As String, strTable2 As String, _
    strLink As String, strQuery As String)
  Dim strSelect As String
  Dim strWhere As String
  Dim strSQL As String
 
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim fld As DAO.Field
 
  On Error GoTo ErrHandler
 
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strTable1, dbOpenDynaset)
 
  For Each fld In rst.Fields
    strSelect = strSelect & ", [" & strTable1 & "].[" & fld.Name & "], [" & _
      strTable2 & "].[" & fld.Name & "]"
    If fld.Name <> strLink Then
      strWhere = strWhere & " OR ([" & strTable1 & "].[" & fld.Name & _
        "] <> [" & strTable2 & "].[" & fld.Name & "]) OR ([" & strTable1 & _
        "].[" & fld.Name & "] Is Not Null AND [" & strTable2 & "].[" & _
        fld.Name & "] Is Null) OR ([" & strTable1 & "].[" & fld.Name & _
        "] Is Null AND [" & strTable2 & "].[" & fld.Name & "] Is Not Null)"
    End If
  Next fld
 
  strSelect = Mid(strSelect, 3)
  strWhere = Mid(strWhere, 5)
 
  strSQL = "SELECT " & strSelect & " FROM [" & strTable1 & _
    "] INNER JOIN [" & strTable2 & "] ON [" & strTable1 & "].[" & strLink & _
    "] = [" & strTable2 & "].[" & strLink & "] WHERE " & strWhere
 
  On Error Resume Next
  dbs.QueryDefs.Delete strQuery
  On Error GoTo ErrHandler
  dbs.CreateQueryDef strQuery, strSQL
 
ExitHandler:
  On Error Resume Next
  Set fld = Nothing
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  Exit Sub
 
ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

and to be used as : Compare2Tables "tblOrders", "tblOrders2", "pkeyOrderID", "qryCompare" (table names are sample) I tried it but I get an error Argument not optional, when I put it under a command button.
any ideas on how to make it work?

Webisti

**webisti** · 03-17-2014, 07:36 AM

Orange
I tried your code but it shows me nothing..so I do not know where is the "error" "-) And of course I know vba otherwise I would post the thread into another section...

webisti

**orange** · 03-17-2014, 09:30 AM

Take the code I gave you, put a single quote (') at the start of line 40 and line 90.
That will make the lines comments which means they will not be executed.

Then do the F5 thing I mentioned. We'll get there.
Post back with results.

**June7** · 03-17-2014, 10:03 AM

Refer to link at bottom of my post for debugging guidelines.

**webisti** · 03-17-2014, 10:51 AM

ok I give up for today...is never ending (running all the time).
will continue tomorrow

Webisti

**orange** · 03-17-2014, 11:04 AM

What are you talking about?
Who is your comment addressed to?

Adding 2 single quotes and pushing a function key F5 can not take too much effort.................

**webisti** · 03-17-2014, 11:06 AM

Orange I tried to do what you said.. but the result is just :

B
fields(0) TABLEID
fields(1) BSKRF
fields(2) DISKZ
fields(3) DLINL
fields(4) EINME
fields(5) ERSDA
fields(6) EXPPG
fields(7) EXVER
fields(8) HERKL
fields(9) INSME
fields(10) KEINM
fields(11) KINSM
fields(12) KLABS
fields(13) KSPEM
fields(14) KZILE
fields(15) KZILL
fields(16) KZVLE
fields(17) KZVLL
fields(18) KZVLQ
fields(19) KZVLS
fields(20) LABST
fields(21) LBSTF
fields(22) LFGJA
fields(23) LFMON
fields(24) LGORT
fields(25) LGPBE
fields(26) LVORM
fields(27) LWMKB
fields(28) MANDT
fields(29) MATNR
fields(30) MDJIN
fields(31) MDRUE
fields(32) PSTAT
fields(33) RETME
fields(34) SPEME
fields(35) SPERR
fields(36) VKLAB
fields(37) VKUML
fields(38) VMEIN
fields(39) VMINS
fields(40) VMLAB
fields(41) VMRET
fields(42) VMSPE
fields(43) VMUML
fields(44) WERKS

**orange** · 03-17-2014, 11:21 AM

Did you put the single quotes on the lines as mentioned in a previous post?
What I see is a list of 45 field names in your Table.

**webisti** · 03-17-2014, 11:30 AM

Here it is :

B
fields(0) TABLEID
fields(1) BSKRF
fields(2) DISKZ
fields(3) DLINL
fields(4) EINME
fields(5) ERSDA
fields(6) EXPPG
fields(7) EXVER
fields(8) HERKL
fields(9) INSME
fields(10) KEINM
fields(11) KINSM
fields(12) KLABS
fields(13) KSPEM
fields(14) KZILE
fields(15) KZILL
fields(16) KZVLE
fields(17) KZVLL
fields(18) KZVLQ
fields(19) KZVLS
fields(20) LABST
fields(21) LBSTF
fields(22) LFGJA
fields(23) LFMON fields(24) LGORT
fields(25) LGPBE
fields(26) LVORM
fields(27) LWMKB
fields(28) MANDT
fields(29) MATNR
fields(30) MDJIN
fields(31) MDRUE
fields(32) PSTAT
fields(33) RETME
fields(34) SPEME
fields(35) SPERR
fields(36) VKLAB
fields(37) VKUML
fields(38) VMEIN
fields(39) VMINS
fields(40) VMLAB
fields(41) VMRET
fields(42) VMSPE
fields(43) VMUML
fields(44) WERKS
MSysAccessStorage
fields(0) DateCreate
fields(1) DateUpdate
fields(2) Id
fields(3) Lv
fields(4) Name
fields(5) ParentId
fields(6) Type
MSysAccessXML
fields(0) Id
fields(1) LValue fields(2) ObjectGuid
fields(3) ObjectName
fields(4) Property
fields(5) Value
MSysACEs
fields(0) ACM
fields(1) FInheritable
fields(2) ObjectId
fields(3) SID
MSysIMEXColumns
fields(0) Attributes
fields(1) DataType
fields(2) FieldName
fields(3) IndexType
fields(4) SkipColumn
fields(5) SpecID
fields(6) Start
fields(7) Width
MSysIMEXSpecs
fields(0) DateDelim
fields(1) DateFourDigitYear
fields(2) DateLeadingZeros
fields(3) DateOrder
fields(4) DecimalPoint
fields(5) FieldSeparator
fields(6) FileType
fields(7) SpecID
fields(8) SpecName
fields(9) SpecType
fields(10) StartRow
fields(11) TextDelim
fields(12) TimeDelim
MSysNameMap
fields(0) GUID
fields(1) Id
fields(2) Name
fields(3) NameMap
fields(4) Type
MSysNavPaneGroupCategories
fields(0) Filter
fields(1) Flags
fields(2) Id
fields(3) Name
fields(4) Position
fields(5) SelectedObjectID
fields(6) Type
BMSysNavPaneGroups
fields(0) Flags
fields(1) GroupCategoryID
fields(2) Id
fields(3) Name
fields(4) Object Type Group
fields(5) ObjectID
fields(6) Position
MSysNavPaneGroupToObjects
fields(0) Flags
fields(1) GroupID
fields(2) Icon
fields(3) Id
fields(4) Name
fields(5) ObjectID
fields(6) Position
MSysNavPaneObjectIDs
fields(0) Id
fields(1) Name
fields(2) Type
MSysObjects
fields(0) Connect
fields(1) Database
fields(2) DateCreate
fields(3) DateUpdate
fields(4) Flags
fields(5) ForeignName
fields(6) Id
fields(7) Lv
fields(8) LvExtra
fields(9) LvModule
fields(10) LvProp
fields(11) Name
fields(12) Owner
fields(13) ParentId
fields(14) RmtInfoLong
fields(15) RmtInfoShort
fields(16) Type
MSysQueries
fields(0) Attribute
fields(1) Expression
fields(2) Flag
fields(3) LvExtra
fields(4) Name1
fields(5) Name2
fields(6) ObjectId
fields(7) Order
MSysRelationships
fields(0) ccolumn
fields(1) grbit
fields(2) icolumn
fields(3) szColumn
fields(4) szObject
fields(5) szReferencedColumn
fields(6) szReferencedObject
fields(7) szRelationship

**orange** · 03-17-2014, 11:35 AM

So where is your TableA ???

This is B and 45 fields; the other tables are System Tables beginning with MSys*.

comparing two tables and via vba

Thread Tools

comparing two tables and via vba

Similar Threads

Comparing two tables

Comparing two tables

Comparing two tables.

Comparing two tables.

Comparing tables

Posting Permissions