Identifying fields in a recordset where a wild card is used in SQL statement

[gssstuff]

I have a piece of code I use to compare two identically structured
tables. There are 15+ sets of tables I am comparing. I am looking to
see what has changed between the "old" and "new" versions of the table.
Any changes get reported to an audit file which contains the index,
field name and field value of the records that have changed. I have
the changes nailed for modifications, but cannot seem to get a handle
on adds/deletes.

Due to the somewhat dynamic nature of the tables I am comparing, I
wanted to avoid having to create separate queries for each set of
tables. Here is the statement I am using to do the "Find Unmatched":

Set tmpb = db.OpenRecordset("Select * from " & btbl & " left join " &
ctbl & " on " & btbl & "." & cfield & " = " & ctbl & "." & cfield & "
Where " & ctbl & "." & cfield & " is null;")

btbl = baseline table, ctbl = current table, cfield = common field
between the tables

This runs just fine.

Now I want to see the actual fields that are part of this recordset.

From there, I will go field by field and append the field name and

field value into an audit table.

I have a hunch I should do something involving TableDef with this, but
cannot seem to figure it out.

Any ideas?

[pietlinden]

This might be long, but I'll try to keep it as simple as possible...

You're using DAO... good, Makes life easier for me!

What you need to do is just walk down the hierarchy a little further.

database ==> tabledefs collection ===> fields collection of tabledef.

ONE way of doing this is to break out the "get the delimited field list
from the table" as a separate function - it makes it possibly reuseable
and MUCH easier to debug, should you make a mistake somewhere.

Here's part of the code... tweak as necessary...
'--BEGIN CODE

Option Compare Database
Option Explicit

Public Function GetFieldList(ByVal strTable As String, Optional ByVal
strSep As String, Optional ByVal blnIncludeTableNames As Boolean) As
String

'INPUTS:
'1. strTable - the name of the table you want the fields from
'2. strSep - the delimiter you want between your fields, like
comma-space, or semicolon (good if you want to create a rowsource for a
listbox)
'3. blnIncludeTableNames - if you need the tablenames included, i.e.
"TableA.FieldA1" instead of just FieldA1, set this to True)

Dim db As DAO.database
Dim tdf As DAO.tabledef
Dim intCounter As Integer

If IsMissing(strSep) Then
strSep = ", "
End If

If IsMissing(blnIncludeTableNames) Then
blnIncludeTableNames = False
End If

Set db = CurrentDb
Set tdf = db.tabledefs(strTable)

For intCounter = 0 To tdf.Fields.Count - 1
If Len(GetFieldList) = 0 Then
If blnIncludeTableNames Then
GetFieldList = "[" & tdf.Name & "].[" &
tdf.Fields(intCounter).Name & "]"
End If
Else
GetFieldList = GetFieldList & strSep & "[" & tdf.Name &
"].[" & tdf.Fields(intCounter).Name & "]"
End If
Next intCounter

Set tdf = Nothing
Set db = Nothing
End Function

'---END CODE

Okay, so then you have to create your query SQL in another function.

Function QuerySQL(byval strTableA as string, byval strTableB as string,
byval strDelimiter, blnIncludeTableNames as boolean) As string
' --- call the above function for both tables to get the list of
fields.
' --- prepend the "SELECT "
' --- create and add the "FROM tableA LEFT JOIN tableB ON A.PK=B.FK...
statement
' --- add the WHERE statement, if necessary.
End Function

Isn't this fun? Man, after this, I *really* think the QBE grid is
cool!!! <g>

Hope this gets you started!

Pieter

[pietlinden]

sorry, me again. You might want to look at this article and see if
this might help you in the future...

http://members.iinet.net.au/~allenbrowne/AppAudit.html
It does all the logging for you.