[AccessD] Looping through table Fields

David:
You could try this DAO code - rough and ready but you could adapt

Max

Option Compare Database
Option Explicit


Private Sub Text()
    Call sCheckFldContents(1, 2)
End Sub

Private Sub sCheckFldContents(ID1 As Long, ID2 As Long)
    Dim sql As String, fld As Field
    Dim rst1 As dao.Recordset, rst2 As dao.Recordset

    Set rst1 = CurrentDb.OpenRecordset("Select * from tblWhatEver where ID="
& ID1)
    Set rst2 = CurrentDb.OpenRecordset("Select * from tblWhatEver where ID="
& ID2)

' comment out these next 2 lines the first time you run it.
    sql = "Drop Table tblCheckContents"
    CurrentDb.Execute (sql)
   
    sql = "Create Table tblCheckContents (ID1 long,ID2 long,fldName
text,ID1Contents text,ID2Contents text)"
    CurrentDb.Execute (sql)

    For Each fld In rst1.Fields
        If rst1(fld.Name) <> rst2(fld.Name) Then
           ' Debug.Print fld.Name
            sql = "Insert into tblCheckContents
(ID1,ID2,fldname,ID1Contents,ID2Contents) " & _
            " values (" & ID1 & "," & ID2 & ",'" & fld.Name & "','" &
rst1(fld.Name) & "','" & rst2(fld.Name) & "')"
            Debug.Print sql
            CurrentDb.Execute (sql)
        End If
    Next fld

End Sub




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: 26 June 2009 22:45
To: accessd at databaseadvisors.com
Subject: [AccessD] Looping through table Fields

I tried this in the SQL list but now response.

I have an SQL2000 database that we require to compare two records 
field by field and report back on the fields that have different values.

Can someone please point me in the right direction of how to do this 
within a stored procedure?

I know I could hard code each field but the full project has several 
tables with over 400 fields.  I would rather lop through a fields 
collection to do the comparison.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand  

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com