Export to Ascii

[Guest]

I have a database where two tables have a one to many relationship defined
where a single record in table one can have any number of records in table 2,
although in reality it is usually between 1 and four.
I need to export the records to an ascii file where the records are
concatenated with the record from table 1 being followed by all associated
records from table 2.
example:
T1T2T2T2T2
T1T2
T1T2T2

I'm ok with the basics of the export to the ascii file but would be very
grateful to receive some help in regard to how to achieve the concatenation
of the various records.

Thanks,

Dave

 

[John Nurick]

Hi Dave,

Go to http://www.mvps.org/access/modules/mdl0004.htm and download the
fConcatChild() function. This can be used in a query to collect and
concatenate values from a related table; then export the query to a text
file.

 

[Guest]

Thanks John ... I'll give it a go.

Dave

Hi Dave,

Go to http://www.mvps.org/access/modules/mdl0004.htm and download the
fConcatChild() function. This can be used in a query to collect and
concatenate values from a related table; then export the query to a text
file.

 

[Guest]

Hello John,
The referenced function creates a deliminated ascii file; what I really need
is a fixed length record file. I'd appreciate any suggestions how to achieve
this.
Thanks,
Dave

 

[John Nurick]

All you need to do is first to modify the fConcatChild() function so
that instead of concatenating with a delimiter it concatenates a fixed
length string for each sub-record value. Instead of the exisiting

Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";"
.MoveNext
Loop

use something like this air code (which gives a 5-character field, left
aligned)

Do Until rs.EOF
varConcat = varConcat & Left(rs(strFldConcat) & Space(5), 5)
.MoveNext
Loop

This will give you a concatenated field containing a variable number of
fixed-width (5-character, in this example) subfields. If you want to
export to a fixed-width file you presumably need to pad out all these
fields to the same maximum length.

Usually it's simplest to do this by using TransferText to export the
query, with an export specification or schema.ini to set the fixed field
widths. If you're writing your own VBA export routine, just modify
fConcatChild() further to return a fixed-length string (in this example,
250 characters wide, i.e. 50 five-character subfields):

fConcatChild = Left(varConCat & Space(50 * 5), 50 * 5)

 

[Guest]

John,
Just a quick note to thank you for your help. I've used both of your
solutions and am now comfortable with the logic behind them and am about to
put them to use.
Thanks again,
Dave

 

[John Nurick]

Thanks for the feedback.

John,
Just a quick note to thank you for your help. I've used both of your
solutions and am now comfortable with the logic behind them and am about to
put them to use.
Thanks again,
Dave

 

[Guest]

Hello John,
It's some time since you helped me on this but I've just hit a problem which
I hope you can help me with.
The solution you directed me towards works great in the original
application, but I've now need to use the same type code in another project.
The problem I'm having is that the concatinated "T2" records are exceeding
255 characters and as a result I'm getting junk in the export file after the
255th character.
Can you suggest any way that I might overcome this problem. For your
information, the "T2" fields equate to a total of 250 characters per record
and there are usually two, but up to four, of these records for each "T1"
record.

I hope that you pick this up after all this time and that you can stop my
hair going ever greyer!!!

Thanks,

Dave

 

[John Nurick]

Hi Dave,

I'm glad to say I can't remember the details (glad because it means I
have a life!). At a guess you're hitting Access's limit of 255
characters in a calculated field when you export a query. This means
that if you use the modified fConcatChild to create a field longer than
255 characters it will be truncated when you export it.

The official work-round is to use a temporary table containing a memo
field. Modify your query to append its output to the temporary table;
run the query; export the data direct from the temporary table (that
way, the contents of the memo field will not be truncated); and delete
the temporary records ready for next time.

The other is to create a query that, instead of returning one big field
containing all the T2 records concatenated, returns N fields each
containing one T2 record - N being the greatest number of T2s for any
T1. Then you'd create an export specification with the appropriate field
lengths to export it to a fixed-width file.

Depending on what's in your tables, this may be possible with a crosstab
query, or it may mean some joining N copies of the T2 table to T1, maybe
with some cunning subqueries to keep things in order. Post back here
with more information on your tables and their keys.