Query Table Properties

[Paul H]

I would like to query the basic table properties of all the tables in my database,

Table Name

Date Created

Date Modified

Description

I need to do some side by side comparisons between to versions of my application and this would help me zone in on which tables need to be modified. I know this in buried in some of the hidden tables, but I haven't worked with those before. Can I borrow someones query? It would be much appreciated.

[June7]

Google: Access VBA table properties TableDefs

Review: http://msdn.microsoft.com/en-us/library/ff195790.aspx

[ssanfu]

The following code will work in A2000, don't have A2010 to test it:

Public Sub GetTableINfo()
   Dim db As Database
   Dim i As Integer


   Dim TblName As String
   Dim C_Date As Date
   Dim U_Date As Date
   Dim Desc As String

   Set db = CurrentDb

   On Error Resume Next

   db.TableDefs.Refresh
   Debug.Print "Name, Created , Updated"
   For i = 0 To db.TableDefs.Count - 1
      Desc = ""
      If Left(db.TableDefs(i).Name, 4) <> "Msys" Then
         TblName = db.TableDefs(i).Properties(0)
         C_Date = db.TableDefs(i).Properties(2)
         U_Date = db.TableDefs(i).Properties(3)
         Desc = db.TableDefs(i).Properties(14)
         
         Debug.Print TblName & " / " & C_Date & " / " & U_Date & " / " & Desc

      End If
   Next i
   Set db = Nothing
   MsgBox "done"
End Sub

[Paul H]

This code gives me a lot of useful information including all the field names. These are falling under description. As is this gets me what I need, but I would also like to get the text description that is found in the table properties form. For future reference, could you point me at a list of table properties and their associated numbers?

Sorry, one more item. I am pasting this data into Excel to parse out. The last column with the field names is also full of question marks and an unprintable character, a tiny box with a "?" in it. I am trying to do a find and replace to get rid of them, but I'm not having any luck. Any suggestions?

[June7]

Are you referring to the text description for each field of table? Review http://allenbrowne.com/func-06.html

This code will list the 21 table properties by index and name:

Sub ListTableProps()
Dim db As DAO.Database
Dim tdf As TableDef
Dim i As Integer
Set db = CurrentDb
Set tdf = db.TableDefs("tablename")
For i = 0 To 20
    Debug.Print i & " : " & tdf.Properties(i).Name
Next
End Sub

Google: access excel export has box with question mark
Review http://www.mrexcel.com/forum/showthread.php?t=321197

[Paul H]

I actually found this elsewhere, combining the clean(trim(cell ref)) did most of the work. I still was left with a lot of question marks and some inconsistent parsing. Seems like this should be simpler. I also tried loading the output into an Access table, but for some reason the text fields would not populate.

I adapted Ssanfu's code like this.

Public Function GetTableINfo2()
   Dim db As Database
   Dim i As Integer

   Dim TblName As String
   Dim C_Date As Date
   Dim U_Date As Date
   Dim Desc As String
   Dim myRS As Recordset
   Dim SQL As String
   Set db = CurrentDb
   
   
    SQL = "Select * from a_tblTables"
   Set myRS = CurrentDb.OpenRecordset(SQL)
   On Error Resume Next
   
   With myRS
       db.TableDefs.Refresh
       Debug.Print "Name, Created , Updated, Desc"
       
       For i = 0 To db.TableDefs.Count - 1
          Desc = ""
          If Left(db.TableDefs(i).Name, 4) <> "Msys" Then
             TblName = Len(db.TableDefs(i).Properties(0))
             C_Date = db.TableDefs(i).Properties(2)
             U_Date = db.TableDefs(i).Properties(3)
             Desc = Trim(db.TableDefs(i).Properties(14))
             
             Debug.Print TblName & " , " & C_Date & " , " & U_Date & " , " & Desc
          End If
          .AddNew
            !Table = TblName
            !Created = C_Date
            !Updated = U_Date
            !Description = !Desc
          .Update
    
       Next i
           
    End With 

Here's the result from my table.

tblName	Created	Updated	Fields
	3/20/2012 12:46:32 PM	3/20/2012 12:57:55 PM
	9/29/2011 8:58:09 AM	9/29/2011 8:58:16 AM
	9/29/2011 8:58:09 AM	9/29/2011 8:58:16 AM
	9/29/2011 8:58:09 AM	9/29/2011 8:58:16 AM
	9/29/2011 8:58:09 AM	9/29/2011 8:58:16 AM

This has moved past the practical stage, at least for the moment. If I could get this to work simply it would be something I would use, but as it stands, I can't spend much more time figuring this out. It will nag at me though, so if you have any other suggestions I'll look them over.

I appreciate both your and Ssanfu's help with this.

[ssanfu]

          .AddNew
            !Table = TblName
            !Created = C_Date
            !Updated = U_Date
            !Description = !Desc
          .Update

Why do you have the bang (!) in front of the variable Desc?? It shouldn't be there....

[Paul H]

Of course you are correct. I made a few changes including this new function I found here. The full function appears at the end and uses "Wizhook", something I never heard of till a few minutes ago.

GetColumnNames(strObjectName As String, Optional ShowDetails As Boolean = False) As String

This produces none of the problems that show up when using

Desc = db.TableDefs(i).Properties(14)

 Public Function GetTableINfo2()
   Dim db As Database
   Dim i As Integer

   Dim TblName As String
   Dim C_Date As Date
   Dim U_Date As Date
   Dim Desc As String
   Dim myRS As Recordset
   Dim SQL As String
   Dim Fields As String
   Set db = CurrentDb
   
   DoCmd.SetWarnings False
   DoCmd.RunSQL "Delete FROM a_tblTables"
   DoCmd.SetWarnings True
    SQL = "Select * from a_tblTables"
   Set myRS = db.OpenRecordset(SQL)
   On Error Resume Next
   
   With myRS
       db.TableDefs.Refresh
       Debug.Print "Name, Created , Updated, Fields"
       
       For i = 0 To db.TableDefs.Count - 1
          Desc = ""
          If Left(db.TableDefs(i).Name, 4) <> "Msys" Then
             TblName = db.TableDefs(i).Properties(0)
             C_Date = db.TableDefs(i).Properties(2)
             U_Date = db.TableDefs(i).Properties(3)
             Fields = GetColumnNames(db.TableDefs(i).Properties(0))
             
             Debug.Print TblName & " , " & C_Date & " , " & U_Date & " , " & Fields
          End If
          
          .AddNew
            !TblName = TblName
            !Created = C_Date
            !Updated = U_Date
            !Fields = Fields
          .Update
       Next i
           
    End With
       
       Set db = Nothing
End Function

Function GetColumnNames(strObjectName As String, Optional ShowDetails As Boolean = False) As String 'Returns the names of all columns in the specified table or query
'If ShowDetails = False, the return value is like so:
' ID;Column1;Column2;
'If ShowDetails = True, the return value is like so:
' ID;4;4;Column1;10;100;Column2;4;4;
' In this case,the columns represent the following data:
' Column name ; DAO datatype constant:
' (dbLong = 4, dbText = 10, etc) ; Max bytes per column
With WizHook
WizHook.Key = 51488399 'This is an internal Wizhook key.
'It only needs to be given once per
'user session, but most Wizhook
'functions won't work without it.
If ShowDetails Then
GetColumnNames = .GetInfoForColumns(strObjectName) 'where strObjectName is the table name passed to the function
Else
GetColumnNames = .GetColumns(strObjectName) 'where strObjectName is the table name passed to the function
End If
End With
'MsgBox GetColumnNames
End Function

I'm happy with the finished product which I can quickly drop into Excel and parse out using Text to Columns.

It's been an interesting discussion.

[ssanfu]

Glad you were able to get the data you wanted....