Listing tables

[parkes]

I've got an Access database and I need to get a list of tables that I've created. How can I do this?

[AKA]

Access has system tables as every (?) SQL database. You get the table names with the query below. But I dont know fore sure how to remove the system tables from the result but you could also test on the name.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));

Check the help in Access about System tables.

Or simply view the system tables with :

To make system objects visible, click Options on the Tools menu, click the View tab, and then select the System Objects check box.

Hope this helps, I have never used it by myself.

[BruceG]

The following code will list your tables to a listbox:

    Dim db      As Database
    Dim td      As TableDef

    List1.Clear

    Set db = OpenDatabase("C:\Whatever\MyData.mdb")

    ' Loop thru and add the table name to the listbox
    For Each td In db.TableDefs
        If Left$(td.Name, 4) <> "MSys" Then
            List1.AddItem td.Name
        End If
    Next

    db.Close

    Set td = Nothing
    Set db = Nothing

[alexn]

The following should work from an Access module:

Option Base 0
Option Explicit
Sub tablenames()
Dim dbsData As Database, tdfTable As TableDef, a() As String, i As Integer, j As Integer
Set dbsData = CurrentDb
i = 0
j = 0
For Each tdfTable In CurrentDb.TableDefs 'loop through each tabledef in collection tabledefs
'assume dbsystemobject attribute is set correctly
If (tdfTable.Attributes And dbSystemObject) = False Then ' could also test dbhiddenobject
ReDim Preserve a(j)
a(j) = dbsData.TableDefs(i).Name ' record table names in an array
j = j + 1
End If
i = i + 1
Next tdfTable
'you could then dump the array to excel
'(you might have to select excel objects under tools:references for this to work) :-
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
objExcel.Application.Workbooks.Add
objExcel.Application.Range("a1").Select
For j = 0 To UBound(a)
objExcel.ActiveCell.Value = a(j)
objExcel.ActiveCell.Offset(1, 0).Select
Next j
objExcel.Application.Visible = True
End Sub

Alex

[Edited by alexn on 08-21-2000 at 10:55 PM]

[alexn]

how do i get the code in my reply to retain vb format??