SQL for Quering Access Tables from a Table/Field List Table

[robboll]

I have a Table called TableField that has the table names, fields and
Data type of all the tables and fields in the database. They are set
up as follows:

Table: TableField

TableName FieldName Type
Table1 LName Text
Table1 FName Text
Table1 Address1 Text
Table1 Address2 Text
Table1 State Text
Table1 Zip Text
Table2 Color Text
Table2 Descrip Text
Table2 Size Text
Table2 Temp Text

 

[Ken Snell [MVP]]

This may get you started:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strFind As String, strSQL As String
strFind = InputBox("Enter the field name fragment:")
strSQL = "SELECT * FROM TableField WHERE " & _
"FieldName Like '*" & strFind & "*';"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
With rst
If .EOF = False And .BOF = False Then
.MoveFirst
Do While .EOF = False
Debug.Print "Field " & .Fields(1).Value & " in Table " & _
.Fields(0).Value
.MoveNext
Loop
Else
MsgBox "No such fields found."
End If
.Close
End With
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[robboll]

Ken,

When I apply your code I get the "No such fields found." message but I
know the value that I entered is in one of the tables. Thanks for your
effort, I'll keep trying.

 

[robboll]

Ken,

After trying to get your code to work, I came up with a hybrid of what
I started and what you are suggesting. It looks like it should work,
but nothing is getting appended. Do you have any suggestions.

Note that xyzFields provides fields: TableName, FieldName and DataType.
And xyzResults is the same structure. The routine is supposed to
append records to xyzResults only when the strFind condition is met.

The entire routine is supposed to look for the value that you key in in
every table in the database. If it finds the value, it appends the
TableName and the FieldName into xyzResults.

I appreciate your help with this.

Function myfun()
On Error Resume Next

Dim db As Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

strSQL = "INSERT INTO xyzResults SELECT from " & mTable &
" where " & mField & " Like " & "'*'" & strFIND & "'*'"
CurrentDb.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With

End Function

 

[Thomas T via AccessMonster.com]

strSQL = "INSERT INTO xyzResults SELECT from " & mTable &

" where " & mField & " Like " & "'*'" & strFIND & "'*'"

try:

"INSTER INTO xyzResults SELECT FROM " & mTable & " WHERE " & mField & " Like
" & "'*" & strFIND & "*'"

instead. (Only difference is I removed two ') Now it should look like:

mfield Like '*strFIND*'

instead of

mField Like '*'strFIND'*'

 

[robboll]

Thanks - That cleans up my syntax but unfortunately doesn't solve the
problem. Still nothing is being appended to the database. It's
probably something pretty simple, but I am not seeing it.

Thanks for the tip.

RBollinger

 

[Ken Snell [MVP]]

Your code never "filters" your xyzFields table based on the fragment that
you are seeking. If you look at the code that I'd posted, I use the string
fragment to obtain a recordset that contains the records where the FieldName
contains that fragment, and then I loop through that recordset to read its
records.

You said that you tried my code and it always said "no such field"...did you
try the code exactly as posted? or did you change it? If you changed it, how
did you change it?

Your code might be better this way (I am assuming that the field names in
xyzResults table are the same as in xyzFields table):

Function myfun()
On Error Resume Next

Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table
strSQL = "SELECT * FROM xyzFields WHERE FieldName " & _
"Like '*" & strFIND & "*';"
Set rsXYZFields = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = Trim(.Fields(0))
mField = Trim(.Fields(1))

strSQL = "INSERT INTO xyzResults ( TableName, " & _
"FieldName ) VALUES ( '" & mTable & "', '" & _
mField & "' )"
CurrentDb.Execute strSQL, dbFailOnError
If Err.Number <> 0 Then MsgBox "Error occurred!!!!"
Err.Clear
.MoveNext
Loop
End With

End Function

--

Ken Snell
<MS ACCESS MVP>

Ken,

After trying to get your code to work, I came up with a hybrid of what
I started and what you are suggesting. It looks like it should work,
but nothing is getting appended. Do you have any suggestions.

Note that xyzFields provides fields: TableName, FieldName and DataType.
And xyzResults is the same structure. The routine is supposed to
append records to xyzResults only when the strFind condition is met.

The entire routine is supposed to look for the value that you key in in
every table in the database. If it finds the value, it appends the
TableName and the FieldName into xyzResults.

I appreciate your help with this.

Function myfun()
On Error Resume Next

Dim db As Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

strSQL = "INSERT INTO xyzResults SELECT from " & mTable &
" where " & mField & " Like " & "'*'" & strFIND & "'*'"
CurrentDb.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With

End Function

 

[robboll]

Ken,

When I enter a value that I know is in one of the test tables, it moves
from Do until .EOF to End With, and I am prompted to enter the value
again. Apparently when there is nothing in the record set it breaks
out of the loop.

 

[Ken Snell [MVP]]

If you're entering a value that you "know" is in the table, and it's not
being found, then I would venture that what you're entering is not what is
in the table. Show us an example of what you're entering when the input box
pops up, and what it's supposed to be matching in the table.

 

[Ken Snell [MVP]]

By the way, is FieldName a generic name for the actual field name in
xyzFields table? If it is, it must be replaced by the real name. I used
FieldName because that is the name you posted in an earlier message as the
name of the field. But I note that you are using a different name now for
the table than you did in that earlier message, so I am wondering if the
field name also is different.

Here is what you posted earlier:

Table: TableField

TableName FieldName Type


But you're now using xyzFields as the table name, not TableField.

 

[robboll]

Ken,

Sorry about the mixup with the two posts. To stay consistent with the
last post.

The Tablename with the table names and field names is:
xyzFields

Field Names:
TableName, FieldName, DataType

The Tablename for the results set:
xyzResults
It is the same structure as xyzFields, but with a different name.

For testing I am only using one table called customers. One of the
fields of the customer database is: fname, and one of the names is
Brenda. I entered it that way without quotes.

When I tried the code from your last post it never made it to fname.
The rs apparently had no records because it went from Do Until .EOF to
EndWith, and prompted me to enter another name.

Where rsXYZFields associated with table xyzFields?

Thanks for your help with this.

 

[Ken Snell [MVP]]

OK - I am confused. Are you wanting to find a field that is named "fname"?
or find a record in a table where "Brenda" is the value of a field in that
table's record?

 

[robboll]

Oh gee -- how confusing can I make it???? let me try again.

Table with TableNames and FieldNames:
xyzFields

Table with the Results (Same Structure):
xyzResults

 

[robboll]

If I enter "Brenda" it goes through each table in the database and
appends the TableName and the FieldName of the find into xyzResults.

 

[robboll]

If I enter "Brenda" it goes through each table in the database and
appends the TableName and the FieldName of the find into xyzResults.

 

[Ken Snell [MVP]]

Excuse me for my slow understanding.... let me restate what I am reading:
the purpose of the code that you want to use is to find the text string
fragment "Brenda" in the entire database -- by searching every field in
every record in every table to find that string fragment, and then to give
you a list of the field and table where you found at least one record that
has the text string fragment "Brenda" within the string that is the value in
a field in a table?

If I am now understanding what you are wanting to do, then the code that I
posted is not going to do that.

But what you want is an amazingly huge overhead for the database. In normal
operations, one usually does a search for information in a specific field
and table. I'm curious about the context for doing this massive, global
search? And I'm not sure how the result is helpful...simply listing the
field and table where you find a string value of "Brenda" doesn't tell you
which record in that table actually contains that string fragment.

Am I now correctly understanding what you want to do?

 

[robboll]

Ken,

To step you precisely what I am doing, I first run this block of code
in any MDB. It generates xyzTables and xyzFields and populates them
with complete schema information of all existing tables.

Public Sub DocumentTables()
Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
Dim rstTable As DAO.Recordset, rstField As DAO.Recordset
Dim t As String, strQuery As String
Dim strTableSet As String, strFieldSet As String

strSQL = "CREATE TABLE XYZTables " & _
"(TableName TEXT CONSTRAINT " & _
" PrimaryKey PRIMARY KEY, " & _
" TableRecords Number) "
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "CREATE TABLE XYZFields " & _
"(TableName CHAR, " & _
"FieldName CHAR, " & _
"DataType CHAR, " & _
"DataSize Number, " & _
"FieldDesc CHAR) "
CurrentDb.Execute strSQL, dbFailOnError

strTableSet = "xyzTables"
strFieldSet = "xyzFields"

DoCmd.SetWarnings False
DoCmd.runsql "DELETE * FROM [" & strTableSet & "];"
DoCmd.runsql "DELETE * FROM [" & strFieldSet & "];"
DoCmd.SetWarnings True

Set db = CurrentDb
Set rstTable = db.OpenRecordset(strTableSet, dbOpenDynaset)
Set rstField = db.OpenRecordset(strFieldSet, dbOpenDynaset)

Debug.Print

For Each tbl In db.TableDefs
Debug.Print tbl.Name
If tbl.Attributes = 0 Then
With rstTable
.AddNew
.Fields("TableName") = tbl.Name
.Fields("TableRecords") = tbl.RecordCount
On Error Resume Next
.Fields("TableDesc") =
tbl.Properties("Description").Value
On Error GoTo 0
.Update
End With
For Each fld In tbl.Fields
'add new record for each field in each table,
containing
'table, field, data type of field
With rstField
.AddNew
.Fields("TableName").Value = tbl.Name
.Fields("FieldName").Value = fld.Name
.Fields("DataType").Value =
GetFieldDataType(fld.Type)
.Fields("DataSize").Value = fld.Size
On Error Resume Next
.Fields("FieldDesc").Value =
fld.Properties("Description").Value
On Error GoTo 0
.Update
End With
Next fld
End If
Next tbl

Debug.Print

rstField.Close
rstTable.Close
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Then, this is where you received my sketchy description.

Using xyzField which is a complete list of all the tables and fields, I
am trying to put together a routine that skips through that table and
any time it finds "Brenda", or whatever, it appends the table and field
into that table. That is why I started with this approach:

Function InterrogateDB()
On Error Resume Next

Dim db As Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

strSQL = "Select " & mField & "from " & mTable & "where "
& mField Like strFIND
CurrentDb.Execute strSQL, dbFailOnError
If strSQL = True Then
strSQL = "INSERT INTO xyzResults ( TableName, " & _
"FieldName ) VALUES ( '" & mTable & "', '" & _
mField & "' )"
End If
.MoveNext
Loop
End With



End Function

 

[Ken Snell [MVP]]

OK - I think I am on the same page now.... this code should do what you
seek:


Function InterrogateDB()
On Error Resume Next

Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

If DCount("*", mTable, mField & " Like '*" & _
strFIND & "*'") > 0 Then
strSQL = "INSERT INTO xyzResults ( TableName, " & _
"FieldName ) VALUES ( '" & mTable & "', '" & _
mField & "' )"
End If
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing

End Function

--

Ken Snell
<MS ACCESS MVP>

Ken,

To step you precisely what I am doing, I first run this block of code
in any MDB. It generates xyzTables and xyzFields and populates them
with complete schema information of all existing tables.

Public Sub DocumentTables()
Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
Dim rstTable As DAO.Recordset, rstField As DAO.Recordset
Dim t As String, strQuery As String
Dim strTableSet As String, strFieldSet As String

strSQL = "CREATE TABLE XYZTables " & _
"(TableName TEXT CONSTRAINT " & _
" PrimaryKey PRIMARY KEY, " & _
" TableRecords Number) "
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "CREATE TABLE XYZFields " & _
"(TableName CHAR, " & _
"FieldName CHAR, " & _
"DataType CHAR, " & _
"DataSize Number, " & _
"FieldDesc CHAR) "
CurrentDb.Execute strSQL, dbFailOnError

strTableSet = "xyzTables"
strFieldSet = "xyzFields"

DoCmd.SetWarnings False
DoCmd.runsql "DELETE * FROM [" & strTableSet & "];"
DoCmd.runsql "DELETE * FROM [" & strFieldSet & "];"
DoCmd.SetWarnings True

Set db = CurrentDb
Set rstTable = db.OpenRecordset(strTableSet, dbOpenDynaset)
Set rstField = db.OpenRecordset(strFieldSet, dbOpenDynaset)

Debug.Print

For Each tbl In db.TableDefs
Debug.Print tbl.Name
If tbl.Attributes = 0 Then
With rstTable
.AddNew
.Fields("TableName") = tbl.Name
.Fields("TableRecords") = tbl.RecordCount
On Error Resume Next
.Fields("TableDesc") =
tbl.Properties("Description").Value
On Error GoTo 0
.Update
End With
For Each fld In tbl.Fields
'add new record for each field in each table,
containing
'table, field, data type of field
With rstField
.AddNew
.Fields("TableName").Value = tbl.Name
.Fields("FieldName").Value = fld.Name
.Fields("DataType").Value =
GetFieldDataType(fld.Type)
.Fields("DataSize").Value = fld.Size
On Error Resume Next
.Fields("FieldDesc").Value =
fld.Properties("Description").Value
On Error GoTo 0
.Update
End With
Next fld
End If
Next tbl

Debug.Print

rstField.Close
rstTable.Close
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Then, this is where you received my sketchy description.

Using xyzField which is a complete list of all the tables and fields, I
am trying to put together a routine that skips through that table and
any time it finds "Brenda", or whatever, it appends the table and field
into that table. That is why I started with this approach:

Function InterrogateDB()
On Error Resume Next

Dim db As Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

strSQL = "Select " & mField & "from " & mTable & "where "
& mField Like strFIND
CurrentDb.Execute strSQL, dbFailOnError
If strSQL = True Then
strSQL = "INSERT INTO xyzResults ( TableName, " & _
"FieldName ) VALUES ( '" & mTable & "', '" & _
mField & "' )"
End If
.MoveNext
Loop
End With



End Function

 

[Ken Snell [MVP]]

whoops... omitted a few lines...


Function InterrogateDB()
On Error GoTo Err_Line

Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

If DCount("*", mTable, mField & " Like '*" & _
strFIND & "*'") > 0 Then
strSQL = "INSERT INTO xyzResults ( TableName, " & _
"FieldName ) VALUES ( '" & mTable & "', '" & _
mField & "' )"
db.Execute strSQL, dbFailOnError
End If
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing
Exit Function

Err_Line:
MsgBox "Error occurred when inserting record"
Resume Next

End Function

--

Ken Snell
<MS ACCESS MVP>

OK - I think I am on the same page now.... this code should do what you
seek:


Function InterrogateDB()
On Error Resume Next

Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

If DCount("*", mTable, mField & " Like '*" & _
strFIND & "*'") > 0 Then
strSQL = "INSERT INTO xyzResults ( TableName, " & _
"FieldName ) VALUES ( '" & mTable & "', '" & _
mField & "' )"
End If
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing

End Function

--

Ken Snell
<MS ACCESS MVP>

Ken,

To step you precisely what I am doing, I first run this block of code
in any MDB. It generates xyzTables and xyzFields and populates them
with complete schema information of all existing tables.

Public Sub DocumentTables()
Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
Dim rstTable As DAO.Recordset, rstField As DAO.Recordset
Dim t As String, strQuery As String
Dim strTableSet As String, strFieldSet As String

strSQL = "CREATE TABLE XYZTables " & _
"(TableName TEXT CONSTRAINT " & _
" PrimaryKey PRIMARY KEY, " & _
" TableRecords Number) "
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "CREATE TABLE XYZFields " & _
"(TableName CHAR, " & _
"FieldName CHAR, " & _
"DataType CHAR, " & _
"DataSize Number, " & _
"FieldDesc CHAR) "
CurrentDb.Execute strSQL, dbFailOnError

strTableSet = "xyzTables"
strFieldSet = "xyzFields"

DoCmd.SetWarnings False
DoCmd.runsql "DELETE * FROM [" & strTableSet & "];"
DoCmd.runsql "DELETE * FROM [" & strFieldSet & "];"
DoCmd.SetWarnings True

Set db = CurrentDb
Set rstTable = db.OpenRecordset(strTableSet, dbOpenDynaset)
Set rstField = db.OpenRecordset(strFieldSet, dbOpenDynaset)

Debug.Print

For Each tbl In db.TableDefs
Debug.Print tbl.Name
If tbl.Attributes = 0 Then
With rstTable
.AddNew
.Fields("TableName") = tbl.Name
.Fields("TableRecords") = tbl.RecordCount
On Error Resume Next
.Fields("TableDesc") =
tbl.Properties("Description").Value
On Error GoTo 0
.Update
End With
For Each fld In tbl.Fields
'add new record for each field in each table,
containing
'table, field, data type of field
With rstField
.AddNew
.Fields("TableName").Value = tbl.Name
.Fields("FieldName").Value = fld.Name
.Fields("DataType").Value =
GetFieldDataType(fld.Type)
.Fields("DataSize").Value = fld.Size
On Error Resume Next
.Fields("FieldDesc").Value =
fld.Properties("Description").Value
On Error GoTo 0
.Update
End With
Next fld
End If
Next tbl

Debug.Print

rstField.Close
rstTable.Close
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Then, this is where you received my sketchy description.

Using xyzField which is a complete list of all the tables and fields, I
am trying to put together a routine that skips through that table and
any time it finds "Brenda", or whatever, it appends the table and field
into that table. That is why I started with this approach:

Function InterrogateDB()
On Error Resume Next

Dim db As Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")

Set db = CurrentDb
'Open the Table/Fields table

Set rsXYZFields = db.OpenRecordset("xyzFields", dbOpenSnapshot)

With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"

strSQL = "Select " & mField & "from " & mTable & "where "
& mField Like strFIND
CurrentDb.Execute strSQL, dbFailOnError
If strSQL = True Then
strSQL = "INSERT INTO xyzResults ( TableName, " & _
"FieldName ) VALUES ( '" & mTable & "', '" & _
mField & "' )"
End If
.MoveNext
Loop
End With



End Function

 

[robboll]

Ken,

Works like a champ.

Thank you very much. You are AWESOME!!!!!!