Access VBA

css navigation by Css3Menu.com

Capture Table Info

We got a particularily messy Access database from an internal customer. There were tables from other Access databases, some from SQL Server at their location, and some pointing to our SQL Server.

Needed help making sense of it all. Found that some tables had links but did not have file in the other place.

Sub listTables(bShowSys As Boolean) As String
On Error GoTo listTables_Error
    Dim db      As DAO.Database
    Dim td      As DAO.TableDefs
    Dim T
 Open "\\daPlace\IT\MacroAlan\TErep_tables.txt" For Output As #1          'Output to txt
print #1, "Table Name|Link if not Local|Updated|"
    Set db = CurrentDb()
    Set td = db.TableDefs
    For Each T In td    'loop through all the fields of the tables
        If Left(T.Name, 4) = "MSys" And bShowSys = False Then GoTo Continue
        Print #1, T.Name & "|" & T.Connect & "|" & T.LastUpdated

Continue:
    Next
 Close #1
    Set td = Nothing
    Set db = Nothing
If Err.number = 0 Then Exit Sub
 
listTables_Error:
     Close #1
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.number & vbCrLf & "Error Source: listTable" & vbCrLf & _
    "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    Exit Sub
End Sub

This runs thru 50 tables in seconds. I plan to add it to my Add-Ins soon to make widely available.

© 2016-2017

Updated:  12/10/2017 15:42
This page added:  04 May 2016