Query Access System Tables

[mickers]

I need to access the following properties of an Access 97 table field by specifying the table name and field name:
- required
- data type
- field size
- default value

I can upsize to another version of Access if I have to, but I'd prefer not.

Is there a way to query the system tables to get these values? If so, can you provide the query? I have searched for hours and can't find this query or a good lead.

Thanks for the help.

 

[dhookom]

You can't query field information from system tables. You can use the Documenter to report all of this. In Access 97 I think it's in the Tools->Analyze menu.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mickers]

That's very unfortunate.

I need to get these properties in a custom web application so the Documentor won't help in this case.

I originally wrote this application for SQL Server and the application depends on it, but now I need the same for Access databases.

Is there any another way to get them?

Thanks.

 

[PHV]

Try to play with the ADOX library (Catalog object)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

You'll need to use TableDefs, Fields and Properties to get this stuff since you're in Access '97. Just a skeleton

    Dim tbl                         As DAO.TableDef
    Dim fld                         As DAO.Field

    Set tbl = CurrentDB.TableDefs("TableName")
    On Error Resume Next

    For Each fld In tbl.Fields
        Debug.Print "Name =          " & fld.Name
        Debug.Print "Required =      " & fld.Properties("Required").Value
        Debug.Print "Type =          " & fld.Type
        Debug.Print "Size =          " & fld.Size
        Debug.Print "Default =       " & fld.Properties("Default").Value
    Next

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[dhookom]

Since you want to do this in a "custom web application" you should probably post this question in a forum for your "custom web application". If you are using ASP, you can get at these properties with code like:

<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _ 
           "Dbq=c:\INETPUB\[URL unfurl="true"]wwwroot\yourfolder\database\Access.mdb;"[/URL] & _
           "Uid=admin;" & _
           "Pwd="
%>
<%
SQL_query = "SELECT * FROM tblYourTable"
Set RS = MyConn.Execute(SQL_query)
For each Fld in rs.fields
Response.Write("Field Name: " & fld.Name & "   Type:" & fld.Type  & "   Size:" & fld.DefinedSize  & "<br>" )
Next
%>

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]