See if values in one table exist in any field in all other tables

[jkirkland]

I want to see if any of the values in Table 1 appear in any of the fields in table 2 (actually in every table in the db)

Table1
FieldA
XYZ
ABC
123

Table2
FieldX FieldY FieldZ
Jane 25
Tom 12 XYZ
Joe 15 123

The field names in Table2 DO NOT match those in Table1.

The values in Table1, FieldA may not be in Table2 at all.

When a value from Table1 is found in Table2, I want to write the name of the table it was found in and the name of the field to a table called tblDocumentorProjs.

My code is as follows, but it keeps writing the name of table to my documentor table, but this table does not contain a value from Table1. I think the sequence of my events is off.

Would someone mind helping me figure this out? Thanks!
==========================================================
Dim db As DAO.Database
Dim rsDoc As DAO.Recordset 'RS: tblDocumentorProj
Dim tdf As DAO.TableDef
Dim rsOuter As DAO.Recordset
Dim rsInner As DAO.Recordset
Dim rsOuterFld As DAO.Field
Dim rsInnerFld As DAO.Field
Dim rsInnerFldMatch As DAO.Field
Dim blnJ As Boolean '> than 1 if table contained projects
Dim K As Integer '# of tbls that contain project vals

Set db = CurrentDb
Set rsDoc = db.OpenRecordset("tblDocumentorProj")
Set frm = Forms("form1")

DoCmd.RunSQL ("DELETE tblDocumentorProj.* FROM "
"&_tblDocumentorProj;")

i = 0
K = 0

For Each tdf In db.TableDefs
i = i + 1
blnJ = False
Set rsOuter = db.OpenRecordset("HTEDTA_GM180AP")
Set rsInner = db.OpenRecordset(tdf.Name)

'OUTER LOOP BEGINS
rsOuter.MoveFirst
Do While Not rsOuter.EOF
Set rsOuterFld = rsOuter("[GMPROJ]")

'INNER LOOP BEGINS
On Error Resume Next
If Err = 3021 Then
'Nothing
Else
rsInner.MoveFirst
Do While Not rsInner.EOF
For Each rsInnerFld In rsInner
If rsOuterFld = rsInnerFld Then
blnJ = True
rsInnerFldMatch = rsInnerFld.Name
Else
blnJ = False
rsInnerFldMatch = rsInnerFldMatch
End If
Next rsInnerFld
rsInner.MoveNext
Loop
End If
'INNER LOOP ENDS

rsOuter.MoveNext
Loop
' OUTER LOOP ENDS

If blnJ = True Then
MsgBox "blnJ = " & blnJ
K = K + 1
With rsDoc
.AddNew
!tblName = tdf.Name
!fldname = rsInnerFldMatch
!modDate = Now()
!modUser = f_getUser()
.Update
End With
End If
Next tdf
'==========================================================
'Cleanup code, etc

 

[jkirkland]

I guess this is too difficult to do?

 

[BoxHead]

It's not too difficult, it's just somewhat complicated for me to get my head wrapped around it.

I don't see where you're actually checking each value from your first table against each field in every other table. I used a DCount for that.

I copied a table of dates that I had in my testing database (copyOfDates), deleted all but ten random records and ran a sub off a command button to check all* the other tables for any occurences of each value in the first fied of that table.

Note that I did not check it against itself, against tblDocumentorProjs or against the system tables that start with MSys.

Also notice that I included the matching value into the append query with a third field in the tblDocumentorProjs. The results just didn't have much meaning for me without that.

You probably won't need the pound signs (#) in your DCount statement if you're not checking dates but you may need to add some double quotes around the rs1.fields(0) reference.

Make a backup and consider putting in a counter so it will exit the loop after adding 50 records or so to get an idea of how long this sub will take to run. I was only checking ten values against 44 tables with 6-10 fields in each and it takes about 15 seconds to return 33 matches.

The code below opens a recordset, goes to the first column of the first table in the tblDef collection and uses DCount to see if the first record of the recordset exists in that column. If it does, the field name, the table name and the value are appended to tblDoumentorProjs. It then checks the next record in the recordset and so on until it reachs the end of the recordset. Then it goes to the next column/field in that table and when it is through with that table it moves on to the next table and does it all over again.

Private Sub Command51_Click()
Dim db1 As DAO.Database, rs1 As DAO.Recordset, tdfLoop As TableDef
Dim myAppend As String, myDelete As String, myFldName As String, myTblName As String
Dim intI As Integer
DoCmd.SetWarnings False
myDelete = "DELETE * FROM tblDocumentorProjs;"
DoCmd.RunSQL myDelete

Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("SELECT * FROM copyOfDates;", dbOpenDynaset)
For Each tdfLoop In db1.TableDefs
If tdfLoop.Name <> "tblDocumentorProjs" And tdfLoop.Name <> "copyOfDates" And Left(tdfLoop.Name, 4) <> "msys" Then
myTblName = tdfLoop.Name
rs1.MoveFirst
Do While Not rs1.EOF
For intI = 0 To tdfLoop.Fields.Count - 1
myFldName = tdfLoop.Fields(intI).Name
If DCount("*", myTblName, "[" & myFldName & "] = " & "#" & rs1.Fields(0) & "#") > 0 Then
myAppend = "INSERT INTO tblDocumentorProjs ( FldName, tblName,myVal ) SELECT " & """" & myFldName & """" & ", " & """" & myTblName & """" & ", " & """" & rs1.Fields(0) & """" & ";"
DoCmd.RunSQL myAppend
End If
Next intI
rs1.MoveNext
Loop
End If
Next tdfLoop
DoCmd.SetWarnings True
End Sub

HTH


John

Use what you have,
Learn what you can,
Create what you need.