[AccessD] Looping through table Fields

Thanks Max,

I already have ADO code that I use for the Access version of the 
programme, but the SQL version requires to be run in a stored procedure.

Eric has given me some SQL equivalent to look at.

Regards

David

At 27/06/2009, you wrote:
>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