[AccessD] Data Export Spec - Rev1

<<<
LOL, I NEVER want to be all those backwards things
 that you portray me to be
>>>
I think I should have added that in solution I envision, young
Indian,Pakistnian,... guys and/or American, Canadian, West European...
students will gain also because they can have some work delegated by you,
e.g. the work to develop specific data formatters etc. - you can keep them
busy with this simple work and as far as they get more experienced you will
delegate them more and more complicated work and therefore you and your
customers will be ready to pay them more... Everybody's "win" approach.
Yes, I know, it sounds too optimistic...

<<<
so join the team and show me (all of us) how we accomplish this?
>>>
I think I joined it, didn't I? :)

<<<
Specifically for EXPORTING data
> to these monsters of computing.
>>>
OK, let's make it clear - for EXPORTING and nothing else?
And that the data source is something like DAO, ADO,.... recordsets and the
target is delimited or fixed width text file.

As you I did work many times on such tasks here and in the West Europe.
Until last year I didn't program them more or less generic way - the
customers didn't ask for that and my solutions were hardcoded. Last year it
was a requirement to make it metadata based. Now it's your idea to make it
even more generic. OK. Let's make it more generic and let's also get new
experience and some fun while designing/coding it and supporing it in the
future?

> My intention is to:
OK. They are clear and good enough specified for now.

> so please take a few
> minutes to expand what you have in mind,
It will take more than a few minutes for sure.
And I have problems to find free time now.

> 1) Given what I am attempting to accomplish how do you create the module
> that you swap out.
> 2) What piece(s) of this puzzle is modified when I swap out this module
for
> another.
> 3) How do the modules interface together.
> 4) How is this accomplished in VBA?
These all positions above can be accomplished using custom classes,
Implements feature and several software design patterns like the ones
described here - http://home.earthlink.net/~huston2/dp/patterns.html
(Classical book on software design patterns is this one:
http://www.amazon.com/gp/product/0201633612/102-0631645-6216162?v=glance&n=283155)

The risk factor for VBA is that the more interfaces to implement you
introduce/use in Implements the higher the probability it (VBA) will start
breaking unexpectedly. It was so for me - when I used several interface
custom classes it worked OK. When I introduced more - I was forced by VBA
engine to give up.(MS Excel, MS Access) For this solution there should be a
few interface custom classes - so it should work well I hope in VBA, If it
will not then the solution should be to upsize to VB.NET and create COM
Callable Wrapper(relatively easy task) (The problem with Implements in VBA
exists in Office 2003 too - I can't give real examples because the problems
is floating - and I guess MS will never convince it exists - so there is no
sense to spend time to create reproducible description of this bug/feature.)

> 5) Give me a specific example of the kind of export / format that you are
> running into which isn't fixed/delimited/formatted and how modularizing
this
> program would allow you to handle this kind of export.
John, where did I write that my solution pretends to be that far generic?
I think I didn't write that.
Yes, I did write that I did program an export data solution for the banks
here, which used header (and footer) additionally to the fixed
length/delimited export records. Do you mean this my note?

My main points were:

- use pluggable custom classes additionally to your generic formatter based
on Format$ function and metadefinitions;
- make solution ready to process large amounts of source data without
stopping all the other work on the PC running export code;
- make solution working in asynchronous mode;
- make solution working with different source data sources.

That's all I think.
Did I promise more? (Hopefully not :) )

> and without ever seeing your problems, I
> can't comprehend how to respond to them.
OK, John.
Let's make it this way - you develop your solution your way.
Then I will try to show what (potential) problems it has - and it may happen
that what you call my problems becomes yours :)

It should be the best approach for this small project because my solution
has a risk to not run well on VBA because of my plan to use Implements
feature, which is buggy on VBA as my experience shows.

If you(we) start with your solution it should be also like it's getting done
in popular now eXtreme Programming approach when a quick satisfactory
solution is developed, which makes customers happy and busy with using
it/playing with it but then this solution is refactored to make its
architecture more flexible.

Your small task is also interesting to develop using Test Driven Development
(http://www.objectmentor.com/writeUps/TestDrivenDevelopment), but VBA
doesn't have unit testing framework and to write one is senseless for
this(VBA) dying development environment. So TDD should be left for other
VB.NET/C# based projects...

Recap:
=====
1. My proposal is that you write your solution your planned way(as far as I
see it's good traditional solution good enough specified now) to have 0%
risk of fail (Risks are: a Technical problems with VBA & Implements b. My
inactive participation in this project because of my need to work for my
customers currently - urgent release due end of the next week "blood from
nose".)

2. When your solution ready I will try to find and show its "weak points"
and when we agree on them/some of them then we will refactor your solution
together in several steps, with every step getting running and more and more
flexible, scalable and asynchronous solution.

3. You proceed with your team with or without me because I will again have
to hardwork for my customers...

If I have been free from my customer's work these days we could have done it
different way with more advanced architecture designed from the very
beginning but my approach has relatively high risk of failure when
implemented on VBA because of VBA enigine bugs...

Currently I see nine major "players" of my solution:

- 1. DataExportService
- 2. DataFeeder
- 3. DataCruncher (DataProcessor)
- 4. (Export)FileDefinition
- 5. (Export)FileSection
- 6. (Export)Record
- 7. ((Export)Record)Field
- 8. (Data)Formatter
- 9. (Export)FileWriter

These should be loosely coupled, highly cohesive custom classes.
There could be more of them and several Interfaces including events.
The ones above can be changed/renamed. Some may go out probably.
It will be more custom classes than in your solution.
It will be more coding for sure than in your solution.
But the coding should be more streamlined with less code lines in custom
classes methods/properties.
This more coding will make sense only if it will be shown that it brings
real gains in long run.
Therefore IMO this is pragmatic approach.
No "air castles", no "miracles", no "universal solutions" - I base my work
on my own experience and on the concepts of the books like "Object Thinking"
http://www.bookpool.com/sm/0735619654 published by MS Press - who can blaim
MS as being non pragmatic? (One can blaim me that I misinterpret the books
like this one referred above and published by MS - feel free to say that if
you think so..)

Shamil

----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Friday, October 21, 2005 5:56 PM
Subject: Re: [AccessD] Data Export Spec - Rev1


> Shamil,
>
> LOL, I NEVER want to be all those backwards things that you portray me to
> be, so join the team and show me (all of us) how we accomplish this?
>
> What I am discussing is getting data out of a row of a record, into a
> delimited string of text, with any format you desire (or which format$()
can
> handle anyway).  Each field can have its own format string stored right in
> the field definition, so you can specify the output format of the field.
> For example, if a field needs to be currency, placing the format$
> "$###,###.00" gives an output in the text string of $23,423.00, etc etc.
> Every fld record has a place to store a format string like this, so each
> field can be formatted as precisely as format strings allows you to format
> that field.
>
> Let's back up a minute.
>
> The objective is to create a system for creating fixed width / delimited
> data files for exchanging data between our puny little micro computers and
> those monsters of calculation mainframes.  Specifically for EXPORTING data
> to these monsters of computing.
>
> The "typical" way that developers export data is:
>
> 1) Use the export wizard, which works well IF the format is well
understood.
> Call up the wizard, point to a query, fill in the blanks, use the export
> specification with the OutputTo vba function.  This is fine, except that
if
> anything in the format needs to change, you have to step back through the
> wizard, selecting the field, and then find and fix the format.  And of
> course, if you need any formatting not provided by the wizard you are
stuck.
> The wizard is pretty cool, I use it, but I always find it a bit of a PITA.
>
> I also have one HUGE issue with this, which is that the OutputTo
> occasionally errors out for reasons completely unknown to me.  Come back
> through and execute again and it works the second time.  Reliability
sucks.
> The exports that I do occur in the middle of the night because my client's
> client needs them on their desk in the morning.  Every few weeks I come in
> to find it not happening.  I even tried just looping back to do it again
> until it works and for whatever reason that doesn't work.  So... Output to
> is a fine solution as long as you don't need reliability.
>
> 2) Write a custom output module that takes a record, pulls each field, and
> outputs it however it needs to be seen in the file, hard coding the
quotes,
> if any, the delimit property, the fixed width etc.  This is Gustav's
> solution, and if your spec is carved in stone, and you don't need to do
lots
> of them, this method also works just fine.
>
> In either case, what often happens is that any necessary FORMATTING is
often
> accomplished in the query that pulls the data.  IOW, I pull a currency
> field, alias it, and right in the query use a format$() to get it in the
> desired format.  This certainly works, but the issue is that if you want
to
> find out (and change) how it is formatted, you have to go find the
specific
> query that has the format$() statement in the field alias and change the
> format there.
>
> My intention is to:
>
> Move the formatting out of the queries entirely.
> Allow the query just selects data to export, nothing more.
> Allow the system to export data using the same query (so that IDENTICAL
data
> is sent) into multiple files with varying DELIMITING format requirements.
> Place the formatting (if any) in a table.
> Make the formatting field specific, i.e. have a place to specify that for
> THIS field, I want THIS format.
> Place the other "delimiting information" such as "fixed width" or "comma
> delimited", or "Tab delimited" in a table, easy to see, easy to edit.
> Place the other "export name", "file name" etc. in a table, easy to see,
> easy to edit.
> Break the process into pieces - specify the data source, specify the field
> format, specify the delimiter format in the exported line of code.
>
> All of this allows me to build a specific kind of output, commonly known
as
> fixed "width / delimited".  This is not a report generator, it is used
> (typically) to exchange data between computers.  The receiving computer
MUST
> HAVE FieldX at PositionY, formatted EXACTLY Z.  Getting the client to
change
> how their program accepts the data is not an option.
>
> I have run into this a dozen times or more, and I see this same request
come
> by the list several times a year so others are seeing it as well.  Every
> time I have this requirement, these things are what I needed to format,
> never anything else, which is why I have "blinders on" I suppose.  I admit
> to having a specific purpose in mind.
>
> You obviously have something more far ranging in mind, so please take a
few
> minutes to expand what you have in mind, and how we can accomplish this.
> Everything you have said so far is so generalized that I cannot even
> understand what you have in mind.  I understand "plug-in modules".  My
> questions are:
>
> 1) Given what I am attempting to accomplish how do you create the module
> that you swap out.
> 2) What piece(s) of this puzzle is modified when I swap out this module
for
> another.
> 3) How do the modules interface together.
> 4) How is this accomplished in VBA?
> 5) Give me a specific example of the kind of export / format that you are
> running into which isn't fixed/delimited/formatted and how modularizing
this
> program would allow you to handle this kind of export.
>
>
>
> It sounds like my blinders are crippling me but we all think about
handling
> the problems that we run into.  It appears that you are running into
> problems that I am not seeing, and without ever seeing your problems, I
> can't comprehend how to respond to them.
>
> John W. Colby
> www.ColbyConsulting.com
>

<<< tail trimmed>>>