Find a field in hundreds of tables

[Rehmann]

I have an Access front-end that has linked tables to an Oracle database;
there are hundreds of linked tables in my front-end.

Is there a way to write some code that goes through every table and
finds all the tables that have fields named "EMPLID" and puts the names
of those tables into another table?

Help is appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Peter Russell]

The code below should give you a start. I don't see that use of Oracle is
specifically relevant here.

It requires the DAO object library to be included in your application.

You might want to add some error handling and/or restrict the table names
to those not starting with MSys (which are Access internal tables).

Regards

Peter Russell
Sub FindTables()
Dim fName
Dim db As Database
Set db = CurrentDb

Dim tbl As DAO.TableDef
Dim fld As DAO.Field

fName = "EMPLID"

For Each tbl In db.TableDefs

For Each fld In tbl.Fields
If fld.Name = fName Then
InsertRec tbl.Name
Exit For
Else
End If
Next fld

Next tbl

Set db = Nothing
End Sub

Sub InsertRec(vtblname)
' set your own table name (=MyTable) and fieldname (=tablename) where
' results are stored
Dim strSQL
strSQL = "INSERT INTO MyTable (tablename) VALUES ('"
strSQL = strSQL & vtblname & "')"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


Rehmann previously wrote:

I have an Access front-end that has linked tables to an Oracle database;
there are hundreds of linked tables in my front-end.

Is there a way to write some code that goes through every table and
finds all the tables that have fields named "EMPLID" and puts the names
of those tables into another table?

Help is appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!