CODEWITHSUNDEEP
ms-access
Isaac
Isaac

Reputation: 3363

Microsoft Access metadata?

I would like to query the metadata of a number of Microsoft Access databases (some 2007 and some 2010) to get the columns and data types for a given table. I can do this in Oracle by querying ALL_TAB_COLUMNS. I can do this in SQL Server by querying sys.objects, sys.columns and sys.types. Something like this...

USE AdventureWorks2008R2

SELECT
    b.name,
    b.column_id,
    c.name,
    b.max_length,
    b.precision,
    b.scale
FROM
    sys.objects a
INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.user_type_id
WHERE
    a.name = 'Person'
ORDER BY
    b.column_id

The purpose of capturing this metadata is to use it in Biml to create an SSIS package factory. Is there a way to do systemically get the column data types for Microsoft Access tables?

Thanks

Upvotes: 1

Views: 973

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 49169

Unfortunately there is no SQL query that will return the columns and types.

The above answer of using VBA code is one way, and another is to use the database documenter. It will produce a report like this:

enter image description here

Upvotes: 2

geeFlo
geeFlo

Reputation: 375

Use the fields object when examine the table. I've only used fld.name where fld is a fields object.

I just gave someone else code on finding names looking through each column. You could probably use other properties in place of "name". (Oh and insert your Addnew, update for the recordset. I'm typing on tablet and being lazy

           for each fld in rst.fields
                   NewRst!variable = fld.name
           next fld

Upvotes: 0

Related Questions