All Tables Query

Posted on 2004-11-11
Medium Priority
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

RDWaibel earned 200 total points
ID: 12556099
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


Expert Comment

ID: 12556276
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

Expert Comment

ID: 12556907
I think RD got it.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

862 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