Exporting records from two tables with different fields from AcessXP to a text file

  • Thread starter Thread starter Blake Farren
  • Start date Start date
B

Blake Farren

Hello, I would like to know if it is possible to export data to a text
file where the fields of the two tables are not the same. The
requirement for the file is to have an initial fixed width record
containing specific information regarding the sending program and then
the fixed width records regarding the specifics of the billing
information. I can use the transfertext command to send the initial
record to a text file but when I then send the main table of data it
overwrites the initial line. Is there anyway of appending records even
if they do not have the same export specification?? Sometimes ( make
that most times) I wish government departments would enter the 21st
century.. any assistance would be greatly appreciated.. Blake Farren
(e-mail address removed)
 
You cannot use TransferText or wizards to do this.
You have to write code.

Here is a sample using 1 recordset. You will need to declare a 2nd recordset
variable and assign it to your other table. Then you can open both
recordsets and alternate between header and Detail records as you write out
the file.

Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(![key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(![Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(![TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(![Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(![Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(![Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(![Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(![Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub
 
Thanks Joe for the information. I was wondering if this code is based
on the ADO model. I have been working on this project using DAO or
does it matter.. Thanks again. Blake Farren (e-mail address removed)
 
Back
Top