tables in a DataBase

is there a way to read all tables in a database and list them? I want to simply list all the tables in a database. ultimately I want to put focus on one table and list the remaining tables. All help is appreciated. Thanks.

just in case anyone wanted to know how to do it:

aConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=yourdatasource/yourdatabasename.mdb"

Set connList = server.createobject("ADODB.Connection")
connList.ConnectionString = aConnectionString
connList.open

Const adSchemaTables = 20

Set Rs = connList.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))

' Loop through the list and print the table names
Do While Not Rs.EOF
if NOT Rs ("TABLE_NAME") = "MSysAccessObjects" AND NOT Rs ("TABLE_NAME") = "MSysAccessXML" AND NOT Rs ("TABLE_NAME") = "MSysACEs" AND NOT Rs ("TABLE_NAME") = "MSysObjects" AND NOT Rs ("TABLE_NAME") = "MSysQueries" AND NOT Rs ("TABLE_NAME") = "MSysRelationships" then
Response.Write "<BR>" & Rs ("TABLE_NAME")
Rs.MoveNext
end if
Loop

I did all the exceptions because OpenSchema gives you all the tables that access uses and I don't want to display those. I hope this helps. :thumbsup:

I tried, this, but it didn't work, can you be MORE specific on what I am to edit to have this work for my databases.

It looks neat.

Sorry,

What database are you using? The connection string above is for an access DB. You have to change this to correspond with the DB you are using.

access is mine...

What type of error are you getting? Did you use the exact code from above? Let me know.

Blank Page...

<%
TABLE = "tblComputer"

aConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("computer.mdb")

Set connList = server.createobject("ADODB.Connection")
connList.ConnectionString = aConnectionString
connList.open

Const adSchemaTables = 20

Set Rs = connList.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, TABLE))

' Loop through the list and print the table names
Do While Not Rs.EOF
if NOT Rs ("'" & TABLE & "'") = "MSysAccessObjects" AND NOT Rs ("'" & TABLE & "'") = "MSysAccessXML" AND NOT Rs ("'" & TABLE & "'") = "MSysACEs" AND NOT Rs ("'" & TABLE & "'") = "MSysObjects" AND NOT Rs ("'" & TABLE & "'") = "MSysQueries" AND NOT Rs ("'" & TABLE & "'") = "MSysRelationships" then
Response.Write "<BR>" & Rs ("'" & TABLE & "'")
Rs.MoveNext
end if
Loop

%>

so with the above code you get a blank page? can I have an example of what is in computer.mdb (give me the values in two records).

edit----
I see your problem I did not mean for the "Table" in "Array(Empty, Empty, Empty, "TABLE"))" to be changed as a variable you leave that as "Table" without explicitly assigning it any value. Tell me if that helps.

I changed that back.
Now I get error with:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

Line 17, or:
if NOT Rs ("" & TABLE & "") = "MSysAccessObjects" AND... ETC...
I tried ("" & TABLE & "") and ("'" & TABLE & "'") and even putting in ("tblComputer")

I even tried the 2 other tables in there... They are all very different from eachother.

Same error. Same place...
Any ideas?

<%
TABLE = "tblComputer"

aConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("computer.mdb")

Set connList = server.createobject("ADODB.Connection")
connList.ConnectionString = aConnectionString
connList.open

Const adSchemaTables = 20

Set Rs = connList.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))

' Loop through the list and print the table names
Do While Not Rs.EOF
if NOT Rs ("'" & TABLE & "'") = "MSysAccessObjects" AND NOT Rs ("'" & TABLE & "'") = "MSysAccessXML" AND NOT Rs ("'" & TABLE & "'") = "MSysACEs" AND NOT Rs ("'" & TABLE & "'") = "MSysObjects" AND NOT Rs ("'" & TABLE & "'") = "MSysQueries" AND NOT Rs ("'" & TABLE & "'") = "MSysRelationships" then
Response.Write "<BR>" & Rs ("'" & TABLE & "'")
Rs.MoveNext
end if
Loop

%>

try this, the only thing I changed was the "table"

I already changed that "TABLE" back... My last post tells you of the problem I have now...

Test the (exact) code you gave me, with one of your databases, tell me if it works, if so, send me the database (make a new one that works) And I will test it on my server...

here is working code

<%@ language=VBscript %>
<% Response.Buffer = True %>
<%

aConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=PATHNAME"

Set connList = server.createobject("ADODB.Connection")
connList.ConnectionString = aConnectionString
connList.open

Const adSchemaTables = 20 'number of how many tables to display

Set Rs = connList.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
Rs.movefirst

%>
<html>
<body>
<table>
<% ' Loop through the list and print the table names
Do While Not Rs.EOF
if NOT Rs ("TABLE_NAME") = "MSysAccessObjects" AND NOT Rs ("TABLE_NAME") = "MSysAccessXML" AND NOT Rs ("TABLE_NAME") = "MSysACEs" AND NOT Rs ("TABLE_NAME") = "MSysObjects" AND NOT Rs ("TABLE_NAME") = "MSysQueries" AND NOT Rs ("TABLE_NAME") = "MSysRelationships" then
%>
<tr>
<td><font face="arial,helvetica" size="2"><% Response.Write Rs ("TABLE_NAME")%></font></td>
</tr>
<%
end if
Rs.MoveNext
Loop
%>
</table>
</body>
</html>
<%
' Close and destroy the recordset and connection objects

Rs.close
Set Rs = Nothing

connList.close
Set connList = Nothing
%>
<% Response.End %>

tell me what you get

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

Line 25, or:
if NOT Rs ("" & TABLE & "") = "MSysAccessObjects" AND... ETC...

I am getting the same thing as pretty much before...
I don't get it...

If you are making this code, and testing it on your server...
Try to do it with a new database...

Then, when you have it working with a new database, zip it up, and post it on this forum for me to download, and I will try it...

you get the above error when either you have the wrong field names or wrong table names. Make sure that they are accurate. This code works.

Plus keep it the same
if NOT Rs ("TABLE_NAME") = "MSysAccessObjects" ...
don't change this to
if NOT Rs ("" & TABLE & "") = "MSysAccessObjects" ...

Oh wait..
LOL
This is listing tables, eh?
I though it was listing the information in a table from the tables.

Hahah
It works fine now... Thank you.

Const adSchemaTables = 20

Regarding the line above and going by your revised code, you comment that "number of how many tables to display"

If I change that to include the number of non-system tables in my db (ie: 9) I get an error:
ADODB.Connection error '800a0cb3'

Object or provider is not capable of performing requested operation
the error references this line:

Set Rs = connList.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))

Is this normal?

Not a big deal, just wondering (code works if I leave 20).

- sin

I will try to see if I can find an anwser to this. If I can reproduce the error I will post my findings.

I have a similar requirement and I have gone as far as the connection string but when it starts to execute the part:

' 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
Conn.Open "DSN=refuse_bag_issues"

' Open the database schema to query the list of tables. Extract the
' list in a Recordset object
Set Rs = Conn.OpenSchema (adSchemaTables) <<--- I get an error here:
ADODB.Connection(0x800A0CB3)
Object or provider is not capable of performing requested operation.


' Loop through the list and print the table names
Do While Not Rs.EOF
Response.Write "<BR>" & Rs ("TABLE_NAME")
Rs.MoveNext
Loop

' Close and destroy the recordset and connection objects
Rs.Close
Set Rs = Nothing

Conn.Close
Set Conn = Nothing
-------

I was wondering if you have encountered a similar problem? If you have any idea on how to rectify this, you're advise will greatly appreciated.

Hello there, i was just wondering, if you dont want Msys tables to show up, just don't list them, maybe this works ("TABLE_TYPE") Property:


Dim conn
Dim tablas

'Open the connection for the DB
dbpath = "DATA/mydb.mdb"
set conn = server.createobject("ADODB.Connection")
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

'Open an schema for our tables
Const adSchemaTables = 20
set tablas = conn.OpenSchema (adSchemaTables)

Do While Not tablas.EOF
If tablas("TABLE_TYPE") = "TABLE" Then

Response.Write(tablas("TABLE_NAME"))

end if
tablas.MoveNext
Loop

tablas.close
conn.close

set tablas = nothing
set conn = nothing



Let me know if someone finds this useful :thumbsup: