Solved

sys.columns equivalent in access?

Posted on 2015-01-14
6
274 Views
Last Modified: 2015-01-24
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
Comment
Question by:25112
6 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 100 total points
ID: 40550532
Try
SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

Open in new window

0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 40550552
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
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 100 total points
ID: 40550734
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 40551554
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 200 total points
ID: 40551609
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
 
LVL 5

Author Comment

by:25112
ID: 40568660
that was very educational and handy.. thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now