Check Tables In A Database

Hello Experts.
I open a database (set newDB=workspace.opendatabase....)
Is there any way to check how many tables this database
contain ? and their names ?
Many thanks
Who is Participating?
n_narayananConnect With a Mentor Commented:

It is working fine for me.

What you do, first open the vb project then go to project / references menu to add Microsoft DAO Reference 2.5 or 3.51

Put a Command Button and Put a Listbox

in the command button click event write this,
But note that you have to give the valid database

Private Sub Command1_Click()
Dim tables As Collection, i As Integer
 Set tables = NonSystemTables("D:\mydatabase.mdb")
 For i = 1 To tables.Count
    List1.AddItem tables(i)
End Sub

Also pate this function inside,

Public Function NonSystemTables(dbPath As String) As Collection

'Input: Full Path to an Access Database

'Returns: Collection of the names
'of non-system tables in that database
'or Nothing if there is an error

'Requires: a reference to data access
'objects (DAO) in your project

On Error GoTo ErrHandler

Dim td As DAO.TableDef
Dim db As DAO.Database
Dim colTables As Collection

Set db = workspaces(0).opendatabase(dbPath)

Set colTables = New Collection

 For Each td In db.TableDefs

    If td.Attributes >= 0 And td.Attributes <> dbHiddenObject _
         And td.Attributes <> 2 Then
          colTables.Add td.Name
    End If
Set NonSystemTables = colTables

Exit Function
On Error Resume Next
If Not db Is Nothing Then db.Close

Set NonSystemTables = Nothing

End Function

If you still have problem,

Give me your email id. I will mail you the project.



Ryan ChongCommented:
Debug.print newDB.TableDefs.Count

For i = 1 To newdb.TableDefs.Count - 1
        If myTableName = newdb.TableDefs(i).Name Then
            CheckTableName = True
            MsgBox "Got Table":exit for
        End If
    Next i
For Access Database


You can use SQLDMO object for SQL Server,

Dim oTable As SQLDMO.Table
Dim oColumn As SQLDMO.Column

Me.MousePointer = vbHourglass

lblInfn.Visible = False

If Trim$(cboData.Text) <> "" Then

For Each oTable In oSQLServer.Databases (cboData.Text).Tables
        cboTable.AddItem oTable.Name
End If
lblInfn.Visible = True

The code is taken from the link


Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

HESUSAuthor Commented:
To ryancys thank you.
I need a solution for a case that I do not know the names
of the tables.
HESUSAuthor Commented:
To Narayanan many thanks.
I tried to use the code that you refered me to( and when I run it, I get an error message in the line :
                  colTables.Add td.Name
the error:Method or data member not found.
I do have the dao ref.
any sugestions?
thank you very much.
Ryan ChongCommented:
Simple, is this what you want:

For i = 1 To newdb.TableDefs.Count - 1
       Debug.Print newdb.TableDefs(i).Name
   Next i

? or ..?
HESUSAuthor Commented:
To ryancys.
Many thanks for your efforts.
The solution you are suggesting is providing few other strings that are not relevant to my needs. I guess that I can filter them, but I cannot be sure that these are the only once.
Thanks again for the help.
HESUSAuthor Commented:
To n_narayanan thank you.
I still get the same error message (collection type do not have Collection. Add),
But maybe this is because I am still using VB6.
Anyway, instead of using Collection type, I am using A List, and I get the results I need.
Many thanks for your willing to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.