Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question Unanswered: Tables displayed within List box

    Hey guys,

    I have seen within the forums the ability to display all the reports in your database in a list box without typing them in. i.e. Database retieves the tables automatically and lists them.

    Is it possible to display all the tables that are in the database? and if so does anybody know how?

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    It's possible to display all the objects of your database. you must just use the good collection
    Tabledefs for table
    Querydefs for query
    Documents for form, report
    etc....

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It depends on what version of Access you are using. If you are still using 97 like me then somehting like this will work for queries:

    Code:
    SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3)) ORDER BY MSysObjects.Name;

    or

    Code:
    SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=-32764) AND ((MSysObjects.Flags)=0)) ORDER BY MSysObjects.Name;

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Came across this.

    Came across this in a book. you may want to try it.

    on the load event for your form.

    dim strTables as string
    dim tdf as AccessObject

    for each tdf in currentdata.alltables
    strtables = tdf.name & ";"
    next tdf

    your_List_Box.rowsource = strtables



    I tried it and it works pretty well. Automatically excludes all Sys Tables. Which is nice.

    MW

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Excellent!

    Thanks guys..

    I used DCKunkle's 1st method which I've slightly adapted to exclude MSys Tables.

    SELECT MSysObjects.Name FROM MSysObjects WHERE ((Left$([Name],4)<>"MSys") AND ((MSysObjects.Type)=1) AND ((MSysObjects.Flags)<>3)) ORDER BY MSysObjects.Name;

    For anyone's reference MSysObjects.types are

    = 1 for Tables
    = 5 for Queries
    = 8 for Relationships
    = -32768 for Forms
    = -32764 for Reports
    = -32761 for Modules

    Don't know what the rest are.

Posting Permissions

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