loop tables for column name

Hello,

I need a vba code in MS access where I say that there is a column called “XYZ” and the code loops through all access tables in the access DB and tells me which table in the DB has that column name “XYZ” in it. Can someone help me with the vba …

Thank you
RayneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
My preferred solution is to use Rick Fisher's Find & Replace software.  This will find all references to that string anywhere in your application.  You can download a 30 day trial, and the price ($37) will be the best $37 dollars you have ever spent if you are going to be doing any Access database development.
0
Dale FyeCommented:
If you really only want to do this by yourself, I would write a query to select all of the tables from the mSysObjects table, something like:

SELECT [Name], Database, Type FROM mSysObjects
WHERE ([Type] = 1) and (NOT [NAME] Like "mSys*")

This is even better if your tables all have the same prefix ("tbl") or suffix.

Then you create a recordset from this query.  With this, you then loop through the databases TableDefs collection, using the [Name] field from this query.  Looks something like (untested):

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim tdf as DAO.TableDef
Dim fld as DAO.Field

set db = Currentdb
strSQL = "SELECT [Name], Database, Type FROM mSysObjects WHERE ([Type] = 1) and (NOT [NAME] Like 'mSys*')"
SET rs = db.OpenRecordset(strsql)
While not rs.eof
    set tdf = db.Tabledefs(rs!Name)
    for each fld in tdf.Fields
        if fld.Name = "XYZ" then 
            debug.print tdf.Name
            Exit For
        End IF
    Next
    rs.movenext
Wend

rs.close
set rs = nothing

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
Try Dale's code first,

....Something like this should work also

The field name is specified on a form.
And a button on the form runs this code:

Dim tdf As TableDef
Dim fld As Field
Dim strFieldName As String
strFieldName = Me.txtFieldName
For Each tdf In CurrentDb.TableDefs
    For Each fld In tdf.Fields
        If fld.Name = strFieldName Then
            MsgBox "the table: " & tdf.Name & ", contains the field: " & fld.Name
        End If
   Next fld
Next tdf

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Something like this will tel you if no fields match your name...

Dim tdf As TableDef
Dim fld As Field
Dim lngCounter As Long
Dim strFieldName As String
strFieldName = Me.txtFieldName
For Each tdf In CurrentDb.TableDefs
    For Each fld In tdf.Fields
        If fld.Name = strFieldName Then
            MsgBox "The table: " & tdf.Name & ", contains the field: " & fld.Name
            lngCounter = lngCounter + 1
        End If
   Next fld
Next tdf

If lngCounter = 0 Then
    MsgBox "No table contains the field: " & strFieldName
End If

Open in new window


Note that this is very simple, ...Off-The-Cuff code, ...but it should work...

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Actually, I like Jeff's better, because it doesn't risk getting some of the system objects which are not actually tables.

You might throw in an If statement to ignore the tables (mSys...) which will show up in the TableDefs collection, but querying msysObjects can result in other tables that you don't know exist (especially if you are using the lookup feature in your table definitions).
0
RayneAuthor Commented:
Thank you for all your generous help :) :)
0
Jeffrey CoachmanMIS LiasonCommented:
To all, FWIW, ....

The reason that I did not omit the system tables is that perhaps you wanted to search them as well.

In my code it displays the table name, so you know where the field exists.

Like Dale posted, there are many ways to do this, and getting the name is easy.

The fun starts with what you might want to do with the names...
;-)

later, in a new question, you can ask how to store the Found fields in a table, or present them in a list/combobox, or use the name as a variable...

Fun stuff.
;-)
Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.