Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Tables in db (Access 2K, SQL Server 2005)

    I have an Access/SQL Server project and need to query a set of tables and only list those with data in them. I'd like to display the name of the table itself in the resultset. I am working on a VBA solution and wanted to ask if anyone has had some experience doing this that may help me hack thru the object library options... I have done this sort of thing in ASP before so I know it is possible to query and list tables by their names in a db but haven't done this is VBA for Access...

    TIA!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Tables in db (Access 2K, SQL Server 2005)

    The following code requires a reference (in Tools | References) to the Microsoft DAO 3.6 Object Library.

    Sub ListTables()
    ' Declarations
    Dim col As New Collection
    Dim itm As Variant
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    ' Fill collection
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    If Not Left(tdf.Name, 4) = "msys" And _
    Not Left(tdf.Name, 1) = "~" Then
    Set rst = dbs.OpenRecordset(tdf.Name, dbOpenDynaset)
    If Not rst.RecordCount = 0 Then
    col.Add tdf.Name
    End If
    rst.Close
    End If
    Next tdf
    ' Do something with the collection, e.g.
    For Each itm In col
    Debug.Print itm
    Next itm
    ' Clean up
    Set rst = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set col = Nothing
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Tables in db (Access 2K, SQL Server 2005)

    great! -- thanks. it works. the only change that was needed was to add dbSeeChanges due to some config I have set up between Access and SQL Server...

Posting Permissions

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