Display Information About a Microsoft Access Database

The following block of code will display the properties of ALL tables in a Microsoft Access database. For the code to run successfully, the variable MyConnectionString needs to contain a valid connection string pointing to the Access database.
With this code, all tables, including system tables are displayed. If you want to limit the list, use myTable.Type to determine the type of the table and skip the system tables. User tables will return "TABLE", while system tables will return "ACCESS TABLE".

If you want to display information about just a single table, remove the For Each loop and uncomment the line that starts with Set myTable. Don't forget the pass the name of the right table to the Tables collection. 

<% 
  ' Create a Catalog Object and open the Table
  Dim myCatalog
  Dim myTable
  Dim myProperty
  Dim MyColumn
  Set myCatalog = Server.CreateObject("ADOX.Catalog") 
  myCatalog.ActiveConnection = MyConnectionString

  ' Set myTable = myCatalog.Tables("myTable") 

  For Each myTable in myCatalog.Tables %> 
    <h1>Table Name: <%=myTable.name%></h1>	
    <h2>Table Properties</h2>
		<table border="1"> 
    <tr> 
      <td><strong>Name</strong></td> 
      <td><strong>Value</strong></td> 
    </tr> 
    <% For Each myProperty in myTable.Properties %> 
    <tr> 
      <td><%=myProperty.Name%></td> 
      <td><%=myProperty.Value%></td> 
    </tr> 
    <% Next %> 
    <tr>
      <td>
        Type
      </td>
      <td>
        <%=myTable.Type%>
      </td>
    </tr>
    </table>
    <br />
    <br />
    <h2>Column Properties</h2> 
    <table border="1">
    <tr> 
      <td>Name</td> 
      <td>Type</td> 
      <td>Defined Size</td> 
      <td>Numeric Scale</td> 
      <td>Precision</td> 
      <td>Properties</td> 
    </tr> 
    <% For Each MyColumn in myTable.Columns %> 
    <tr> 
      <td><%=MyColumn.Name%></td> 
      <td><%=MyColumn.Type%></td> 
      <td><%=MyColumn.DefinedSize%></td> 
      <td><%=MyColumn.NumericScale%></td> 
      <td><%=MyColumn.Precision%></td> 
      <td> 
        <table border ="1">
        <tr> 
          <td>Property Name</td> 
          <td>Property Value</td> 
        </tr> 
        <% For each MyProperty in MyColumn.Properties %> 
        <tr> 
          <td><%=MyProperty.Name%></td> 
          <td><%=MyProperty.Value%></td> 
        </tr> 
        <% Next %> 
        </table> 
      </td>
    </tr> 
    <% Next %> 
    </table>
  <% Next %>
%>

Where to Next?

Wonder where to go next? You can post a comment on this article.


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.



 
QuickDocId252
Full URLhttp://imar.spaanjaars.com/252/display-information-about-a-microsoft-access-database
Short cuthttp://imar.spaanjaars.com/252/
Written byImar Spaanjaars
Date Posted02/19/2004 16:06
Date Last Updated02/27/2006 18:58
Date Last Reviewed 12/06/2006 15:31