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
  Next
db.close
Set NonSystemTables = colTables

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

Set NonSystemTables = Nothing

End Function
Public Sub OpenSchemaX()

Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String

Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn

Set rstSchema = cnn1.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close

cnn1.Close

End Sub
Private Sub PrintTableName()
    Dim cat As New ADOX.Catalog

    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source= 'c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"

    For i = 0 To cat.Tables.Count - 1
        If cat.Tables(i).Type = "TABLE" Then
            Debug.Print cat.Tables(i).Name
        End If
    Next i
End Sub
Private Sub PrintTableName()
    Dim cat As New ADOX.Catalog

    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source= 'c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"

    For i = 0 To cat.Tables.Count - 1
        If cat.Tables(i).Type = "TABLE" Then
            Debug.Print cat.Tables(i).Name
        End If
    Next i
End Sub
Private Sub PrintTableName()
    Dim cat As New ADOX.Catalog

    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source= 'c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"

    For i = 0 To cat.Tables.Count - 1
        If cat.Tables(i).Type = "TABLE" Then
            Debug.Print cat.Tables(i).Name
        End If
    Next i
End Sub
Private Sub PrintTableName()
    Dim cat As New ADOX.Catalog

    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source= 'c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"

    For i = 0 To cat.Tables.Count - 1
        If cat.Tables(i).Type = "TABLE" Then
            Debug.Print cat.Tables(i).Name
        End If
    Next i
End Sub

 

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
Next
db.close
Set NonSystemTables = colTables

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

Set NonSystemTables = Nothing

End Function

Public Sub OpenSchemaX()

Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String

Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn

Set rstSchema = cnn1.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close

cnn1.Close

End Sub

Private Sub PrintTableName()
Dim cat As New ADOX.Catalog

cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= 'c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb';"

For i = 0 To cat.Tables.Count - 1
If cat.Tables(i).Type = "TABLE" Then
Debug.Print cat.Tables(i).Name
End If
Next i
End Sub