How to query table properties?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The Access Documenter provides a rather lengthy list if you document a
table. Can anyone provide help in querying only selected properties of a
table, such as:
Field.Name
Field.Type
Field.Size
Field.Caption
Field.Description

This query would be used to write a custom properties report.

Thanks for your help,
Bill
 
Access Documenter???
I havent used that one before but is you are
after for the Field properties, i guess this code
will do the job.



Function GetTableProperties()
Dim db As DAO.Database
Dim tbf As TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim i, j As Integer
Dim ExcelApp As Object
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
ExcelApp.Workbooks.Add



Set db = CurrentDb

On Error Resume Next

For Each tbf In db.TableDefs
'For Physical table Only

If tbf.Attributes <> 537001984 Then

ReDim indexname(tbf.Indexes.Count)
j = 0
For Each idx In tbf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
indexname(j) = fld.Name
j = j + 1
Next
End If
Next

ExcelApp.Worksheets.Add
ExcelApp.ActiveSheet.Name = tbf.Name
ExcelApp.Cells(1, 2).Value = "FIELD COMPOSITION LIST"
ExcelApp.Cells(2, 2).Value = "TABLE NAME:"
ExcelApp.Cells(2, 3).Value = tbf.Name

i = 5
ExcelApp.Cells(4, 1).Value = "No"
ExcelApp.Cells(4, 2).Value = "Japanese"
ExcelApp.Cells(4, 3).Value = "English"
ExcelApp.Cells(4, 4).Value = "Key"
ExcelApp.Cells(4, 5).Value = "Field Name"
ExcelApp.Cells(4, 6).Value = "Data Type"
ExcelApp.Cells(4, 7).Value = "Size"
For Each fld In tbf.Fields

For j = 0 To UBound(indexname)
If indexname(j) = fld.Name Then ExcelApp.Cells(i,
4).Value = "Primary Key"
Next j

ExcelApp.Cells(i, 1).Value = i - 4
ExcelApp.Cells(i, 2).Value = fld.Name
Select Case (fld.Type)
Case 1
ExcelApp.Cells(i, 6).Value = "YES/NO"
ExcelApp.Cells(i, 7).Value = "Binary"
Case 2
ExcelApp.Cells(i, 6).Value = "NUMBER"
ExcelApp.Cells(i, 7).Value = "Byte"
Case 3
ExcelApp.Cells(i, 6).Value = "NUMBER"
ExcelApp.Cells(i, 7).Value = "Integer"
Case 4
If fld.Attributes = 1 Then ExcelApp.Cells(i,
6).Value = "NUMBER"
If fld.Attributes = 17 Then ExcelApp.Cells(i,
6).Value = "AUTONUMBER"
ExcelApp.Cells(i, 7).Value = "Long Integer"
Case 5
ExcelApp.Cells(i, 6).Value = "CURRENCY"
ExcelApp.Cells(i, 7).Value = "Currency"
Case 6
ExcelApp.Cells(i, 6).Value = "NUMBER"
ExcelApp.Cells(i, 7).Value = "Single"
Case 7
ExcelApp.Cells(i, 6).Value = "NUMBER"
ExcelApp.Cells(i, 7).Value = "Double"
Case 8
ExcelApp.Cells(i, 6).Value = "DATE/TIME"
ExcelApp.Cells(i, 7).Value = "Date time"
Case 10
ExcelApp.Cells(i, 6).Value = "TEXT"
ExcelApp.Cells(i, 7).Value = fld.Size
Case 11
ExcelApp.Cells(i, 6).Value = "OLE OBJECT"
ExcelApp.Cells(i, 7).Value = "Ole Object"
Case 12
If fld.Attributes = 2 Then ExcelApp.Cells(i,
6).Value = "MEMO"
If fld.Attributes = 32770 Then ExcelApp.Cells(i,
6).Value = "HYPERLINK"
If fld.Attributes = 2 Then ExcelApp.Cells(i,
7).Value = "Memo"
If fld.Attributes = 32770 Then ExcelApp.Cells(i,
7).Value = "Hyperlink"
Case 15
ExcelApp.Cells(i, 6).Value = "NUMBER"
ExcelApp.Cells(i, 7).Value = "Replication ID"
Case 20
ExcelApp.Cells(i, 6).Value = "NUMBER"
ExcelApp.Cells(i, 7).Value = "Decimal"
End Select
i = i + 1
Next
ExcelApp.Columns("A:G").AutoFit
End If
Next

End Function

HTH

Arthur Cruz
 
Thanks for your reply. I will try your code. For the "Access Documenter",
click on Tools->Analyze-> Documenter. Select the object to document and the
options you desire.
 
Back
Top