[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