?
Solved

All Tables Query

Posted on 2004-11-11
5
Medium Priority
?
215 Views
Last Modified: 2012-06-21
Hello,

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.

Ex:

Select table_name from all_tables where column_name = "Program";

Thank you
Michele
0
Comment
Question by:mmemon
3 Comments
 
LVL 14

Accepted Solution

by:
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

Rob
0
 
LVL 3

Expert Comment

by:leeskelton83
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
      Next
     
    Next
    strNames = Left(strNames, Len(strNames) - 1)
End Sub
0
 
LVL 2

Expert Comment

by:kmoloney
ID: 12556907
I think RD got it.
0

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