Solved

MS Access metadata?

Posted on 2004-08-30
5
4,741 Views
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!
0
Comment
Question by:tinh911
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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 & "')"
   Next
Next
End Function

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

Author Comment

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

Accepted Solution

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

Author Comment

by:tinh911
ID: 12071779
Thank you!
0
 

Expert Comment

by:AxisGroup
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
    Else
        Set qd = db.CreateQueryDef("")
        qd.SQL = "DELETE * FROM table_metadata"
        qd.Execute
    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
                .AddNew
                !table_nm = tdf.Name
                !column_nm = fld.Name
                !zero_length_ind = fld.AllowZeroLength
                !required_ind = fld.Required
                !length_no = fld.Size
                .Update
            End With
        Next
    Next
    
    rs.Close
    Set rs = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    db.Close
    Set db = Nothing
    
End Sub

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

717 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