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

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.

What attributes are you looking for?

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?

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.

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.

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.

.