[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/