Find field name in multiple tables 2

[JR2913]

I have a complex database (not mine, I hasten to add!) with literally dozens of tables. There is a problem with the use of data in one field (LecturerID), which appears in a number of the tables. However, without going through the tables individually on a manual basis, is there a quick way of using VBA to locate and list the tables where this field occurs.

I'm sure the code is quite simple.......

I need to locate the tables so that I can construct SQL statements to amend the data accordingly.

Any help gratefully received.

Many thanks in advance.

John R

 

[smedvid]

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) <> &quot;MSys&quot; And Mid(tdf.Name, 1, 11) <> &quot;tblDataDict&quot; Then
lcSQL = &quot;INSERT INTO tblDataDictTables &quot;
lcSQL = lcSQL & &quot;(ddTableName, ddTableComment, ddTableConnectString) &quot;
lcSQL = lcSQL & &quot;VALUES ('&quot; & tdf.Name & &quot;','&quot; & &quot;&quot; & &quot;','&quot; & tdf.Connect & &quot;');&quot;
dbs.Execute lcSQL, dbFailOnError

For Each fld In tdf.Fields
lcSQL = &quot;INSERT INTO tblDataDictFields &quot;
lcSQL = lcSQL & &quot;(ddTableName, ddFieldName, ddType, ddSize, ddDefaultValue, ddRequired) &quot;
lcSQL = lcSQL & &quot;VALUES ('&quot; & tdf.Name & &quot;','&quot; & fld.Name & &quot;','&quot; & fld.Type & &quot;','&quot; & fld.Size & &quot;','&quot; & fld.DefaultValue & &quot;',&quot; & fld.Required & &quot;&quot;
dbs.Execute lcSQL, dbFailOnError
Next fld

For Each idx In tdf.Indexes
lcSQL = &quot;INSERT INTO tblDataDictIndexes &quot;
lcSQL = lcSQL & &quot;(ddTableName, ddIndexName, ddFieldNames, ddPrimary, ddUnique) &quot;
lcSQL = lcSQL & &quot;VALUES ('&quot; & tdf.Name & &quot;','&quot; & idx.Name & &quot;','&quot; & idx.Fields & &quot;',&quot; & idx.Primary & &quot;,&quot; & idx.Unique & &quot;&quot;
dbs.Execute lcSQL, dbFailOnError
Next idx
End If
Next tdf
MsgBox (&quot;Done Creating Data Dict Tables...&quot
'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) <> &quot;MSys&quot; And Mid(tdf.Name, 1, 11) <> &quot;tblDataDict&quot; 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 = &quot;INSERT INTO tblDataDictTables &quot;
' lcSQL = lcSQL & &quot;(ddTableName, ddTableComment, ddTableConnectString) &quot;
' lcSQL = lcSQL & &quot;VALUES ('&quot; & tdf.Name & &quot;','&quot; & &quot;&quot; & &quot;','&quot; & tdf.Connect & &quot;');&quot;
' dbs.Execute lcSQL, dbFailOnError

For Each fld In tdf.Fields

' lcSQL = &quot;INSERT INTO tblDataDictFields &quot;
' lcSQL = lcSQL & &quot;(ddTableName, ddFieldName, ddType, ddSize, ddDefaultValue, ddRequired) &quot;
' lcSQL = lcSQL & &quot;VALUES ('&quot; & tdf.Name & &quot;','&quot; & fld.Name & &quot;','&quot; & fld.Type & &quot;','&quot; & fld.Size & &quot;','&quot; & fld.DefaultValue & &quot;',&quot; & fld.Required & &quot;&quot;
' dbs.Execute lcSQL, dbFailOnError
Next fld

For Each idx In tdf.Indexes

' lcSQL = &quot;INSERT INTO tblDataDictIndexes &quot;
' lcSQL = lcSQL & &quot;(ddTableName, ddIndexName, ddFieldNames, ddPrimary, ddUnique) &quot;
' lcSQL = lcSQL & &quot;VALUES ('&quot; & tdf.Name & &quot;','&quot; & idx.Name & &quot;','&quot; & idx.Fields & &quot;',&quot; & idx.Primary & &quot;,&quot; & idx.Unique & &quot;&quot;
' dbs.Execute lcSQL, dbFailOnError
Next idx

End If

Next tdf

MsgBox (&quot;Done Creating Data Dict Tables...&quot
'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
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[dhookom]

Smedvid,
What a great, complete response. One small correction regarding the documenter in recent versions of Access. A table is created when you run the documenter.
When I run the documenter on my PC (Windows ME), it creates a table in an MDT file burried in my profile. The connection string of the table is DATABASE=C:\WINDOWS\Application
Data\Microsoft\Access\ACWZUSR.MDT;TABLE=doc_tblObjects.

You can link to this table and create any type of report you want with the table. For instance, this SQL will provide the table and field names with field descriptions:

TRANSFORM First(doc_tblObjects_2.Extra1) AS FirstOfExtra1
SELECT doc_tblObjects.Name AS TableName, doc_tblObjects_1.Name AS FieldName,
doc_tblObjects_1.Extra2 AS FieldType, doc_tblObjects_1.Extra3 AS FieldSize
FROM (doc_tblObjects AS doc_tblObjects_2 INNER JOIN doc_tblObjects AS
doc_tblObjects_1 ON doc_tblObjects_2.ParentID = doc_tblObjects_1.ID) INNER
JOIN doc_tblObjects ON doc_tblObjects_1.ParentID = doc_tblObjects.ID
WHERE (((doc_tblObjects_1.TypeID)=11))
GROUP BY doc_tblObjects.Name, doc_tblObjects_1.Name,
doc_tblObjects_1.Extra2, doc_tblObjects_1.Extra3
PIVOT doc_tblObjects_2.Name In (&quot;Description:&quot;




Duane
MS Access MVP

 

[smedvid]

doc_tblObjects... I will make use of that...

Not sure why they removed the Save As Table option... It was very useful... that is MS for ya...

Thanks!

Steve

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[JR2913]

That's great Smedvid - thanks a lot. For speed I used the Analyzer (which I hadn't thought of applying) and exported the report as a text document. Found the field in 45 tables in a 651 page report!

I'll give you a star when I can get IE to provide the text in the pop-up window!

John R

 

[zhiraf]

This thread has helped me enormously! I have succeeded in finding the data I want inside acwzusr.mdt.

I have two questions, however.

1. How can I link to doc_tblObjects within acwzusr.mdt? My version of Access (2000) does not recognize .mdt as a valid extension for linking tables. (I can export from the acwzusr.mdt file to the file I'm documenting.)

2. It occurs to me that exporting to a separate file might be preferable, as the acmzusr.mdt information changes every time you run Database Documenter on ANY database file. Thoughts?

Thank you,

Zhiraf

 

[dhookom]

Even though you don't see the mdt files in the open file dialog, you can enter *.mdt in the file name to show you the file. The mdt is an mdb file with a different extension.

You are correct that the records are deleted each time you run the documenter.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[zhiraf]

Thanks, Duane. You've made ME a star among my local MS Access users!

Zhiraf