Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62

    Unanswered: List of Reports in current db

    I am trying to create a list of the reports that exist within a database.

    I have not had much success with the Documenter.

    Can someone help me create a list of reports that are within the database. I do not need any information about the reports. I simply need a list of the reports.

    Any help would be greatly appreciated.

    Jack

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: List of Reports in current db

    Originally posted by Jaash
    I am trying to create a list of the reports that exist within a database.

    I have not had much success with the Documenter.

    Can someone help me create a list of reports that are within the database. I do not need any information about the reports. I simply need a list of the reports.

    Any help would be greatly appreciated.

    Jack
    Try this query...

    SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
    (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

    HTH

  3. #3
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62
    Many thanks.

    That works perfectly.

    Thank you,
    Jack

  4. #4
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62
    On this same topic ...

    Is there a way to list the tables and the fields that exist within the table?

    Thank you,

    Jack

  5. #5
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Jaash
    On this same topic ...

    Is there a way to list the tables and the fields that exist within the table?

    Thank you,

    Jack
    This link will give you examples of the queries for a list of tables, reports, forms, queries, etc...
    http://www.mvps.org/access/queries/qry0002.htm
    (Note: Be very careful when you use the system tables for anything! ... Make a change to one and it could cause MAJOR problems... )

    As for the list of fields, you would have to iterate through the Fields Collection for each table in the TableDefs Collection... I'm sure there's sample code in Access' help... If you can't find it, let me know...

  6. #6
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62
    Thank you very much for your help.

    The code to cycle through the records eludes me as I am not very experienced with code writing.

    If you could post an example that would dump the output (table name, fieldname) to a file I would very much appreciate it.

    Many thanks for your time,

    Jack

  7. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Here's a quick example...

    Code:
    Sub TablesAndFields()
    
        Dim db As DAO.Database
        Dim rstNew As DAO.Recordset
        Dim tdfNew As TableDef
        Dim tdfLoop As TableDef
        Dim fldLoop As Field
    
        Set db = CurrentDb
        
        'delete the table first
        db.TableDefs.Delete "tblTableFields"
        
        'create new table
        Set tdfNew = db.CreateTableDef("tblTableFields")
            tdfNew.Fields.Append tdfNew.CreateField("fldTableName", dbText)
            tdfNew.Fields.Append tdfNew.CreateField("fldFieldName", dbText)
        db.TableDefs.Append tdfNew
        
        Set rstNew = db.OpenRecordset("tblTableFields")
    
        With db
            ' iterate through TableDefs collection
            For Each tdfLoop In .TableDefs
                If tdfLoop.Name <> "tblTableFields" And _
                    Left(tdfLoop.Name, 4) <> "MSys" Then
                    For Each fldLoop In tdfLoop.Fields
                        'add record to tblTableFields with table and field names
                        rstNew.AddNew
                        rstNew![fldTableName] = tdfLoop.Name
                        rstNew![fldFieldName] = fldLoop.Name
                        rstNew.Update
                    Next fldLoop
                End If
            Next tdfLoop
        End With
        
        Set rstNew = Nothing
        Set db = Nothing
    
    End Sub
    HTH

  8. #8
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62
    Thank you very much for this code!

    I am having one problem on the line that reads...

    For Each fldLoop In tdfLoop.Fields

    It gives me a

    Runtime Error '13':

    Type Mismatch

    Message. Why is that happening? I commented out this set of code and the writes to the new table of the TableNames works perfectly.

    The only problem is with this fldLoop.

    Any additional help or ideas why this is happening would be greatly appreciated.

    Jack

  9. #9
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62
    Wow, OK I got it!

    I needed to change the field declaration as DAO.Field, and DAO.TableDef.

    That was is good to know.

    Thank you for all of your assistance on this.

    Jack

  10. #10
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: List of Reports in current db

    Originally posted by Jaash
    I am trying to create a list of the reports that exist within a database.

    I have not had much success with the Documenter.

    Can someone help me create a list of reports that are within the database. I do not need any information about the reports. I simply need a list of the reports.

    Any help would be greatly appreciated.

    Jack
    Or Try that:

    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=-32764))
    ORDER BY MSysObjects.Name;

Posting Permissions

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