All Tables Query

Posted on 2004-11-11
Last Modified: 2012-06-21

Is there a way in Access to do a search on all tables? I want to write a query that gives me all the table names that have the column equal  Program. In oracle I would be able to do this.


Select table_name from all_tables where column_name = "Program";

Thank you
Question by:mmemon
    LVL 14

    Accepted Solution

    Hello Michele.
    this function (run in an immediate widnow) will display all the tables where a column name exists in your database.

    to use it, just type :
    ? FindColumnsInTablesRS("Program")
    : in the Immediate window and press enter.

    Public Function FindColumnsInTablesRS(inColumnName As String)

        Dim T As DAO.TableDef
        Dim F As DAO.Field
        Dim X As Integer
        For Each T In CurrentDb.TableDefs
            For X = 0 To T.Fields.Count - 1
                Set F = T.Fields(X)
                If UCase(inColumnName) = ucase(F.Name) Then
                    Debug.Print T.Name
                End If
            Next X
        Next T
    End Function

    LVL 3

    Expert Comment

    Or this if you want a string with all fields:
    Sub getfields()

    Dim db As Database
    Dimtbl As TableDef

      Set db = CurrentDb
       For Each tbl In db.TableDefs
         For i = 0 To tbl.Fields.Count - 1
             If tbl.Fields(i).Name = "Program" Then
             strNames = strNames & tbl.Name & "." & tbl.Fields(i).Name & ","
             End If
        strNames = Left(strNames, Len(strNames) - 1)
    End Sub
    LVL 2

    Expert Comment

    I think RD got it.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now