Check for existence of a given fieldname in all tables

[BenjaminLim]

I need help to do the above for a particular database <b><font color=purple>(ACCESS 97) </font></b>.<br><br>All tables are linked tables (Oracle).<br><br>Pls advice.<br>

 

[Geekette]

Hi Benjamin,<br>&nbsp;&nbsp;&nbsp;&nbsp;There was a similar question back that I answered back on June 6th - check it out.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I've just written a FAQ &quot;Find and/or Replace&quot; which details the <b><i>Find & Replace</b></i> utility - go check it out

 

[BenjaminLim]

Thanks for the update.<br><br>As my company has very strict regulations on downloading of files I will like to be aware of what files will be loaded to my PC and what are the configuration changes if I were to setup the utility on my PC.<br><br>Pls advice.<br><br>Thanks.

 

[cmmrfrds]

If you need something quick, here is some code that was run in access 2000. Just give the first function a field name that you want to find. I just grabbed some functions I had so there is a mixture of DAO and ADODB definitions which you could change to fit your wants.<br><br>Function FindTables(whatevername As String)<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim tbl As Object, dbs As DAO.Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim nameToCheck As String, retMsg As String, theTable As String<br>&nbsp;&nbsp;&nbsp;&nbsp;nameToCheck = whatevername<br>&nbsp;&nbsp;&nbsp;&nbsp;Set dbs = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Search for all the access tables, that were created for the application<br>&nbsp;&nbsp;&nbsp;&nbsp;' Attributes = 0, are user defined tables.<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each tbl In dbs.TableDefs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Print name of Table<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If tbl.Attributes = 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;''''Debug.Print &quot;Table name = &quot;; tbl.Name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;theTable = tbl.Name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;retMsg = FindFieldNames(theTable, nameToCheck)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next tbl<br><br>End Function<br><br>Function FindFieldNames(theTable As String, nameToCheck) As String<br><br>Dim foundOnes As String, RSMT As New Recordset, cnn As ADODB.Connection<br>Set cnn = CurrentProject.Connection<br>Dim indx As Integer<br><br>''Open the table and the fields are accessible<br>RSMT.Open theTable, cnn, adOpenStatic, adLockReadOnly<br><br>For indx = 0 To (RSMT.Fields.Count - 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;If RSMT.Fields(indx).Name = nameToCheck Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;foundOnes = theTable & &quot; -- &quot; & nameToCheck<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;Table and Field = &quot;; foundOnes<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>Next '-- end for<br><br>FindFieldNames = foundOnes ' Returns the ones found if wanted<br>End Function<br><br>Jerry<br>