create ascii file to unique design

  • Thread starter Thread starter GBA
  • Start date Start date
G

GBA

A toss up whether this is better posted under programming or export: we need
to deliver an ascii file. The Access2003 db is fine. The recipient is
dictating the layout of the ascii file. I can select the appropriate fields
from the Access db using two queries.

Query 1 (Q1) will always have just one record, and always the same set of
fields; let’s call the fields: A,B,C,D.

Query 2 (Q2) will always have at least one record, generally more than one,
and always also have the same set of fields; lets call these fields:
F-1,F-2,F-3 - - -(there is a unique key field also in Q2 not shown and not
included in the ascii export) and let’s say there are 3 records in Q2 for
this example.

The ASCII file must be this:
ABF-1F-2F-3F-1F-2F-3F-1F-2F-3CD
Note F-1,F-2,F-3 repeats 3x for this example because we said there were 3
records. Also note; no delimiter, no line breaks…just a steady line up of
data fields.
That’s it. So I’m thinking vb code that picks Q1’s AB then loop to get
fields from Q2 and then back to Q1’s CD?? But have never done something like
this. Would welcome input & generic syntax from anyone who has done this
type of ascii formatting as an export… grateful thanks in advance
 
Try somtign like this:

Sub testm1()

Dim rstParent As DAO.Recordset
Dim rstChild As DAO.Recordset
Dim intOutFile As Integer
Dim strOutFile As String
Dim strOneLine As String
Dim strSql As String

strOutFile = "c:\mydata\data.txt"
intOutFile = FreeFile
Open strOutFile For Output As #intOutFile

Set rstParent = CurrentDb.OpenRecordset("tblParent")

Do While rstParent.EOF
strOneLine = rstParent!A & rstParent!B
strSql = "select * from tblchild where parent_ID = " & rstParent!ID
Set rstChild = CurrentDb.OpenRecordset(strSql)
Do While rstChild.EOF = False
strOneLine = strOneLine & rstChild!f1 & rstChild!f2 & rstChild!f3
rstChild.MoveNext
Loop
strOneLine = strOneLine & rstParent!C & rstParent!D
Print #intOutFile, strOneLine;
rstParent.MoveNext
Loop
rstParent.Close

Close intOutFile

Beep

MsgBox "export done"

End Sub


the above is air code...but, it should be quite close to what you need....
 
hey, wanted to thank you for the air code; have copied it out and will be
working with it in the coming week. haven't been able to get reliably into
this site for about a day & half - - kept getting either a 'temporarily out
of service' page or just so slow as to be unusuable....much thanks for
posting...it helps me with a starting point.
 
Back
Top