I've got an Access database and I need to get a list of tables that I've created. How can I do this?
Printable View
I've got an Access database and I need to get a list of tables that I've created. How can I do this?
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.
The following code will list your tables to a listbox:
Code: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
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]
how do i get the code in my reply to retain vb format??