Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    I would rather be a lion for a day than a lamb that lives forever.

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    I would rather be a lion for a day than a lamb that lives forever.

  • #3
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    I would rather be a lion for a day than a lamb that lives forever.

  • #5
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    access is mine...

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post
    What type of error are you getting? Did you use the exact code from above? Let me know.
    I would rather be a lion for a day than a lamb that lives forever.

  • #7
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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

    %>

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Last edited by ecnarongi; 04-03-2003 at 10:31 PM.
    I would rather be a lion for a day than a lamb that lives forever.

  • #9
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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?

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post
    <%
    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 would rather be a lion for a day than a lamb that lives forever.

  • #11
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    I already changed that "TABLE" back... My last post tells you of the problem I have now...

  • #12
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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...

  • #13
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post
    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
    I would rather be a lion for a day than a lamb that lives forever.

  • #14
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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...

  • #15
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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...


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •