[AccessD] OpenSchema not working as expecting with query

MartyConnelly martyconnelly at shaw.ca
Sun Mar 16 13:24:01 CST 2003


 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
>
>
>
>  
>





More information about the AccessD mailing list