Can you create a 'Describe' table SQL statement in Access?

[eerich]

Is it possible to create a SQL statement that describes the attributes of a table in Access. I attempted the following that was unsuccessful:

Select Describe *
From Corporate;

Any help is appreciated.

Thanks.

 

[traingamer]

See faq700-4310 for one starting point.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Or you could try Tools->Analyze->documenter from the menu, select 'tables' and adjust the 'options' as needed.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[eerich]

Thanks traingamer, I've used the documenter function before.

I was mainly interested in whether you could create a SQL statement to pull such results as in Oracle and other databases.
I assume that the only methods available are via code module and use of the documenter; ergo, I will have to stick with these methods.

My main reason for wanting to use SQL statements is to determine if there is a way I could update the 'Caption' property via SQL for a set of tables I receive each quarter that is delivered without the properties populated.

Thanks.

 

[traingamer]

You can use queries to display the system tables as in thread701-396948 but I don't believe that you can use them to update. (I could be wrong).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Remou]

Does it have to be SQL?

 

[eerich]

Remou

If there is a method to update the table properties I would be interested in the solution. My request for SQL is because I'm most comfortable in that space. I have no knowledge of coding in the VB modules.

Is it possible to achive the table property updates through the Macro function?

 

[Remou]

You can use DAO. Some notes:

Sub SetProperty()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim strNewCap As String
Dim strName As String

    On Error GoTo Err_Property

    strNewCap = "New Caption"
    strName = "Caption"
    
    Set db = CurrentDb
    Set fld = db.TableDefs("Table1").Fields("ID")
    fld.Properties(strName) = strNewCap
    
    Debug.Print fld.Properties("Caption")
    
    On Error GoTo 0

    Exit Sub

Err_Property:

    ' Error 3270 means that the property was not found.
    If DBEngine.Errors(0).Number = 3270 Then
        ' Create property, set its value, and append it to the
        ' Properties collection.
        Set prp = fld.CreateProperty(strName, _
            dbText, strNewCap)
        fld.Properties.Append prp
        Resume Next
    End If

End Sub

Not all properties can be updated with this method in an already appended field.