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 FyeOwner, Developing Solutions LLCCommented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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

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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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 FyeOwner, Developing Solutions LLCCommented:
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).
RayneAuthor Commented:
Thank you for all your generous help :) :)
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
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.