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

    Formatting text output (97)

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

    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
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting text output (97)

    <P ID="edit" class=small>(Edited by carbonnb on 29-Nov-02 13:56. To reorder a couple of paragrahps, so it actually makes sense.)</P>I don't know of anything like this.

    But if you were up for a challenge, you could roll your own.

    This is just a conceptual idea, but it could go something like this:

    You would have a way to write an export template, something like:

    "[field1}{tab}{quote}{field2}{quote}{NewLine}{tab}{t ab}{field3}"

    This would your template. You pass that, the recordset and the output file to a function that is a series of replace commands.

    Replace strTemplate, "{Quote}", """"

    This would replace the {quote} tag with a quote mark.

    You would also have to loop through all the fields in the recordset to, as well as all the records in the recordset

    Like I said, this is just conceptual. None of it is actually tested.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting text output (97)

    I have been thinking about this a bit more and I would probably make it a class module, the function to export, that is.

    That way you can set the parameters at various stages(i.e. the export file name, a file header, a file footer, a record header a record footer, the recordset...) and then once all the pieces are in place, you write out the text file.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formatting text output (97)

    This question was cross-posted to the VB/VBA forum here
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Formatting text output (97)

    I'm not completely sure that I was following your request, but.....
    We have a tool that we created in Access for collecting Application Requirements. The "requirements are then exported to a WORD document. But to get from Access to Word and have the final document with appropriate formatting, we had to:

    Run an ACCESS query that outputs information in a specific order (determined by our final output needs). We placed ~ marks to break up the fields. The final output was saved as a .txt file. The file output looked something like this:

    2~Functional
    3~COMMON STUDY REQUIREMENTS
    4~Log In
    5~The system must allow the user to log in to CDAS. $$ID=8$$
    6~The system must allow the user to enter a username and password. $$ID=10$$

    The first outline number told me what outline level to assign.
    I then created a WORD template that imported the .txt file, and ran a macro that formatted the text. Using the first number to assign outline number levels, I formatted everything by use of search/replace styles. The $$ around the ID number were used because I couldn't search for () since they represent wildcards.

    After the Find/Replace formatting, the .txt info looked like:
    3.1.1 COMMON STUDY REQUIREMENTS
    3.1.1.1 Log In
    3.1.1.1.1 The system must allow the user to log in to CDAS. (ID=8)
    3.1.1.1.1.1 The system must allow the user to enter a username and password. (ID=10)
    3.1.1.1.1.2 The system must only allow authenticated users access to CDAS. (ID=12)
    3.1.1.2 Site Map




    So, you should be able to create styles associated with various sections, export from ACCESS to a .txt file, and then insert the contents of that file into a WORD Document. Use find and Replace to assign appropriate styles.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting text output (97)

    Sounds like a perfect spot to use Implements, if I have ever heard one.

    Implements lets you create one interface, which can use multiple classes.

    You could build a blank class (read MSDN articles about Implements) that would replicate functions similar to ADO (RecordCount, EOF, BOF, MoveFirst, etc.), then build individual classes for each type of format. That way, you can use the Implements class in all of your code to interact with the data, and to allow the class to interact correctly, you simply tell it which type of implements to use.

    Drew

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formatting text output (97)

    Did Access 97 support Implements? I know VB5 did but I didn't think VBA 5 did.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting text output (97)

    No, I don't think so. I was thinking more along the lines of doing that in VB 6.0, creating an ActiveX control, and using that within Access. ( Because you never know when you would want to interact with that data from something else, like VB, ASP, etc, and if they are in a .dll, you just reference it.)

    Drew

Posting Permissions

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