Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Melbourne, Victoria, Australia
    Thanked 0 Times in 0 Posts

    Document tables (Access 2002 SP-2)

    I have a number of tables for which I would like to produce/create/append to another table
    properties like column name, type, size, index name, index key.
    I know that the documenter produces a report containing that information, but I can't
    export it to a table within another mdb, and if I export to an Excel file, there seems a fair amount of
    work to be done to massage it into the columnar structure which is required.
    The tables are linked tables from SQL Server.
    Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Document tables (Access 2002 SP-2)

    The results of the Documenter are stored in a table named doc_tblObjects in a special database named ACWZUSRT.MDT; on my system it is in Cocuments and Settings<username>Application DataMicrosoftAccess.

    Note: the Application Data folder is hidden, so you will only see it in Windows Explorer if you show hidden files and folders (setting in the View tab of Tools | Folder Options...)

    The following query will display field properties and values in a usable format:

    SELECT doc_tblObjects.Name AS TableName, doc_tblObjects_1.Name AS FieldName, doc_tblObjects_1.Extra2 AS FieldType, doc_tblObjects_1.Extra3 AS FieldSize
    FROM doc_tblObjects AS doc_tblObjects_1 INNER JOIN doc_tblObjects ON doc_tblObjects_1.ParentID = doc_tblObjects.ID
    WHERE doc_tblObjects_1.TypeID=11;

    TypeID = 11 indicates fields; look at the table for other info; other TypeID's correspond to indexes, relationships etc.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Niagara Falls, New York, USA
    Thanked 0 Times in 0 Posts

    Re: Document tables (Access 2002 SP-2)

    Hi Zinger

    In addition to Hans solution I put the attached db together with the help of John Viescas and Candice Tripp

    It will dump all local and remote attached tables and queries to a table using John Viescas modified code.

    Then you can use the Candice Tripp modified form to drill down on the storage table, allow you to select querys or tables, then select local or remote, then the individual query or table in question.

    The db contains some northwind tables and queries for demo purposes.

    HTH John
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts