Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Object Lists (2003)

    Hello again, I have inherited a large database with hundreds of tables - is there any way I can print a list of these? I know I can see a list in the db properties or take screen shots, but I was hoping there might just be a way to list the tables and queries in a printable format????

    TVM

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object Lists (2003)

    One way to obtain a list is as follows:
    - Activate the Queries section of the Database window.
    - Click "New Query in Design View".
    - Close the "Show Table" dialog without adding any tables.
    - Select View | SQL.
    - Replace the text you see with

    SELECT Name FROM MSysObjects WHERE Type=1 AND Not Left([Name], 4) = "MSYS"

    - Select View | Datasheet.
    - You'll see a list of tables in the database. You can print it, or export it to (for example Excel), or copy and paste into Word, etc.

    To do the same for queries, use

    SELECT Name FROM MSysObjects WHERE Type=5 AND Not Left([Name], 4) = "~sq_"

  3. #3
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object Lists (2003)

    Thanks I'll try this - like a new language - you don't see this sort of thing in any of the Dummy books! :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object Lists (2003)

    It *is* a language, and it's called SQL (pronounced like "sequel", it's an acronym for Structured Query Language). All queries in Access use SQL under the hood.
    The SQL posted is a bit of a trick, since it uses a system table (MSysObjects) that is normally invisible to users.

  5. #5
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object Lists (2003)

    Thanks Hans, this is helpful ta - it works fine, except for the fact that it doesn't list linked tables, of which there are many - is there a way I can add in that I want the list to show these too?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object Lists (2003)

    Linked tables have type = 6, so use

    SELECT Name FROM MSysObjects WHERE (Type=1 Or Type=6) AND Not Left([Name], 4) = "MSYS"

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object Lists (2003)

    When I select type 5, the list has all my queires in addition to several queries that start with "~_sq....." They do don't apper in my normal list of queries. Do you know what they are?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object Lists (2003)

    Those are hidden queries created by the system. For example, if you set the Control Source of a form or the Row Source of a combo box to an SQL string, Access will create a hidden query with that SQL string.

    You could skip them by changing the query from <post:=662,388>post 662,388</post:> slightly.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object Lists (2003)

    Ohh my mistake I looked right over that.
    thanks,

Posting Permissions

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