View Single Post
  #4 (permalink)  
Old October 10th, 2003, 08:54 PM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

You got me curious about the contents of MsysAccessObjects since Access doesn't let you see what's really in it. ADO.NET, however, has a GetOleDbSchemaTable method of the Connection object that does. I found a C# class that implements it, and this is what the output looked like when I connected the console app to a blank Access database (i.e., contains only system tables). The class is available at:

http://my.execpc.com/~gopalan/dotnet..._metadata.html

Just a bunch of database metadata about table and column properties:

Listing Table Metadata Information ...
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED

Listing Tables ...
MSysAccessObjects
MSysACEs
MSysObjects
MSysQueries
MSysRelationships

Listing Column Metadata Information ...
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION

Listing Columns (TableName : ColumnName format)...
MSysAccessObjects : Data
MSysAccessObjects : ID
Press any key to continue

So it turns out, as one would expect, that MSysAccessObjects is the Access counterpart to the SQL Server 'Master' database. Difference is that in SQL Server, you can use these column values to query for schema information as in:

SELECT
      T.TABLE_NAME AS [A_Table],
      C.COLUMN_NAME AS [B_Column],
      C.IS_NULLABLE AS [C_NULLS],
      C.DATA_TYPE AS [D_Type],
      C.COLUMN_DEFAULT AS [E_DEFAULT]
FROM
      INFORMATION_SCHEMA.Tables T JOIN INFORMATION_SCHEMA.Columns C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE
      T.TABLE_NAME = 'Customers'
ORDER BY
      T.TABLE_NAME, C.ORDINAL_POSITION

If I run this query against the SQL Server version of Northwind, I get the following output (sorry if this doesn't format right):

A_Table B_Column C_NULLS D_Type E_DEFAULT
Cutomers CustomerID No nchar NULL
Cutomers CompanyName No nvarchar NULL
Cutomers ContactName Yes nvarchar NULL
Cutomers ContactTitle Yes nvarchar NULL
Cutomers Address Yes nvarchar NULL
Cutomers City Yes nvarchar NULL
Cutomers Region Yes nvarchar NULL
Cutomers PostalCode Yes nvarchar NULL
Cutomers Country Yes nvarchar NULL
Cutomers Phone Yes nvarchar NULL
Cutomers Fax Yes nvarchar NULL

I don't think there is anyway to do this in Access.

Regards,

Bob