[AccessD] Reporting field properties

I have tried the same thing with the ADODB.Field object but there is nothing
like the wealth of properties that DAO makes available, and I see nothing
like SourceTable and SourceField.

Anyone know if this is possible with ADO?

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Thursday, July 14, 2005 8:28 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Reporting field properties


As part of the reporting app I am writing for the call center, I need to do
some data validation and report fields that fail the validation.  The query
that pulls data for the reports can come from many different tables, as many
as 8 or 10.  The issue is how to report that the data in FieldName X in
TableName Y is invalid.  

I have to build a report of data that fails the validation, and issue the
report so that the data can be fixed.  For example some fields are critical
to the client, i.e. "do not send the record in the report if the data in
fields A, F, G, K, or X are missing".  The nature of this business is that
data is collected piecemeal over time but specific pieces just absolutely
have to be there before these reports (data extracts) can be sent to the
client.

I already have a system that builds classes to hold the reporting
information about the export, the record and the fields IN THE QUERY but one
thing that is tough to get (directly) is the actual table name and field
name in the source field of a query.  As a result I designed a little class
to accept a field object from DAO and grab specific properties from that
field object.  The specifics looks something like:

In the class header:

Private mstrForeignName As String
Private mstrName As String
Private mvarDefaultValue As Variant
Private mblnAllowZeroLength As Boolean
Private mblnDataUpdatable As Boolean
Private mvarOriginalValue As Variant
Private mblnRequired As Boolean
Private mlngSize As Long
Private mstrSourceField As String
Private mstrSourceTable As String
Private mintType As Integer
Private mstrValidationRule As String
Private mstrValidationText As String
Private mvarValue As Variant
Private mvarVisibleValue As Variant

And the class method that accepts a field object and pulls these properties
out:

Public Function mReadClassProperties(lFld As DAO.Field)
On Error GoTo Err_mReadClassProperties
    On Error Resume Next
    With lFld
        mblnAllowZeroLength = .AllowZeroLength
        mblnDataUpdatable = .DataUpdatable
        mvarDefaultValue = .DefaultValue
        mstrForeignName = .ForeignName
        mstrName = .Name
        mvarOriginalValue = .OriginalValue
        mblnRequired = .Required
        mlngSize = .Size
        mstrSourceField = .SourceField
        mstrSourceTable = .SourceTable
        mintType = .Type
        mstrValidationRule = .ValidationRule
        mstrValidationText = .ValidationText
        mvarValue = .Value
        mvarVisibleValue = .VisibleValue
    End With
Exit_mReadClassProperties:
Exit Function
Err_mReadClassProperties:
        MsgBox Err.Description, , "Error in Function
clsDAOFld.mReadClassProperties"
        Resume Exit_mReadClassProperties
    Resume 0    '.FOR TROUBLESHOOTING
End Function

And of course methods to read the property values:

Public Property Get pForeignName() As String
    pForeignName = mstrForeignName
End Property
Public Property Get pName() As String
    pName = mstrName
End Property
Public Property Get pDefaultValue() As Variant
    pDefaultValue = mvarDefaultValue
End Property

Etc etc.


The reason that I store these field properties in my own class is that I
need to gather the data about each field in the big query one time, as the
recordset is opened, rather than each time a record is read (for speed).  I
am currently really only interested in the static stuff like SourceField and
SourceTable but for completeness just added the rest.

Notice that some of the field properties are dynamic and some are static,
i.e. some are about data and others are about the format of the data.  I
could (and probably will eventually) break this down into two methods, one
that grabs the static properties such as AllowZeroLength, DefaultValue,
SourceField, stuff like that ONE TIME, and another method that grabs the
dynamic data every time the record changes - Value and Original Value, stuff
like that.

At any rate, I now have a class that I can open a recordset from a query,
iterate through the fields collection instantiating one of these classes for
each field, then save the properties at the instant the recordset opens.  I
then have any static properties - specifically of interest right now is
SourceField and SourceTable - to use whenever I need to report or log a
field as being empty.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com