What is a database Schema?
A database schema is simply a repository that has information about the structure and content of the database. It also contains information about how the data is stored internally, as well as how it is stored physically on the storage device.
The picture shows an Access 97 database with three tables. Access also maintains a database schema, like any other database. Our goal is to list these tables in our ASP code.
To connect to the database, we need to create a connection string or a DSN. Let's create a System DSN named "Library" that points to our library database.
The following ASP code depicts how we will extract the table names from the above database. While the above database is not a part of this tutorial, you can use almost any Access database to try the following code.
<!-- #include file="adovbs.inc"-->
' Create a connection object
Set Conn = Server.CreateObject ("ADODB.Connection")
' Open the connection to the database. I use a system DSN here, but
' you can use whatever method you wish
' Open the database schema to query the list of tables. Extract the
' list in a Recordset object
Set Rs = Conn.OpenSchema (adSchemaTables)
' Loop through the list and print the table names
Do While Not Rs.EOF
Response.Write "<BR>" & Rs ("TABLE_NAME")
' Close and destroy the recordset and connection objects
Set Rs = Nothing
Set Conn = Nothing
When you run the above code, you will see a result something like:
So, what are the list items highlighted in red? In fact, they are system tables, meaning tables maintained by Access for its own use. In fact, if you print out the Rs ("TABLE_TYPE") field, you can see that these tables are marked "SYSTEM TABLE" These tables list all the available objects within the database - tables, queries, modules etc. So, in a way they are tables that list tables..
asp access list tables access97