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.
|