Link, Compile , Export Help

[Steve]

We are looking to automate a task within our batch-process based MRP system.
The MRP system has a very strict requirement on how we must form this data
in order for it to accept it. The MRP system has 7 exported Access 2000
tables where the data we need to compile is stored.

Basically, we want to automate creation of an invoice. The MRP system
requirement says in order to do this that I need to generate a CSV file
consisting of 4 inter-related transactions in a specific form.

So, this is they layout they require - ID1, ID2, ID3 and ID4 are the
transaction set markers, "T" indicates text field, N indicates number field,
"" indicates a blank placemarker that I must include.

This is one transaction:

"ID1","T","T",N,N,N,"T",N, "T","","", N,N
"ID2",N,N,"T","T",N,N,""
"ID3","","","",N,N,N,N,"T"
"ID4","T","T","","",N,N

This is 3 transactions

"ID1","T","T",N,N,N,"T",N, "T","","", N,N
"ID2",N,N,"T","T",N,N,""
"ID3","","","",N,N,N,N,"T"
"ID4","T","T","","",N,N
"ID1","T","T",N,N,N,"T",N, "T","","", N,N
"ID2",N,N,"T","T",N,N,""
"ID3","","","",N,N,N,N,"T"
"ID4","T","T","","",N,N
"ID1","T","T",N,N,N,"T",N, "T","","", N,N
"ID2",N,N,"T","T",N,N,""
"ID3","","","",N,N,N,N,"T"
"ID4","T","T","","",N,N

I can easily compile the information necessary for each ID. My thought was
since the text, number, blank place holders don't line up that I'd have a
table called ID1 with the ID1 data, ID2 with the ID2 data, ID3 with the ID3
data, etc.

However, I need to write it out to a text file so record 1 from ID1 is line
1, record 1 from ID2 is line 2, record 1 from ID3 is line 3 .

My thought is that I need open ID1 and do a writeline to write record 1,
open table ID2 and do a writeline, open table 3 and do a writeline, open
table 4 and do a writeline, move to record 2 and loop. I need to but don't
yet have a though on how I make sure that records in each table are alinged
so that each set is written in the right order.

Anyone know of an easier way to accomplish this? Our two limiting factors
are the MRP system provides the 7 Access 2000 tables (no other format
options) and we must have the transactions in the right order and with
everything above specified. We elected to use Access 2000 since the provided
tables are already in that format, we'd be open to using some other tool
though we can't think of/find one that wouldn't have the same issues
(complicated, potentially error prone writeline and loop throughs)

Any thoughts?

 

[Albert D. Kallal]

It is not at all clear how you load up the transactions..

are the id1 - id4 simply reocrds from a talbe, or 4 differnt tables...or
what??

It don't really matter, but I would load up 4 reocrdsets...

dim rst1 as dao.ReocrdSet
dim rst2 as dao.ReocrdSet
dim rst3 as dao.ReocrdSet
dim rst4 as dao.ReocrdSet
dim intF as integer

set rst1 = currentdb.openReocrdSet("select * from ....")
set rst2 = currentdb.openReocrdSet("select * from ....")
set rst3 = currentdb.openReocrdSet("select * from ....")
set rst4 = currentdb.openReocrdSet("select * from ....")

So, you load up the 4 sets via the correct sql.

intF = freeFile()
open "c:\dataout.txt" for output as #intF

do while rst1.EOF = false

' code to writeline rst1
rst1.movenext

' code to writeline rst2
rst2.movenext

' code to writeline rst3
rst3.movenext

' code to writeline rst4
rst4.movenext

loop

Since each set of records should match up, then the above would work if you
load up 4 reocrdsets.....

There is no restrictions on having multiple reocrdsets open to the same
table, or different tables if need be...