How to List Field's Name in Table in Access Using SQL

MS Access: How can I return all table and field names with in that table that have the field format 'hyperlink' via SQL or VBA?

You can loop through every tableDef/Table in the database, but it gets a little hairy since hyperlinks are an attribute on a memo field type. The Attributes property of Access objects are a bitmask, so when checking to see if a particular attribute is set, you have to check if that attributes bit is set in the bitmask.

Sub ListTablesWithHyperlinks()
    Dim tdf As TableDef
    Dim fld As Field
    Dim tdfPrint As Boolean

    'Loop through all tables
    For Each tdf In CurrentDb.TableDefs
        'Set flag to false
        tdfPrint = False

        'Loop through all fields
        For Each fld In tdf.Fields
            'Test if column has an attribute of `dbHyperlinkField`
            If (fld.Attributes And dbHyperlinkField) = dbHyperlinkField Then
                'Set the flag and exit the loop
                tdfPrint = True: Exit For
            End If
        Next fld

        'If the flag was set to true, then iterate fields again and print out
        If tdfPrint Then
            For Each fld In tdf.Fields
                'Print out tablename, column ordinal, and column name
                Debug.Print tdf.Name, fld.OrdinalPosition, fld.Name
            Next fld
        End If
    Next tdf
End Sub

Get all field names in Microsoft Access Table using SQL

I was making using of the ODBC extension in PHP and it has a function to do this!

In case anyone needs this in the future its, odbc_tables!

How can I get column names from a table in SQL Server?

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

Field Values to Column Names in MS Access SQL

Add a 'Value' field to your table so it will read like this:

Name  Attributes  Value
----  ----------  -----
John  Height      172
John  Weight      87
John  Age         25
Mary  Shoe Size   9.5
Mary  Hair Color  Brown
Mary  Eye Color   Blue

You can use 'TRANSFORM' and 'PIVOT' in Access

TRANSFORM FIRST(Value)
SELECT Name FROM MyTable
GROUP BY Name
PIVOT Attributes

Get Column name from a query table

To get the column names from a QueryDef, you can use the Fields collection:

Dim firstColumnName As String
firstColumnName = MrgTbls.Fields(0).Name

Alternatively you can use pure SQL and the MSysObjects and MSysQueries system tables. See here for details about the structure of this table.

How can I get table names from an MS Access Database?

To build on Ilya's answer try the following query:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name 

(this one works without modification with an MDB)

ACCDB users may need to do something like this

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6))
        AND ((MSysObjects.Flags)=0))
order by MSysObjects.Name 

As there is an extra table is included that appears to be a system table of some sort.

Passing table name and field name as strings to function

To use variables in these cases, you use parentheses:

If IsNull(myTable(fieldName)) Then

But note that a more efficient way is to use a query:

x = DCount("*", tableName, "[" & fieldName & "] IS NULL")

---

Related Topics