MS Access metadata?

Posted on 2004-08-30
Last Modified: 2011-04-14
My problem is:
How can I get all table name, column names in a Access file by using queries?
Thankx alot!
Question by:tinh911
  • 2
  • 2
LVL 41

Expert Comment

ID: 11931628
All table names:

SELECT [Name] FROM MSysObjects WHERE [Type]=1

As for field names, you can't retrieve this in a query. You can construct a new table to hold this data though. Run this function to load the table with all the table/field names:

Function GetTableFields()
Dim fld As Field
Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
   For Each fld In tdf.Fields
      CurrentDb.Execute "INSERT INTO MyTable ([TableName],[FieldName]) VALUES ('" & tdf.Name & "','" & fld.Name & "')"
End Function

This assumes you have a table called MyTable containing the fields TableName and FieldName (both text).

Author Comment

ID: 11939300
It means that I can't retrieve the field names of a table in a query!
LVL 41

Accepted Solution

shanesuebsahakarn earned 250 total points
ID: 11941572
If you use the piece of code that I have posted above, you'll have a table containing table and fieldnames, which you can then run a query on to retrieve just the fieldnames of the table that you are interested in, i.e.:

SELECT FieldName FROM MyTable WHERE TableName="SomeTable"

after you've run the code.

Author Comment

ID: 12071779
Thank you!

Expert Comment

ID: 25018806
Here's a simple example that will dump metadata to a table.  Should provide a good starting point.
Public Sub PopulateTableMetadata()

    ' This is a simple procedure that will create a table and

    ' populate it with metadata from the database

    ' It should get you started - modify to suit your specific needs

    Dim db As DAO.Database

    Dim tdf As DAO.TableDef

    Dim fld As DAO.Field

    Dim qd As DAO.QueryDef

    Dim rs As DAO.Recordset


    Set db = CurrentDb()


    ' First create the metadata table, if it doesn't exist

    On Error Resume Next ' allows you to keep going if the table doesn't exist - admitted

    Set tdf = db.TableDefs("table_metadata")

    If tdf Is Nothing Then

        Set tdf = db.CreateTableDef("table_metadata")

        With tdf

            .Fields.Append .CreateField("table_nm", dbText, 255)

            .Fields.Append .CreateField("column_nm", dbText, 255)

            .Fields.Append .CreateField("zero_length_ind", dbBoolean)

            .Fields.Append .CreateField("required_ind", dbBoolean)

            .Fields.Append .CreateField("length_no", dbInteger)

        End With

        db.TableDefs.Append tdf


        Set qd = db.CreateQueryDef("")

        qd.SQL = "DELETE * FROM table_metadata"


    End If


    ' Next, populate the table

    Set rs = tdf.OpenRecordset(dbOpenDynaset)

    For Each tdf In db.TableDefs

        For Each fld In tdf.Fields

            With rs


                !table_nm = tdf.Name

                !column_nm = fld.Name

                !zero_length_ind = fld.AllowZeroLength

                !required_ind = fld.Required

                !length_no = fld.Size


            End With





    Set rs = Nothing

    Set fld = Nothing

    Set tdf = Nothing


    Set db = Nothing


End Sub

Open in new window


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

947 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

22 Experts available now in Live!

Get 1:1 Help Now