|
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)
Next
outfile.Close
Exit_EnumerateTables:
Exit Function
Err_EnumerateTables:
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
Next
Debug.Print db.TableDefs(idx).Name & "- non-system"
End If
Next
Exit_EnumerateTablesDAO:
Exit Function
Err_EnumerateTablesDAO:
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:
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:
|
|
|
|
