Senior Web Content Specialist Aquent US-NJ-Parsippany Post A Job | Post A Resume

Listing the Tables in an Access MDB Database Using ASP

---

Introduction:
Let's say that you are using an Access database on your web page, and are using Active Server Pages to interact with the database. Now let's say that your boss wants you to write an informational, administrative page, which lists all of the tables in the database via an ASP page, similar to the way the Analyzer works in Access. You may be sweating bullets by now, but you really shouldn't be, because listing tables in an Access database is not an impossible task. From the Active Server Pages mailing lis t, it is asked if it is possible to list the tables in an Access MDB file. The question is answered by Manfred Stanfield.

---

Listing the Tables in an Access MDB Database Using ASP:
The question is very short and simple indeed:

From ASP, is it possible to list the tables in an Access MDB file?

And the answer by Mr. Stanfield:

Here this will work:

<%
Function ConnectODBC( ConnectionObject )
      Set ConnectionObject = Server.CreateObject("ADODB.Connection")
      ConnectionObject.ConnectionTimeout = Session("JobTracking_ConnectionTimeout")
      ConnectionObject.CommandTimeout = Session("JobTracking_CommandTimeout")
      ConnectionObject.CommandTimeout = 3000
      ConnectionObject.Open("JobTracking")
End Function



Const adSchemaTables = 20

ConnectODBC oConn
Set rstSchema = oConn.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
      Response.Write "Table name: " & rstSchema("TABLE_NAME") & vbCr & "Table type: " & rstSchema("TABLE_TYPE") & vbCr & "<BR>"
      rstSchema.MoveNext
Loop
%>


---

Conclusion:
As you can see, it is quite possible to list the tables in an Access database. You just need to access the database's schema object, which can be done quite simply through ASP. In fact, Access allows you to examine more than just table names- you can vi ew table descriptions, column datatypes, column rules, min. and max values, etc. While this table-level data is available in SQL, it is much more of a hassle to obtain it than it is through Access. If only Access were more secure and efficient.... alas. Happy Programming!

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article