[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

sys.columns equivalent in access?

in tsql you can query the system view sys.columns to see all columns of a database... are there such helps in MSAccess (system views etc)

thanks.
0
25112
Asked:
25112
5 Solutions
 
HuaMinChenBusiness AnalystCommented:
Try
SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

Open in new window

0
 
PatHartmanCommented:
Older versions of Access (Prior to A2K) exposed system tables that showed the columns in a table but not anymore.  You need to loop through the fields collection of the tabledefs collection to find the columns and their attributes.  You can get the columns from a query in MSysQueries.

You can find a list of tables in MSysObjects but not fields.
Local Access Tables - Type =1
ODBC Tables - Type = 4
Linked Access Tables - Type = 6
0
 
pcelbaCommented:
You can open the table as a recordset and list the field names:
Sub ShowColumnProperties(TableName As String)
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

Set rs = CurrentProject.Connection.OpenSchema _
(adSchemaColumns, Array(Empty, Empty, TableName))

' For Each fld In rs.Fields
' Debug.Print fld.Name
' Next

With rs
Do While Not .EOF
Debug.Print !COLUMN_NAME
Debug.Print " "; !DATA_TYPE
Debug.Print " "; !CHARACTER_MAXIMUM_LENGTH
Debug.Print " "; !Description
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing
End Sub

Open in new window

To create your own data dictionary should be easy.

You may also try another code:
dim db as database, td as tabledef, fld as field
set db=currentdb()
for each td in db.tabledefs
for each fld in td.fields
debug.print td.name, fld.name, fld.type
next
next
set db=nothing

Open in new window

Another option is to open the Access database via ODBC from another product (e.g. FoxPro) and use appropriate ODBC call to obtain the table structures.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jeffrey CoachmanCommented:
Another option, ...
...Not sure what the exact output should be, ...but Access includes a "Documenter" feature that will display almost anything you would need to know about the columns Properties: (Datatype, length, Required, Format, Indexed, ...etc)
Database Tools-->Documenter
The output can be sent to, (saved as,) Excel, txt, PDF, Word, HTML, ...or even emailed

It will also document other objects as well. (Forms, Reports, Code Modules, ...etc)
Finally, If you make the system tables visible (Access Options-->Navigation Options->Show system Objects)
...it will document the attributes of those tables as well.

;-)

JeffCoachman
0
 
PatHartmanCommented:
I didn't have time to post it earlier but here's the code I use in my own documenter.  This section builds the Fields table.  To use it, you need to create a table that matches the fields I am putting data into.  The code gets the database name to document from a form field.  You can hard code it if you like or make a form to run the code.  The code is intended to run in dbA and document a different database.  It doesn't run in the database it is documenting.  With the exception of the form field references and creating an empty tblTableFields to correspond to the fields you want to capture, and creating the named delete query , you can use the code as is.
Sub Create_tblTableFields()

    Dim db As DAO.Database
    Dim tblLoop As DAO.TableDef
    Dim fldLoop As DAO.Field
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TempSet1 As DAO.Recordset
    Dim strDatabase As String
    Dim ThisDB As DAO.Database
    Dim CountTables As Integer
    
   On Error GoTo Create_tblTableFields_Error

  On Error GoTo Err_Create_tblTableFields
    'strDatabase = "C:\hartman\LinkDB2.mdb"
    strDatabase = Forms!frmPrintDoc!txtDBName
    
    CountTables = 0
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    
    db.Containers.Refresh
    
    Set QD1 = ThisDB.QueryDefs!QdeltblTableFields
        QD1.Execute
    Set TD1 = ThisDB.TableDefs!tblTableFields
    Set TempSet1 = TD1.OpenRecordset

    ' Loop through TableDefs collection.
    For Each tblLoop In db.TableDefs
        ' Enumerate Fields collection of each
        ' TableDef object.
        CountTables = CountTables + 1
        Forms!frmPrintDoc!txtTableCount = CountTables
        Forms!frmPrintDoc!txtTableName = tblLoop.Name
        Forms!frmPrintDoc.Repaint
                
        If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 2) = "xx" Or Left(tblLoop.Name, 2) = "zz" Or Left(tblLoop.Name, 1) = "~" Then
        Else
            For Each fldLoop In tblLoop.Fields
                TempSet1.AddNew
                TempSet1!TableName = tblLoop.Name
                TempSet1!FieldName = fldLoop.Name
                TempSet1!OrdinalPosition = fldLoop.OrdinalPosition
                TempSet1!AllowZeroLength = fldLoop.AllowZeroLength
                TempSet1!DefaultValue = fldLoop.DefaultValue
                TempSet1!Size = fldLoop.Size
                TempSet1!Required = fldLoop.Required
                TempSet1!Type = fldLoop.Type
                TempSet1!ValidationRule = fldLoop.ValidationRule
                TempSet1!Attributes = fldLoop.Attributes
                On Error Resume Next ' the following property is only available when it is not null
                TempSet1!Description = fldLoop.Properties("Description")
                TempSet1!FieldType = GetType(fldLoop.Type)
                TempSet1!Caption = fldLoop.Properties("Caption")
                If fldLoop.Attributes And dbAutoIncrField Then  'performs bitwise operation
                    TempSet1!AutoNum = True
                    TempSet1!Required = True
                Else
                    TempSet1!AutoNum = False
                End If
                TempSet1.Update
            Next fldLoop
        End If
    Next tblLoop

Exit_Create_tblTableFields:
    db.Close
    Exit Sub

Err_Create_tblTableFields:
    Select Case Err.Number
        Case 3043
            MsgBox "Please select a valid database", vbOKOnly
        Case 91   ' db was not opened so it cannot be closed.
            Exit Sub
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_Create_tblTableFields

   On Error GoTo 0
   Exit Sub

Create_tblTableFields_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Create_tblTableFields of Module DocumentCollections"
End Sub

Open in new window

0
 
25112Author Commented:
that was very educational and handy.. thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now