Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2010
    Posts
    5

    Red face Unanswered: Get a list of tables in a database

    Hi!
    I would like to get the names of all the tables in a database file, in a html table. I'm sorry but i am very new at this, so just tell me if i missed something.
    The server is storing .mdb files (access 2000) wich i would like to print out the table names from. This is to make a webbrowser based editor able to edit the databases using sql.
    There won't be any problem if the names are in one line seperated by commas or such, as i won't have any problem extracting them later on, although a html table would be the best.
    Tnx!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This may need to go to the Access forum.

  3. #3
    Join Date
    Nov 2010
    Posts
    84
    If I understand you correctly, you must do the following query

    SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Name) Not Like "MSYS*") AND ((MSysObjects.Type)=6)) OR (((MSysObjects.Name) Not Like "MSYS*") AND ((MSysObjects.Type)=1))
    ORDER BY MSysObjects.Name;

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are several methods:
    Code:
    Sub EnumTablesDAO()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
    
            ' Don't display system tables
            '
            If tdf.Name Like "MSys*" = False Then Debug.Print tdf.Name
        Next
        Set dbs = Nothing
        
    End Sub
    
    Sub EnumAllTables()
    
        Dim accObj As AccessObject
        
        For Each accObj In Application.CurrentData.AllTables
    
            ' Don't display system tables
            '
            If accObj.Name Like "MSys*" = False Then Debug.Print accObj.Name
        Next
    
    End Sub
    It's better not to access the system tables (MSysObjects etc.) if you can avoid it. Moreover the internal structure of those tables can change in a future version of Access.
    Have a nice day!

  5. #5
    Join Date
    Nov 2010
    Posts
    5
    Code:
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
    this gives me an error, expected end of command line (sry for my bad translations)

    Code:
    Dim dbs As DAO.Database
    --------^
    Isn't that correct VBS syntax? checked msdm, which says it is... Any idea?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do you have a reference to the Microsoft DAO 3.x (usually 3.6) library in your project? (see attachment).
    Attached Thumbnails Attached Thumbnails Acc_References.jpg  
    Have a nice day!

  7. #7
    Join Date
    Nov 2010
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    Do you have a reference to the Microsoft DAO 3.x (usually 3.6) library in your project? (see attachment).
    i don't think so... I'm using dreamweaver cs5, should i change program or is it a way of doing that?

  8. #8
    Join Date
    Nov 2010
    Posts
    84
    However in my opinion you prefer a variant of SQL query I have attached a water trough in a previous post, and now I give you and example attached. You have a query named Q_Tables.
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2010
    Posts
    5
    Quote Originally Posted by eremija View Post
    However in my opinion you prefer a variant of SQL query I have attached a water trough in a previous post, and now I give you and example attached. You have a query named Q_Tables.
    sorry, didn't understand that. Just a database with a few tables? I can't use server variables, because i'm not allowed to use them. Nothing i can change, unfortunately... Is there a way of searching for tables inside databases with some kind of seach-function?
    Last edited by fille3002; 11-18-10 at 09:57.

  10. #10
    Join Date
    Nov 2010
    Posts
    5
    Can't anyone here help me further?

Posting Permissions

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