[AccessD] Data Export Spec - Rev1

>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.

It isn't clear yet that this is even necessary.  When opening a DAO
recordset db.open("SomeThingHere") the SomeThingHere is handled correctly
whether it is a table, named query or SQL Statement.

>Will multiple recordsets be able to write to the same export file?

This could be done with an "append" flag in the usystblExport, i.e. if the
file already exists then just append to the end of it.  If we do that, then
we would need to treat a False in that field as "if the file already exists,
then delete it".  Then multiple Export records could specify the same file
name but different DataSource records.  Of course then you need to be VERY
careful that the data fields match up, all the delimiters etc match.

>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?

In general, a null is treated as "nothing in the string", but SOMETHING
still has to be output.  "", for a quoted comma delimited, or "     " for a
quoted, fixed length etc.  That one is not going to happen but you get the
picture.  The field in the export string has to have a fixed number of
spaces, or a delimited field in there or the whole export gets out of sync.

>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. 

This would not be trivial to do, especially getting down to the fld record
level since format strings are used.

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 Hale, Jim
Sent: Thursday, October 20, 2005 10:56 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com