[AccessD] Re-Invent The Wheel??

I have created a system similar to that which we are about to create.  That
system was the first time I attempted to create a table driven method for
doing this and was dedicated to a specific export for a specific client.  I
think it would be useful to pull the concepts out, hash them over in public,
and write up a specification of how this can be done in a generic manner so
that it can be applied to a wide range of "delimited export" applications.

I propose a system of two tables:

usystblDataSource
DS_ID                  Autonumber   PK
DS_Source           Memo            Data source - can be a sql statement, a
query name or a table name
DS_Type              Text               Source of the data (table, query or
SQL statement
DS_FixedWidth   Yes/No           This data is a fixed width export - If
true, then the format string in each field is responsible for getting the
width right
DS_Delimiter        Text               The delimiter between fields (if any)


usystblFld
FLD_ID               AutoNumber   Field ID
FLD_IDDS          Number            Data Source ID
FLD_Order          Number            The order that the field appears in the
output string
FLD_FldName      Text                  Name of the field in the data source
FLD_Format         Text                  Format string to use on the data
FLD_BeginPos      Number            beginning position in the string

Thus usystblDataSource represents the source of the data and whether this
export specification is going to be fixed width, delimited, or both.

usystblFld represents the fields within a given export, where each field
will appear in the string, how to format the data etc.

In the system I designed previously the data was fixed width, but I wanted
to also do a comma delimited for sending to in-house users.  I quickly
discovered that the exact same data might very well be sent fixed width to
one user or set of users, and delimited to another, even delimited in
various ways.  This left me with a quandry about how to specify the
formatting for various recipients of the data, and this is something that we
will need to discuss.

In any event, what I then do is design two classes:

clsFld
A field class which holds all of the data in a single record of usystblFld.
The field class has properties for holding each data item in usystblFld and
then it has methods for accepting data and formatting the data, returning a
string of formatted data.

clsDataSrc
A supervisor class that has properties for holding each data item in a
single record in usystblDataSource, a method for instantiating all of the
clsFld instances for that data source record and storing the clsFld
instances in a collection, and a method for loading the data represented by
the DS_Source and then "exporting" the data by iterating the clsFld
collection calling each field class and getting the data back formatted, and
appending them into a string for writing to a file.

Obviously if you are going to export the same data twice (or more), perhaps
to a fixed, then to a delimited format, you need a means of specifying the
file spec for each export. 

There are many "opportunities" here, but that is the basic concept.  I am
going to post this and let anyone interested chime in with their thoughts
and perspectives.

John W. Colby
www.ColbyConsulting.com 

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