Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Access System Tables

Status
Not open for further replies.

mickers

Programmer
Dec 21, 2005
7
US
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.
 
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]
 
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.
 
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
 
You'll need to use TableDefs, Fields and Properties to get this stuff since you're in Access '97. Just a skeleton
Code:
    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]
 
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:

Code:
<%
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top