Information on a table

[dubnet]

is there an sql query to retrieve the attributes of a table?

 

[The_Doc_Man]

All such queries would represent implementation-dependent features. I think the ANSI standard says the answer is NO.

In Access, the answer is NO because SQL queries tables but you want to query a TableDef, which is NOT a table in Access.

Now, there might still be a way - but you would have to write some module code to do it. If you wrote a function that could be given a parameter and return a table property, and if the function was declared public, you could use it in a query.

Unless you are using this for documentation, I see no need for such a query. You cannot do much with the table's definitional data in a query context. If you could get the data into a table or query, you still would not be able to do much with it. On the other hand, direct lookups in a module can make the data available in a context where you could actually use it.

 

[Abby N]

What attributes are you looking for?

 

[dubnet]

I do have a practicle use for this situation, that is that if an administrator is given rights to add attributes to a table then, the user view should automatically reflect this. for example if a front end has been written that contains a search option, then the return-query can be used to create the relevant search fields. I am using ColdFusion as the middleware and Flash the front-end. Is there any way cf can help?

 

[Pat Hartman]

Interestingly enough The_Doc_Man, Access used to conform to the ANSI standard which is to store all table definition information in tables. With version A95, Microsoft moved away from the ANSI standard and changed its method of storing this information by eliminating the MSys tables it used in A2.0. Now, you need to write code to read various collections to put this information together.

I have a data dictionary database that I built to use to document a varity of back end databases including non-Access databases. The following two subs are the heart of the database. The db contains a form that takes as input, the name of the database to analyze and then runs the posted code.

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 Database
    'strDatabase = "C:\hartman\LinkDB2.mdb"
    strDatabase = Forms!frmPrintDoc!txtDBName
    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.
        For Each fldLoop In tblLoop.Fields         
            If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 1) = "z" Or Left(tblLoop.Name, 1) = "~" Then
            Else
                TempSet1.AddNew
                If Left(tblLoop.Name, 7) = "MOBDEV_" Then
                    TempSet1!TableName = Mid(tblLoop.Name, 8)
                Else
                    If Left(tblLoop.Name, 4) = "dbo_" Then
                        TempSet1!TableName = Mid(tblLoop.Name, 5)
                    Else
                        TempSet1!TableName = tblLoop.Name
                    End If
                End If
                'Debug.Print tblLoop.Name & "-" & fldLoop.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)
                If fldLoop.Attributes And dbAutoIncrField Then  'performs bitwise operation
                    TempSet1!AutoNum = True
                    TempSet1!Required = True
                Else
                    TempSet1!AutoNum = False
                End If
                TempSet1.Update
            End If
        Next fldLoop
    Next tblLoop
    db.Close 
End Sub
Private Function GetType(lType As Long) As String
'* Returns description of lType
  Select Case lType
    Case dbBigInt: GetType = "BigInt"           '16
    Case dbBinary: GetType = "Binary"           '9
    Case dbBoolean: GetType = "Boolean"         '1
    Case dbByte: GetType = "Byte"               '2
    Case dbChar: GetType = "Char"               '18
    Case dbCurrency: GetType = "Currency"       '5
    Case dbDate: GetType = "Date"               '8
    Case dbDecimal: GetType = "Decimal"         '20
    Case dbDouble: GetType = "Double"           '7
    Case dbFloat: GetType = "Float"             '21
    Case dbGUID: GetType = "GUID"               '15
    Case dbInteger: GetType = "Integer"         '3
    Case dbLong: GetType = "Long"               '4
    Case dbLongBinary: GetType = "LongBinary"   '11
    Case dbMemo: GetType = "Memo"               '12
    Case dbNumeric: GetType = "Numeric"         '19
    Case dbSingle: GetType = "Single"           '6
    Case dbText: GetType = "Text"               '10
    Case dbTime: GetType = "Time"               '22
    Case dbTimeStamp: GetType = "TimeStamp"     '23
    Case dbVarBinary: GetType = "VarBinary"     '17
    Case Else: GetType = "Undefined - " & lType
  End Select
End Function

I didn't post the table layout or the SQL for the two queries because you can figure all that out from the code.

 

[dubnet]

wow!...that is a bit over my head. Could you relate it to a specific example...say...there is a table called myTBL which holds userID and userName. How can I query this table to retrieve the attributes (userID and userName), considering they can be added or removed by another user.

 

[Pat Hartman]

The code that I posted reads through the tables collection of a specified database and for each table in the collection, it reads the fields collection and places the values of the field attributes into a table.

The line:
TempSet1!TableName = tblLoop.Name
stores the table name and the line:
TempSet1!FieldName = fldLoop.Name
stores the column name.

You have me totaly confused as to your environment. You are using Flash and ColdFusion. Where does Access fit into this application? If you are not using Access tables and I would hope you are not, any answer you get from this forum will be of little help to you. As I said in the preamble to my post, Access deviates from the ANSI spec in how it stores information related to tables. If you are using SQL server or something else to hold your data, there will be an SQL solution to your question but NOT if you are using Access. And to get to the SQL solution you would need to tell us which RDBMS you are using to store data.

 

[dubnet]

.