[AccessD] Data Export Spec - Rev1

Good stuff. A couple of thoughts. Is it possible  to automatically determine
DS_Type? ie  if the named query/table is in the system files we can assume
it is a query/table, if not it is a raw sql string. Will multiple recordsets
be able to write to the same export file? The alternative I guess is to use
a union query as the record source. Should there be a boolean in
usystblexport that signifies it is okay to kill existing copies of the named
export file without asking? Does there need to be a method for handling
nulls (or for that matter any illegal characters) in the recordset so that
they are not written to the export file? What would really be interesting is
a front end parser wizard that could take an example file   and fill in
whatever it could in usystblexport and usystablefield. It would be very
useful but is not trivial to do. 
Jim Hale

-----Original Message-----
From: John Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Wednesday, October 19, 2005 9:49 PM
To: 'Access Developers discussion and problem solving'
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.


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.