Command to display all tables

Question

Command to display all tables

sha war

SSC Enthusiast

Points: 136
More actions
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.

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

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

jfmccabe SSCrazy Points: 2547 More actions · Answer 1

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.

sha war SSC Enthusiast Points: 136 More actions · Answer 2

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

gmmastros SSC Veteran Points: 208 More actions · Answer 3

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

Set Connection = Nothing

%>

George Mastros
Orbit Software, Inc.

sha war SSC Enthusiast Points: 136 More actions · Answer 4

Thank you so much George. It works.

and thanks to jfmccabe too.

COLINB SSC Veteran Points: 219 More actions · Answer 5

In access create a query

SELECT MSysObjects.Name

FROM MSysObjects

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

Regards

Col

sha war SSC Enthusiast Points: 136 More actions · Answer 6

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

Thanks again.