• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Retrieve list of tables within MS Access Database

Hi all

Is there a way using ASP and SQL to retrieve a list of all the tables contained within an MS Access Database?

Also is there another SQL query that will return all of the table fields, without returning any records?

Thanks in advance

Richard
0
shaggy_the_sheep
Asked:
shaggy_the_sheep
  • 5
  • 4
1 Solution
 
Anthony PerkinsCommented:
There are a couple of ways:
A.  Query the system tables: MSysObjects
B.  (Better approach) Is to use the Connection's OpenSchema method.
0
 
shaggy_the_sheepAuthor Commented:
Using the OpenSchema method does a schema have to have been specifically defined (or is it defined simply through creating tables, etc.)

A simple example would be great please, as i havent a clue where to start :-)

Cheers

Richard
0
 
Anthony PerkinsCommented:
Consider looking at the one in the MDAC Help :)

Here is a link:
OpenSchema Method Example (VB)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschemax.asp
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
shaggy_the_sheepAuthor Commented:
Okay ive got this far...

<%

'BeginOpenSchemaVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub Main()

    Dim Cnxn
    Dim rstSchema
    Dim strCnxn
       
      Set Cnxn = Server.CreateObject("ADODB.Connection")
      Cnxn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../music/_private/musicfiles.mdb")
      
    Set rstSchema = Cnxn.OpenSchema(adSchemaTables)
   
    Do Until rstSchema.EOF
        Response.Write "Table name: " & _
            rstSchema("TABLE_NAME") & vbCrLf & "Table type: " & rstSchema("TABLE_TYPE") & vbCrLf
        rstSchema.MoveNext
    Loop
   
    ' clean up
    rstSchema.Close
    Cnxn.Close
    Set rstSchema = Nothing
    Set Cnxn = Nothing
    Exit Sub

End Sub
'EndOpenSchemaVB

Main()

%>

But i am getting an error:

Object or provider is not capable of performing requested operation.
/edit/getTable.asp, line 18

Line 18 being: Set rstSchema = Cnxn.OpenSchema(adSchemaTables)

Any ideas?

Thanks

Richard
0
 
Anthony PerkinsCommented:
Have you declared the constant adSchemaTables ?
0
 
Anthony PerkinsCommented:
Otherwise this is where Option Explicit comes in handy :)
0
 
shaggy_the_sheepAuthor Commented:
No i dont think i have declared it...where would i need to insert?

Thanks

Richard
0
 
Anthony PerkinsCommented:
It is as simple as adding the following at the start of your code:
Const adSchemaTables = 20

Or as a lot of ASP developers do, just add all the ADO constants using the include file ADOVBS.inc.
0
 
shaggy_the_sheepAuthor Commented:
Easy when you know how!! :-)

Thanks very much

Richard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now