Post reply

Command to display all tables

  • July 22, 2004 at 2:19 pm

    #65212

    Hi, can someone please tell me what the SQL command is to display all the table names in a MS Access database.

     

     

  • July 23, 2004 at 2:19 am

    #515810

    An Access database can contain several types of table - local, linked Access, ODBC linked etc. The list of tables is held in a system table which is not normally visible and is tricky to query. To make it worse, the name of a linked table in Access doesn't have to match the name of the actual table e.g. a table in SQL server called tCustomer may end up as dbo_tCustomer in Access (if you accept the default name) or Customers if you mess about with it.

    There are two distinct methods to get a list of table names based on DAO and ADOX. DAO is "simpler":

    Sub Table Names
    Dim tdf as ADO.TableDef
    for each tdf in CurrentDb.TableDefs
    print tdf.name
    print tdf.SourceTableName
    next
    end sub

     

    ADOX is similar but more complex since the source table name is held in a provider specific property - see the documentation.

     

     

  • July 23, 2004 at 5:38 am

    #515833

    Thanks for the quick response jfmcabe .

    What I want to do is, using ASP.net to display the list of normal tables I created in a access database on a web page (excluding the link tables and system tables) . 

    I'm quite new to this, so I'm not sure how to use the above information to do this. I would appreciate it, if you can tell me how to do this.

    Thank you

  • July 23, 2004 at 6:32 am

    #515845

    This is ASP 3 code, but it should be easy to adapt it to ASP.net

    <%

    Set Connection = Server.CreateObject("ADODB.Connection")

    Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AnyAccessDatabase.mdb"

    Call Connection.Open

    Set O = Server.CreateObject("ADOX.Catalog")

    Set O.ActiveConnection = Connection

    Response.Write("<table Border=1>")

    Response.Write("<TR><TH>Table Name</TH></TR>")

    For i = 0 To O.Tables.Count - 1

        If O.Tables.Item(i).Type = "TABLE" Then

            Response.Write("<TR><TD>" & O.Tables.Item(i).Name & "</TD></TR>")

        End If

    Next

    Set O = Nothing

    Set Connection = Nothing

    %>


    George Mastros
    Orbit Software, Inc.

  • July 23, 2004 at 3:47 pm

    #515970

    Thank you so much George. It works.

    and thanks to jfmccabe too.

     

     

  • July 27, 2004 at 8:59 pm

    #516446

    In access create a query

    SELECT MSysObjects.Name

    FROM MSysObjects

    WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=4));

     

    Regards

    Col

  • July 28, 2004 at 3:51 am

    #516525

    Thanks Col. it's a very easy way of doing it.

    Thanks again.  

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply