Retrieve list of tables within MS Access Database

There are a couple of ways:
A.  Query the system tables: MSysObjects
B.  (Better approach) Is to use the Connection's OpenSchema method.

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

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

Have you declared the constant adSchemaTables ?

Otherwise this is where Option Explicit comes in handy :)

No i dont think i have declared it...where would i need to insert?

Thanks

Richard

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.

Easy when you know how!! :-)

Thanks very much

Richard