Exporting Multiple Tables to Text

[John W]

I am developing a database that imports delimited text files from multiple
clients into 5 tables, then needs to combine the information, replace
certain fields with our companies information and then export back to text
to be forwarded on for processing. I've successfully written the VBA code
for the import (thanks mostly to this newgroup). Now I need to start the
export. I'm completely self taught so I'm not sure what direction to go.

An example of my tables is:
Table1
Imported File ID* File Submitter(who sent us the file)

Table 2
Imported File ID File Receiver ID* (the destination of the output file)

Table 3
File Receiver ID Order ID*

Table 4
OrderID * OrderHeaderInformation

Table 5
Order ID Details

Each imported file may contain multiple receivers and each receiver will
have multiple orders. This is why I had to break it into so many tables.

I'm needing to look at the table 2 receivers. For each receiver I need to
export to text all the details from tables 3,4&5 before moving to the next
receiver. The information from Table 1 will have to be replaced with our
companies information. The exported files will look just like the imported
ones except all incoming orders for each receiver will be combined and the
table 1 info will be replaced with new info.

I've learned a lot working on the import but now I'm not sure where to start
for the export. If someone could please point me in the right direction so
I at least know where to start. Thanks again for the help!

 

[Smartin]

I am developing a database that imports delimited text files from multiple
clients into 5 tables, then needs to combine the information, replace
certain fields with our companies information and then export back to text
to be forwarded on for processing. I've successfully written the VBA code
for the import (thanks mostly to this newgroup). Now I need to start the
export. I'm completely self taught so I'm not sure what direction to go.

An example of my tables is:
Table1
Imported File ID* File Submitter(who sent us the file)

Table 2
Imported File ID File Receiver ID* (the destination of the output file)

Table 3
File Receiver ID Order ID*

Table 4
OrderID * OrderHeaderInformation

Table 5
Order ID Details

Each imported file may contain multiple receivers and each receiver will
have multiple orders. This is why I had to break it into so many tables.

I'm needing to look at the table 2 receivers. For each receiver I need to
export to text all the details from tables 3,4&5 before moving to the next
receiver. The information from Table 1 will have to be replaced with our
companies information. The exported files will look just like the imported
ones except all incoming orders for each receiver will be combined and the
table 1 info will be replaced with new info.

I've learned a lot working on the import but now I'm not sure where to start
for the export. If someone could please point me in the right direction so
I at least know where to start. Thanks again for the help!

Hi John,

I think your next step should be to build a query that reassembles the
data the way you want it. I think you are saying you want to "break"
your export at each receiver. I wouldn't worry about that yet -- just
make a query that has all the information you want to export for all
the receivers and sort on the receiver ID for now. Also, make a second
query that returns one receiver ID per row from the first query (a
simple group by receiver ID will do).

Once you have that going we can use VBA to capture data on the fly
that breaks on each receiver ID (using the second query as a "control"
loop). I'm having a brain fart on how to export from here but a work-
around would be to make tables and export as you go. This seems very
unelegant to me. Hopefully someone can fill in what I'm missing.

Air code:

Set rstReceivers = dbengine(0)(0).openrecordset (<query that returns
distinct receivers>)
rstReceivers.MoveFirst
Do While Not EOF rstReceivers
ReceiverID = rstReceivers.fields(0).value
tmpQuerySQL = "SELECT * INTO tblTEMP FROM <query that reassembles the
data> WHERE ReceiverID = " & ReceiverID & ";" ' assumes a numeric
ReceiverID
DoCmd.TransferDatabase tblTEMP ... as text ... make a unique name
using the receiverID
rstReceivers.MoveNext
Loop
Set rstReceiver = Nothing

 

[John W]

This gives me something to start with. I'm still a little confused so I'll
have to play around with it. The actual export I think should be no problem
using VBA - what seems difficult to me is getting everything sorted the way
I need it.

Thanks for the help!