A couple of options...
One use the Analyze-Documentor under Access-97 and save report to a table. Within this table, you can then search for the key table/field names.... I do not believe you can save to a table under Access-2K or 2002.
Another option, depending upon timing, it to review tools such as Speed Ferret. It searches and generates a detailed report of user defined search conditions. This tool also offers a search/replace which I have used and it has saved hours upon hours of time...
And, another "old-fashion" option is to write some code to loop through the Table Def of the database. Then while loopthing through each table, setup another loop, that will loop through the Fields collection.
Here is some old code I have... haven't used it in sometime though... since I have speed ferret...
Function MakeDataDict()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim lcSQL As String
Set dbs = CurrentDb
dbs.Execute "DELETE * FROM tblDataDictTables;", dbFailOnError
dbs.Execute "DELETE * FROM tblDataDictFields;", dbFailOnError
dbs.Execute "DELETE * FROM tblDataDictIndexes;", dbFailOnError
For Each tdf In dbs.TableDefs
If Mid(tdf.Name, 1, 4) <> "MSys" And Mid(tdf.Name, 1, 11) <> "tblDataDict" Then
lcSQL = "INSERT INTO tblDataDictTables "
lcSQL = lcSQL & "(ddTableName, ddTableComment, ddTableConnectString) "
lcSQL = lcSQL & "VALUES ('" & tdf.Name & "','" & "" & "','" & tdf.Connect & "');"
dbs.Execute lcSQL, dbFailOnError
For Each fld In tdf.Fields
lcSQL = "INSERT INTO tblDataDictFields "
lcSQL = lcSQL & "(ddTableName, ddFieldName, ddType, ddSize, ddDefaultValue, ddRequired) "
lcSQL = lcSQL & "VALUES ('" & tdf.Name & "','" & fld.Name & "','" & fld.Type & "','" & fld.Size & "','" & fld.DefaultValue & "'," & fld.Required & "
;"
dbs.Execute lcSQL, dbFailOnError
Next fld
For Each idx In tdf.Indexes
lcSQL = "INSERT INTO tblDataDictIndexes "
lcSQL = lcSQL & "(ddTableName, ddIndexName, ddFieldNames, ddPrimary, ddUnique) "
lcSQL = lcSQL & "VALUES ('" & tdf.Name & "','" & idx.Name & "','" & idx.Fields & "'," & idx.Primary & "," & idx.Unique & "
;"
dbs.Execute lcSQL, dbFailOnError
Next idx
End If
Next tdf
MsgBox ("Done Creating Data Dict Tables..."
'Later write an Update Feature that Checks for Changes to Tables, Fields and Indexes. Records Changes to Log File.
'Need to Run Update Periodically - Don't Want to Loose Comments Entered After Initial Load!
End Function
Function UpdateDataDict()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim lcSQL As String
Set dbs = CurrentDb
'1st Loop Through tblDataDictTables to see if Any New Tables or Deleted Tables?
'...
For Each tdf In dbs.TableDefs
If Mid(tdf.Name, 1, 4) <> "MSys" And Mid(tdf.Name, 1, 11) <> "tblDataDict" Then
'Check Existing Data in tblDataDictTables.
'Any Changes to Field Values? If so, Insert to tblDataDictChangeLog Old Values and New Values.
'Not sure how to handle when table deleted. May need to run a check on that 1st. Then add to
'If Statement Above. Update tblDataDictTables with Comment Table Removed?.
' lcSQL = "INSERT INTO tblDataDictTables "
' lcSQL = lcSQL & "(ddTableName, ddTableComment, ddTableConnectString) "
' lcSQL = lcSQL & "VALUES ('" & tdf.Name & "','" & "" & "','" & tdf.Connect & "');"
' dbs.Execute lcSQL, dbFailOnError
For Each fld In tdf.Fields
' lcSQL = "INSERT INTO tblDataDictFields "
' lcSQL = lcSQL & "(ddTableName, ddFieldName, ddType, ddSize, ddDefaultValue, ddRequired) "
' lcSQL = lcSQL & "VALUES ('" & tdf.Name & "','" & fld.Name & "','" & fld.Type & "','" & fld.Size & "','" & fld.DefaultValue & "'," & fld.Required & "
;"
' dbs.Execute lcSQL, dbFailOnError
Next fld
For Each idx In tdf.Indexes
' lcSQL = "INSERT INTO tblDataDictIndexes "
' lcSQL = lcSQL & "(ddTableName, ddIndexName, ddFieldNames, ddPrimary, ddUnique) "
' lcSQL = lcSQL & "VALUES ('" & tdf.Name & "','" & idx.Name & "','" & idx.Fields & "'," & idx.Primary & "," & idx.Unique & "
;"
' dbs.Execute lcSQL, dbFailOnError
Next idx
End If
Next tdf
MsgBox ("Done Creating Data Dict Tables..."
'Later write an Update Feature that Checks for Changes to Tables, Fields and Indexes. Records Changes to Log File.
'Need to Run Update Periodically - Don't Want to Loose Comments Entered After Initial Load!
End Function
Steve Medvid
"IT Consultant & Web Master"
Chester County, PA Residents
Please Show Your Support...
