Exporting from Access to a txt file

[Donna]

I'm exporting from Access to a txt file. The problem I
have is keeping the sequencing. I'm combining three
tables into a main table (a header record, line record &
comment record all for one order number) and I need to
keep the order the main table appears in when I export.
80% of the time it works, the other 20%, I'll have one or
two records out of order.

It should look like: "10","001","item"
"20","001","item"
"30","001","item"
"10","002","item"

It ends up like this: "20","001","item"
"30","001","item"
"10","002","item"
"10","001","item"
"20","002","item"

Please let me know if there's a way to do this.
Thanks

 

[Ken Snell]

Use a query to order your data, and export the query. There is no inherent
order in a table and you have no guarantees about which record is "first" or
"last".

 

[Joe Fallon]

When you have data that is not "table like" you should use code to export
it.
SInce you are working with 3 tables I would use 3 recordsets and walk
through them in code until the file was processed completely.


Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub