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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 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

17 Experts available now in Live!

Get 1:1 Help Now