Get a list of table names and all their fields

[milage]

Hi, I know similar questions have been asked regarding this subject but they all seem to relate to Oracle and SQL server databases, not Access

How do I get a list of all the Table Names, Fields, Types and Descriptions?

Cheers

 

[Lightning]

You can use the built-in Documentor. Select Tools|Analyze|Documentor and then select which objects you wish to document.

Be Warned! This can produce very loooong reports!

HTH
Lightning

 

[milage]

Thanks,

Problem is I need to know how to do it in SQL terms as I need it to be reported outside Access itself (On an ASP page)

Would have asked in the SQL forum but they are all Oracle Gurus there!

Cheers Anyway

 

[GeekGirlau]

Hi Milage - try this.

You need to create a table called tblSYSTableFields, with text fields called "Table", "FieldName", "Type", "Size" and "Description". Make sure "Description" has Allow Zero Length set to Yes.

The SetProperty function is required because the Description property may not technically exist until you append it to the database - then you can read it.

Sub ListTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rec As DAO.RecordSet
Dim i As Integer


Set db = CurrentDb
Set rec = db.OpenRecordset("tblSYSTableFields", dbOpenDynaset)

DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelTableFields"
DoCmd.SetWarnings True

DoCmd.Hourglass True

For Each tdf In db.TableDefs
Select Case LCase(Left(tdf.Name, 4))
Case "msys"

Case Else
Select Case UCase(tdf.Name)
Case "switchboard items", "tblsystablefields"

Case Else
For i = 1 To tdf.Fields.count - 1
With rec
.AddNew
!Table = tdf.Name
!FieldName = tdf.Fields(i).Name
!Type = tdf.Fields(i).Type
!Size = tdf.Fields(i).Size
!Description = SetProperty(tdf.Fields(i), "Description&quot
.Update
End With
Next i
End Select
End Select
Next tdf

DoCmd.Hourglass False
End Sub


Function SetProperty(fldTemp As Field, strName As String) As String
Dim prpNew As Property
Dim errLoop As Error
Dim strTemp As String


' Attempt to set property
On Error GoTo Err_Property
strTemp = fldTemp.Properties(strName)
On Error GoTo 0

If strTemp = "Blank" Then
SetProperty = ""
Else
SetProperty = strTemp
End If
Exit Function


Err_Property:
' Error 3270 means that the property was not found.

If DBEngine.Errors(0).Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = fldTemp.CreateProperty(strName, _
dbText, "Blank&quot
fldTemp.Properties.Append prpNew
Resume

Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop

SetProperty = ""
Exit Function
End If
End Function

 

[GeekGirlau]

Oops - forgot to mention you also need a query called "qdelTableFields" that just deletes all records in tblSYSTableFields. s-)

 

[GeekGirlau]

I've been playing further ...

The "Type" property displays as a number, so I've created a little function that gives you a text description for the Type. The code is now as follows:

Sub ListTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rec As DAO.RecordSet
Dim i As Integer


Set db = CurrentDb
Set rec = db.OpenRecordset("tblSYSTableFields", dbOpenDynaset)

DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelTableFields"
DoCmd.SetWarnings True

DoCmd.Hourglass True

For Each tdf In db.TableDefs
Select Case LCase(Left(tdf.Name, 4))
Case "msys"

Case Else
Select Case UCase(tdf.Name)
Case "switchboard items", "tblsystablefields"

Case Else
For i = 1 To tdf.Fields.count - 1
With rec
.AddNew
!Table = tdf.Name
!FieldName = tdf.Fields(i).Name
!Type = GetType(tdf.Fields(i).Type)
!Size = tdf.Fields(i).Size
!Description = SetProperty(tdf.Fields(i), "Description&quot
.Update
End With
Next i
End Select
End Select
Next tdf

DoCmd.Hourglass False
End Sub


Function SetProperty(fldTemp As Field, strName As String) As String
Dim prpNew As Property
Dim errLoop As Error
Dim strTemp As String


' Attempt to set property
On Error GoTo Err_Property
strTemp = fldTemp.Properties(strName)
On Error GoTo 0

If strTemp = "Blank" Then
SetProperty = ""
Else
SetProperty = strTemp
End If
Exit Function


Err_Property:
' Error 3270 means that the property was not found.

If DBEngine.Errors(0).Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = fldTemp.CreateProperty(strName, _
dbText, "Blank&quot
fldTemp.Properties.Append prpNew
Resume

Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop

SetProperty = ""
Exit Function
End If
End Function


Function GetType(intType As Integer) As String
Select Case intType
Case 1: GetType = "Yes/No"
Case 2: GetType = "Byte"
Case 3: GetType = "Integer"
Case 4: GetType = "Long"
Case 5: GetType = "Currency"
Case 6: GetType = "Single"
Case 7: GetType = "Double"
Case 8: GetType = "Date/time"
Case 10: GetType = "Text"
Case 11: GetType = "OLE Object"
Case 12: GetType = "Memo or Hyperlink"
Case 15: GetType = "Replication ID"
Case Else: GetType = intType
End Select
End Function

 

[milage]

Wow!

That must have taken a bit of time! I'm sure other people will find it useful, the only problem is that I can't do things like that in an ASP page (or at least I don't think I can) in VB Script.

What I really need are the names and fields of the various System Tables within Access so I can do my own queries on them to get what I need. Does anyone have a list of these?

 

[PaulF]

why not use ComboBoxes, one for the Tables and the second for the FieldNames

use this for the Row Source for the Table ComboBox (cboTable):

Select [name] From msysobjects where mid([name],2,3)<>'sys' and type = 1 or mid([name],2,3)<>'sys' and type = 6

Use FieldList for the Row Source Type for the Fields ComboBox (cboFields)

Add this Code to the Table ComboBox AfterUpdate Event

Private Sub cboTable_AfterUpdate()
If cboTable <> &quot;&quot; And cboTable <> &quot; &quot; Then
cboFields.RowSource = cboTable
Me.Refresh
End If
End Sub

PaulF

 

[MichaelRed]

Well, back to the future (or Documentor) again. It has an option to output the results to a table. Unless your db is changing (rapidly) you can use a static table created by the documentor to use as your source of tables. fields, properties, ... types. In particular the system tables will NOT change unless the version of Ms. Access changes - but then these tables are not likely to be useful to an ASP app.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over