Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Exclamation Unanswered: How to list tables exist in acces databse?

    hi there!

    how to list tables exist in acces databse? i searched alot for this question but couldnt find any solution.

    I 've liste all my databases in a list box using ASP. now, if i select any table from the list i wanna generate another list box having tables listed from the selected databse. is thre any solution?

    thnx in advance,

    bye

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    hiya.

    If I understand you correctly you want a list box (or like) that will display all the tables that your database contains - on a form.

    If you create a list / combo box, and look at the properties under Data tab. add the following to the Row Source.

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

    This will also hide all system tables.

    Christy

  3. #3
    Join Date
    Dec 2004
    Posts
    3
    Quote Originally Posted by christyxo
    If I understand you correctly you want a list box (or like) that will display all the tables that your database contains - on a form.

    If you create a list / combo box, and look at the properties under Data tab. add the following to the Row Source.

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

    This will also hide all system tables.

    Christy
    the query generates followng error when used in asp. any solution?

    Microsoft VBScript compilation error '800a0401'

    Expected end of statement

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

  4. #4
    Join Date
    Dec 2004
    Posts
    3
    Quote Originally Posted by christyxo
    If I understand you correctly you want a list box (or like) that will display all the tables that your database contains - on a form.

    If you create a list / combo box, and look at the properties under Data tab. add the following to the Row Source.

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

    This will also hide all system tables.

    Christy
    SELECT MSysObjects.Name FROM MSysObjects

    generates following error:

    Microsoft JET Database Engine error '80040e09'

    Record(s) cannot be read; no read permission on 'MSysObjects'.

    plz help me!

  5. #5
    Join Date
    Sep 2004
    Posts
    161
    You can read the tabledefs collection of your database
    Like this
    Code:
    Dim Db as Database
    Dim Tdf as TableDef
    Set DB =CurrentDb
    For each Tdf in Db.Tabledefs
    Debug.print Tdf.Name
    NExt Tdf
    This code give ALL the table (sysobject,....)
    In code we trust

  6. #6
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I actually copied this directly from a combo box on a form in my database so I am unsure of why this is happening. The exact SQL code as it looks in SQL view is:

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

    but its identical.

    I also have the following for Queries:

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

    Forms:

    SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=-32768) AND ((MSysObjects.Flags)<>3)) ORDER BY MSysObjects.Name;

    Reports:

    SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=-32764) AND ((MSysObjects.Flags)<>3)) ORDER BY MSysObjects.Name;

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    PHP Code:
    SELECT MSysObjects.Name
      FROM MSysObjects
        WHERE 
    (((MSysObjects.FlagsNot In (-2147483648,2,-2147483645)) AND     
            ((
    MSysObjects.TypeIn (1,4)))
         
    ORDER BY MSysObjects.Name
    Will show you all tables' names

Posting Permissions

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