[AccessD] Reporting field properties

Charlotte Foust cfoust at infostatsystems.com
Fri Jul 15 10:11:11 CDT 2005


The closest you come to a DAO.Field object is the ADOX.Column object.

Charlotte Foust


-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com] 
Sent: Thursday, July 14, 2005 8:25 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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



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



More information about the AccessD mailing list