Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    Formatting database output (VBA/VB)

    I'm looking for solutions, suggestions and bright ideas. I'm producing a
    relational database system that outputs various subsets as input data for
    nine different legacy systems. The "client" systems all take ASCII text
    data, but are very picky about its format - for example, a client may expect
    a record to be split over several lines of the output file in a particular
    way, with some of the data items in quotes and some not, some separated with
    commas and some not, different items to different decimal places and so on.
    Some clients expect a separate file for each "object" in the database, while
    others expect a single file with record "headers" and "footers" to separate
    the objects. In all there are 56 formats.

    For my pilot study, I've used an Access database, extracted the appropriate
    data with SQL and used VBA code to format the outputs, but it's required a
    lot of code to do nothing terribly clever and an immense amount of testing
    to check that it always does the right thing, whatever the data in the

    It seems to me that I shouldn't have to program each format separately.
    Having extracted the right data using SQL, there should be a way of
    formatting it according to a "template", predefined for each output format.
    (Even Word's MailMerge would almost do this!) Can anyone suggest an
    off-the-shelf solution for this, or a more appropriate technology than the
    one I'm using, or point me in any promising directions. (BTW I also have to
    bodge the data, for example alter numbers that are outside allowable ranges,
    but I've already written the functions to do this, and they could be called
    by the queries if necessary, rather than as part of the "formatting".)

    The solution needs to be _very_ robust and maintainable for years.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Formatting database output (VBA/VB)

    I once evaluated a product called Data Junction which now appears to have spawned a whole product line. You might find that they have template for your 56 legacy sources, or that you can leverage their tools for that purpose. Or at least you can review the product to get some ideas on how to create your own "any-to-any" conversion engine.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Formatting database output (VBA/VB)

    Have you looked at using Access export specifications. They may not be able to do everything you need to, but they may be able to do most of them so you would only have to write a few arcane formats instead of all 56.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Formatting database output (VBA/VB)

    Since this was cross-posted in the Access forum as well, I'm going to lock this thread and ask that any further replies be posted here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts