[AccessD] OpenSchema not working as expecting with query

I didn't either until recently. :)

If you set the property at the query-level, it'll override the table-level
property in new objects.

Thanks for the ADOX code!

Susan H.

----- Original Message -----
From: "MartyConnelly" <martyconnelly at shaw.ca>
To: <accessd at databaseadvisors.com>
Sent: Sunday, March 16, 2003 2:23 PM
Subject: Re: [AccessD] OpenSchema not working as expecting with query


>
>  I didn't know you could set a Queries field description property?
>
> Here is a different method using ADO 2.7  ADOX Catalog
> ?GetFieldDesc_ADO("Customers","ContactName")
>  No description returns a null
>
> ?GetFieldDesc_ADO("Customers","Phone")
>  returns a description
>
> Function GetFieldDesc_ADO(ByVal MyTableName As String, _
>   ByVal MyFieldName As String)
>
>    Dim MyDB As New ADOX.Catalog
>    Dim MyTable As ADOX.Table
>    Dim MyField As ADOX.Column
>    Dim oConnection As ADODB.Connection
>    Dim sConnStr As String
>     On Error GoTo Err_GetFieldDescription
>     'Access 2000
>       'MyDB.ActiveConnection = CurrentProject.Connection
>     'access 97 method
>       'Access XP Jet 4 version of northwind
>         sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>            "Data Source=C:\Program Files\Microsoft Office
> XP\Office10\Samples\Northwind.mdb;" & _
>            "User Id=admin;" & "Password="
>
>    ' Create and Open the Connection object.
>       Set oConnection = New ADODB.Connection
>       oConnection.CursorLocation = adUseClient
>       oConnection.Open sConnStr
>
>       MyDB.ActiveConnection = oConnection
>    ' end access 97
>      Set MyTable = MyDB.Tables(MyTableName)
>
>    GetFieldDesc_ADO =
MyTable.Columns(MyFieldName).Properties("Description")
>
>    Set MyDB = Nothing
>
> Bye_GetFieldDescription:
>    Exit Function
>
> Err_GetFieldDescription:
>    Beep
>    MsgBox Err.Description, vbExclamation
>    GetFieldDesc_ADO = Null
>    Resume Bye_GetFieldDescription
>
> End Function
>
> Susan Harkins wrote:
>
> >I'm using the following procedure to return the Description property from
a
> >table. I tried to run it using a query, but it doesn't work --
Description
> >property returns Null, which is interesting -- not an error, but Null,
even
> >when there's a string. Anybody know more about this? I'm guessing
OpenSchema
> >doesn't return a query's Description property, but then, I would expect
the
> >rst!Description reference to return an error, but it doesn't. Even with
On
> >Error commented out, it still returns Null.
> >
> >Susan H.
> >
> >
> >
> >Public Function ReturnDescription(fldname As String) As String
> >Dim rst As ADODB.Recordset
> >Dim fld As ADODB.Field
> >Set rst = CurrentProject.Connection. _
> >    OpenSchema(adSchemaColumns, _
> >    Array(Empty, Empty, "Customers"))
> >        Do Until rst.EOF
> >          For Each fld In rst.Fields
> >           On Error Resume Next
> >            If rst!COLUMN_NAME = fldname Then
> >              ReturnDescription = Nz( _
> >                rst!Description, "")
> >            End If
> >          Next fld
> >          rst.MoveNext
> >        Loop
> >Set rst = Nothing
> >Set fld = Nothing
> >End Function
> >
> >
> >
> >
> >
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>