[AccessD] Data Export Spec - Rev1

Shamil,

The modules do not have to use DAO as long as ADO provides the functionality
of accessing the fields using the rst(fldname) operation.  The code you see
is pseudocode, intended to display the concept, not the actual execution,
and I can write DAO in my sleep so it is easy for me to write the pseudocode
in.  In my framework I use ADO exclusively, however I am still not anywhere
close to "as comfortable with it" as I am with DAO.

My PREFERENCE is to use ADO throughout for widest applicability.  My
PREFERENCE is also to get a good SQL Server person on board to keep us
running down a path that allows immediate, built-in usage with SQL Server.
So far I do not have a lot of hands raised saying that they want an in-depth
piece of the action.  Also, I am not even sure that SQL Server does not have
something similar built-in and therefore nobody using SQL Server would use
this.

As always happens in these projects, a lot of interest is expressed, but
that doesn't necessarily translate into active participation.  I have
learned to be prepared to do what needs to be done, on my own, to get what
-I- need done.  

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