[AccessD] Data Export Spec - Rev1

> are you discussing porting this out of an Access container to
> something like VB6 or VB.Net?
I just wanted to make "crystal clear" what kind of the data sources/data
source objects are planned to be used for export and transformation....

>  Or is this a "I don't like to reference DAO"
> kind of thing.
No, I like DAO :)

> If the code that does this is contained inside the class,
> what difference does the choice of DAO vs ADO make
>  - other than having to
> link to the DAO library?
IMO the problem is as far as I see (I can be wrong) that you plan to develop
tightly coupled synchronous solution. This is a problem if you work alone
but this is even a bigger problem when working in a team:

- problem to share the work between team members;
- problem of duplicated code, which may become "nigthmarish" to support;
- problem for future extensions;
- problem for scaling up....

etc.


Shamil


----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Thursday, October 20, 2005 10:46 PM
Subject: Re: [AccessD] Data Export Spec - Rev1


> >As far as I see from your code samples you use DAO. xxx - and from that
> usage one can make assumption that DAO and nothing else is planned to be
> used to work with metadata and the real data sources - is that a correct
> assumption?
>
> So Shamil, are you discussing porting this out of an Access container to
> something like VB6 or VB.Net?  Or is this a "I don't like to reference
DAO"
> kind of thing.  If the code that does this is contained inside the class,
> what difference does the choice of DAO vs ADO make - other than having to
> link to the DAO library?
>
> 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 Shamil
> Salakhetdinov
> Sent: Thursday, October 20, 2005 7:23 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Data Export Spec - Rev1
>
>
> John,
>
> Question #1: You "dive into details" from the very beginning - but it is
not
> clearly stated in your spec are your custom classes/modules to be
developed
> planned to be used with DAO only? I mean if you consciously restrict
> applicability(/reusability) of the code to be developed - that's OK but it
> have to be clearly stated in the specs' abstract to avoid any
> misunderstanding/mistreatment of the goals of your project for all
involved
> parties(/project stakeholders). As far as I see from your code samples you
> use DAO. xxx - and from that usage one can make assumption that DAO and
> nothing else is planned to be used to work with metadata and the real data
> sources - is that a correct assumption?
>
> Shamil
>
> ----- Original Message ----- 
> From: "John Colby" <jwcolby at ColbyConsulting.com>
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Sent: Thursday, October 20, 2005 6:49 AM
> Subject: [AccessD] Data Export Spec - Rev1
>
>
> > OK this is the first rev to the table specification for the data
> > export module.  Everyone is welcomed to try and figure out what we are
> > up to and comment on how you would use this and what you think would
> > be useful in order to nail down an export specification.
> >
> > usystblExport holds the top level information about an export,
> > including
> the
> > name of the export specification, whether the export needs to be fixed
> > width, whether to quote the text files, what the delimiter is for this
> > specific export of the data, and a fully pathed file spec for where to
> place
> > the data.
> >
> > usystblExport
> > EX_ID AutoNumber Export ID
> > EX_Name Text Export Name
> > EX_FixedWidth Yes/No True when Fixed Width
> > EX_QuoteText Yes/No True when quotes needed around text EX_Delimiter
> > Text The delimiter between fields EX_FileSpec Text Fully pathed file
> > name EX_HeaderLine Yes/No True when Place the field names in the first
> > line of the file
> >
> > usystblExportDataSource holds the relationship between an Export and a
> data
> > source.  This is a many to one, i.e. a given export specification can
> > only use a single data source, but a single data source can be used by
> > many different exports.
> >
> > usystblExportDataSource
> > EXDS_ID AutoNumber DataSourceExport ID
> > EXDS_IDEX Number Export ID
> > EXDS_IDDS Number Data Source ID
> >
> > usystblDataSource tells us the name of the table, query or possibly a
> > raw SQL statement.  It also gives us a code telling us how to
> > interpret the Source field.  The objective is to be able to create a
> > single recordset
> and
> > export that recordset to multiple destinations, in various formats,
> > but still ensure that the data is identical.
> >
> > usystblDataSource
> > DS_ID AutoNumber Data source ID
> > DS_Name Text Name of the data set - not required
> > DS_Source Memo Data source - can be a sql
> > statement, a query name or a table name
> > DS_Type Text Source of the data - TBL, QRY or SQL
> >
> > usystblFld gives us information about each field - the order of the
> > field
> in
> > the Big String, the source field name in the table, query or SQL
> statement,
> > the OUTPUT field in the text file, the format string used to coerce
> > the
> data
> > into the correct format, The width of the field if used in a fixed
> > width export, and the beginning position in the string for FIXED WIDTH
> > fields. The beginning position can be calculated from the position and
> > the width
> of
> > all the fields, and is mostly used as a cross check on the
> > specification. Fixed width export specifications are very specific and
> > we need to be certain that the client receiving the data tells us
> > these numbers
> correctly.
> >
> > 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_OutputFldName Text The name of the field in the output
> > file
> > FLD_Format Text Format string to use on the data
> >
> > FLD_BeginPos Number Beginning position in the Big String FLD_Width
> > Number The width if this is to be used in a fixed width export.
> >
> > In my current concept of how to implement this set of tables,
> > usystblFld would be a class (clsFld) which is responsible for
> > formatting a piece of data from a specific field.
> >
> > usystblDataSource becomes a class (clsDataSource) which is the
> > supervisor
> of
> > a collection of clsFld.  The first thing that clsDataSource does is to
> take
> > the DS_ID and use that to pull all of the records in usystblFld with
> > that value in FLD_IDDS.  This basically gathers all of the field
> > records that will be exported.  Thus a query, table or SQL statement
> > can have more
> fields
> > than make it into the export, and the fields that make it into the
> > export are determined solely by their presence in usystblFld.  As
> > clsDataSource reads the records out of usystblFld, it instantiates a
> > clsFld for each
> field
> > to be exported, and storing the class instances in Field Position
> > order (FLD_Order), sort the fields coming out of usystblFlds on
> > FLD_Order.  Thus the clsFld instances are stored in the collection in
> > their order in the output string.
> >
> > clsDataSource reads the dataset specified by the table name, query
> > name,
> or
> > SQL statement stored in DS_Name.  This data set may have N records to
> > be exported into the resulting text file. The data set is iterated
> > record by record, and each record is then fed to each clsFld in
> > consecutive order in the clsFld collection (pseudocode below):
> >
> > Dim rst as dao.recordset
> > Dim db as dao.database
> > Dim lclsFld as clsFld
> > Dim strBigStr as string
> > set db = currentdb
> > set rst = db.Open(DS_Source)
> > while not rst.eof
> >
> > 'the stuff above gets the data to be exported
> >
> > 'The stuff below formats the data using the collection of clsFld
> > instances
> >
> > for each lclsFld in colClsFld
> > str = lclsfld.Format(rst)
> > strBigStr = strBigStr & SomeDelimiter & str
> > next lclsFld
> > wend
> >
> > As you can see, each clsFld instance gets a shot at the recordset, and
> uses
> > FLD_FldName to determine which piece of the recordset it is supposed
> > to format (pseudocode below):
> >
> > Function Format(rst as dao.recordset) as string
> > Dim var as variant
> > Dim str as string
> > var = rst(FLD_FldName)
> > str = format(var,FLD_Format)
> > End function
> >
> > Thus as clsDataSource iterates the recordset of data to export, for
> > each record to be exported it passes the recordset off to each clsFld
> > instance, getting back formatted data and building up BigString.  When
> > all fields
> have
> > been formatted and appended to BigString, BigString is written to
> > EX_FileSpec.
> >
> > That is the big picture, starting to discuss implementation logic.  I
> > am going to cross post this to my forum out on my web site for the
> > purpose of creating a thread where this can be read independently of
> > AccessD chatter.
> >
> > All comments and suggestions welcomed.  We need to nail down the table
> > structure, since the information in the tables drives the construction
> > of the classes that hold the data in the usys tables, and the logic of
> > how to use this data to create an export file.
> >
> >
> > 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
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com