CODEWITHSUNDEEP
ms-accessvbams-access-2010
Quicksilver
Quicksilver

Reputation: 295

How do I programmatically retrieve the values from a linked table's property sheet?

I am working in MS Access. All the tables and views are linked to a SQL Server database. I want to write a procedure that will retrieve and store all of the formatting information about these objects. A lot of this information is available from the property sheet (I open a table in Design View, and hit F4 for the property sheet). Eg:

How do I retrieve these properties programmatically? I only see them listed for Reports.

Note that I need to retrieve the values, not just set them. I know about the SetFilter method, and that's not what I need.

Upvotes: 2

Views: 1132

Answers (2)

HansUp
HansUp

Reputation: 97101

The linked table exists as a DAO.TableDef in your database's TableDefs collection. So you can check the TableDef.Properties collection for those 5 properties.

However beware that both Filter and OrderBy are user-created instead of default properties, which means they are not included in the Properties collection unless you've assigned them values. Attempting to retrieve one which doesn't exist triggers error 3270, "Property not found". You can trap that error, respond to it as you wish, and continue on for the other properties you're interested in. Or you could first determine whether the property exists and only attempt to retrieve its value when it does exist.

This code sample uses the first approach (trap the error):

    Const cstrTable As String = "YourLinkedTableNameHere"
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strMsg As String
    Dim varProp As Variant
    Dim varProperties As Variant

On Error GoTo ErrorHandler

    varProperties = Array("Filter", "FilterOnLoad", "OrderBy", _
        "OrderByOn", "OrderByOnLoad")

    Set db = CurrentDb
    Set tdf = db.TableDefs(cstrTable)
    For Each varProp In varProperties
        Debug.Print varProp, tdf.Properties(varProp).Value
    Next

ExitHere:
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3270 ' Property not found.
        strMsg = "Property '" & varProp & "' not found."
        'MsgBox strMsg
        Debug.Print strMsg
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description & ")"
        MsgBox strMsg
        Resume ExitHere
    End Select

Upvotes: 4

John Q. Noob
John Q. Noob

Reputation: 181

How about something like this? (I've defined "table2" to have two fields, "PropertyName" and "PropertyValue"..."table1" is a placeholder for any of your existing tables)

Dim i As Integer
Dim j As Integer
Dim RS As DAO.Recordset

On Error Resume Next

Set RS = CurrentDb.OpenRecordset("select * from table2")

j = CurrentDb.TableDefs("table1").Properties.Count


For i = 0 To j - 1


RS.AddNew
RS!PropertyName = CurrentDb.TableDefs("table1").Properties(i).Name
RS!PropertyValue = Nz(CurrentDb.TableDefs("table1").Properties(i).Value, "-")
RS.Update

Next i

Upvotes: 0

Related Questions