Extracting a list of tables in a database

Dim db as Dao.Database
Dim tdf as Dao.TableDef
Set db = CurrentDb
For Each tdf in db.TableDefs
     If Left(tdf.Name,4) <> "Msys" Then     'ignore system tables
         Debug.Print tdf.Name                    ' or do something else with the names
    End If
Next
Set db = Nothing

With ADO

Public Sub OpenSchemaX()

   Dim cnn1 As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnn As String
     
   Set cnn1 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn
     
   Set rstSchema = cnn1.OpenSchema(adSchemaTables)
   
   Do Until rstSchema.EOF
      Debug.Print "Nombre de tabla: " & _
         rstSchema!TABLE_NAME & vbCr & _
         "Tipo de tabla: " & rstSchema!TABLE_TYPE & vbCr
      rstSchema.MoveNext
   Loop
   rstSchema.Close
   
   cnn1.Close
   
End Sub

Also, with dao you need to avoid to show every table who's name start with "msys".

machpros answer is the correct answer, althogh you also could add a refernece to the Access object Model and then refer to Access.AllTables, i believe.

Richie Simonetti:
Thank you for your help.
Here is my code, using your suggestions.
The result is 32767 identical entries into the ListBox:

xx MSysAccessObjects  ACCESS TABLE
Where "xx" is "ii" in the code below, and goes from 1 to 32767.

I put code in which skips if first four characters are "MSys", and get nothing.  What am I doing wrong?

Meantime, I will try machpro's method. -- Norm
=========================
Private conADO As ADODB.Connection

Private Sub cmdGo_Click()
    Dim rstSchema As ADODB.Recordset
    Dim strPath As String
    Dim strCnn As String
    Dim strTableName As String
    Dim strTableType As String
    Dim ii As Integer
   
    On Error GoTo GoErr
    ii = 0
    Lbox.Clear
   
    strPath = CurDir & "\ISIdatabase1.mdb"
    Set conADO = MakeConnection(strPath, "MACHUPICHU")
 ' [MakeConnection is the routine I use to make
 ' connections, providing the password]
    strCnn = "Provider=sqloledb;" & _
            "Data Source=srv;" & _
            "Initial Catalog=pubs;" & _
            "User Id=sa;"
    Set rstSchema = conADO.OpenSchema(adSchemaTables)
   
    Do Until rstSchema.EOF
        strTableName = rstSchema!TABLE_NAME
        strTableType = rstSchema!TABLE_TYPE
        ii = ii + 1
        Lbox.AddItem Str(ii) & " " & strTableName & "  " & strTableType
        Lbox.Refresh
    Loop
    Exit Sub
GoErr:
    Text1.Text = ii
End Sub

Mach1pro:
Please explain the two dim statements.
VB returns the diagnostic: "User defined type not defined" when I try to run the code.

If it matters: We are using ADO. -- Norm

Richie Simonetti:
Thank you!  It Works!
I appreciate your help. -- Norm