Results 1 to 8 of 8

Thread: ADO question.

  1. #1
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232

    Unanswered: ADO question.

    I'm trying to query tables in an Access db based on the name of the table. If the name matches a certain criteria I want to run a piece of SQL on it.

    In DAO, you have a TableDef object which relates to the tables in a db and you can then test for a .Name property and then if you want run the SQL.

    However, in this brave new world I'm trying to learn ADO. My question is, ADO has no TableDef object that I can find and so how do I cycle through the tables in the db and then run SQL based on the table name?

    TIA.

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    There are probably several methods, here's the OpenSchema method of the connection.
    Code:
        Dim rs As ADODB.Recordset
        Dim cn As ADODB.Connection
        Set cn = CurrentProject.Connection
        Set rs = cn.OpenSchema(adSchemaTables, _
                 Array(Empty, Empty, Empty, "TABLE"))
        'Set rs = cn.OpenSchema(adSchemaTables)
        Do While Not rs.EOF
            Debug.Print rs.Fields("TABLE_NAME").value, _
                        rs.Fields("TABLE_TYPE").value
            rs.MoveNext
        Loop
    As indicated, you might need to remove/alter the restriction on TABLE_TYPE to get more than native tables.
    Roy-Vidar

  3. #3
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Thanks Roy, I'll check this out.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another option is to query MSysObjects though MS recommend you don't.

    Also you would need to use the ADOX library if you want to start manipulating objects using ADO to the same sort of degree as DAO - just in case you start doing more advanced stuff one day.

    OpenSchema.... nice
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Thanks for the advice Pootle, however where do I find the ADOX library as I can't find it in the references.

    I'm likely to be looking up the wrong name, but I can't dinf it under Microsoft .... now simply ADOX.

    Any ideas.

    Thanks,

    Dave.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon. The ADOX bit is like ADODB - the prefix when using the library. The library is called
    Microsoft ADO Ext. 2.x for DDL and Security

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    And, here's a small sample - I think you'll need similar restriction on this
    Code:
        Dim cat As ADOX.Catalog
        Dim tbl As ADOX.Table
        
        Set cat = New ADOX.Catalog
        Set cat.ActiveConnection = CurrentProject.Connection
        For Each tbl In cat.Tables
            Debug.Print tbl.Name, tbl.Type
        Next tbl
    Roy-Vidar

  8. #8
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Thanks Pootle, I wasn't sure what I was missing but did see the reference you're speaking of but thought it wouldn;t be that.

    Again thanks Roy, I very much appreciate your help!

Posting Permissions

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