Home Page
Tips And Tricks
Helpful Links
e-mail me

Picture of building from outside Tips And Tricks

I seem to run into things from time to time that I want to share with the world, usually things that I had to find out the hard way. Sometimes it will just be something that I had to dig for, and want to make more readily accessible. This page is intended to make life a little easier for other programmers. If it meets your need, then that's what it's here for.

Enumerate MS Access Table Names

I had created a page on the site with an example of enumerating project properties, and it seemed like people were searching for ways to enumerate other things, and found my site. Well, it occurred to me that it would be advantageous to be able to enumerate some other things anyway. We all have those times when we need to be able to get counts or lists of various items in a project or database, like that time that I was explaining the project to a consultant that the customer hired.

We had so many tables in our project that the buffer for the immediate window in the Access 2000 Visual Basic Editor wouldn't hold them all, so in this example I've used the File System Object to write out a delimited text file containing the table names. Since the file extension is ".csv", if this file is opened on a machine where Excel is installed, the file will come up in Microsoft Excel.
'produce a list of table names in the current project -BWM 10-26-09
Function EnumerateTables()
 On Error GoTo Err_EnumerateTables
 Dim idx As Long
 Dim fs As Variant
 Dim outfile As Variant
 Dim outline As String
 Dim FileFullPath, FileFullName
 'create a text file to write out the text to a file
 ChDrive NetDevDrive 'Const NetDevDrive = "I:"
 ChDir NetDevFolder 'Const NetDevFolder = "\accdev\"
 FileFullPath = "."
 FileFullName = "TableList" & Format(Now(), "yyyy-mm-dd-hhnn") & ".csv"
 Set fs = CreateObject("Scripting.FileSystemObject")
 Set outfile = fs.CreateTextFile(CurDir() & "\" & FileFullName, True)
 For idx = 0 To CurrentProject.Application.CodeData.AllTables.Count - 1
  outline = Chr(34) & CurrentProject.Application.CodeData.AllTables(idx).Name & Chr(34)
  Debug.Print outline
  outfile.writeline (outline)
 Exit Function

 MsgBox "Error #" & Err.Number & ": " & Err.Description
 Resume Exit_EnumerateTables
End Function
When I wrote this, I used portions of a program which had been created to read our source code and gather statistics on it.

After I wrote this one, it occurred to me that someone might also want an example of the DAO method of doing the same thing. I found an example of that, as I had in the past, but I didn't like the fact that the example relied on the table name to determine if a table was a "system" table. To that end, I experimented with the various table attributes until I found one that seems to be set to zero for non-system tables. If you don't need a list of the properties for each table, then comment that part out, but I do find it to be helpful to be able to enumerate table properties.
'produce a list of tables in the current database using DAO -BWM 10-26-09
Function EnumerateTablesDAO()
 On Error GoTo Err_EnumerateTablesDAO
 Dim idx As Long
 Dim db As DAO.Database
 Dim prp As Property
 Dim prps As Property
 Set db = CurrentDb
 For idx = 0 To db.TableDefs.Count - 1
  Debug.Print db.TableDefs(idx).Name
  If db.TableDefs(idx).Attributes = 0 Then 'non-system
   Set prps = db.TableDefs(idx).Properties
   For Each prp In prps
    Debug.Print prp.Name & " - " & prp.Value
   Debug.Print db.TableDefs(idx).Name & "- non-system"
  End If
 Exit Function

 MsgBox "Error #" & Err.Number & ": " & Err.Description
 Resume Exit_EnumerateTablesDAO
End Function

It should also be noted that a list of table names can be produced using a query such as:
SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Type=1 AND MSysObjects.Flags=0;
If you enter that text into the SQL view of the query builder and then switch to Design view, you'll see something like this:

Image of MS Access Design View for query to list tables

All of which probably boils down to more ways to enumerate tables than you ever thought you'd need, but the best method to use may vary with each situation.

If you found this information to be useful, and would like to donate via PayPal to help offset the cost of maintaining this web site, use the following button:

|Home Page| |Custom Software| |Policies| |About| |Tips And Tricks| |Helpful Links|