asked on

Export to flat file from Access 2003, not using saved spec

For some reason my saved spec gets whacky and doesn't sort the export to flat file consistently, no matter what I do. I need a rock-solid way to export from an Access table to a flat file. This export runs after the table is built, and it gets built once every few minutes. It's an inventory level feed for our products. The quantity on hand and the pricing can change often, so this file gets uploaded once an hour or so. There are 20 suppliers, and each supplier gets a flat file built and uploaded.

The problem specifically is that the table has field names, of course, and then the first record MUST be inserted into the text file immediately following the field names. After that, the sorting doesn't matter. For some reason, it fails to keep that first row at the top about 30% of the time, no rhyme or reason as to why it fails. I have tried everything GUI-wise to keep the table sorted as I want it, but nothing is consistent.

What's the best option?

Rey Obrero (Capricorn1)

you then have to create a new export specification.

export a query with an order by clause instead of exporting the table.

Bobby

ASKER

Are you saying do not use what Access calls Saved Specifications, but instead use a query to export to the flat file?

Rey Obrero (Capricorn1)

<Are you saying do not use what Access calls Saved Specifications, but instead use a query to export to the flat file? >

No, you will still need the saved export specification, but use a query instead of the table.

post the codes that you use for exporting.

Bobby

ASKER

If so, how do I use a query to write to a flat file? I currently have this in the code:

txtExportName = "C:\AmazonFeeds\production\temp\data.txt"

DoCmd.TransferText acExportDelim, "AmazonFlatFileExportSpecification", "Data2", txtExportName, True

    
    
          ' If an error occurs, close the files and end the macro.
      On Error GoTo ErrHandler

      ' Open the destination text file.
      DestNum = FreeFile()
      Open "C:\AmazonFeeds\production\temp\feed.txt" For Append As DestNum

      ' Open the source text file.
      SourceNum = FreeFile()
      Open "C:\AmazonFeeds\production\temp\data.txt" For Input As SourceNum

      ' Include the following line if the first line of the source
      ' file is a header row that you do now want to append to the
      ' destination file:
      ' Line Input #SourceNum, Temp

      ' Read each line of the source file and append it to the
      ' destination file.
      Do While Not EOF(SourceNum)
         Line Input #SourceNum, Temp
         Print #DestNum, Temp
      Loop

CloseFiles:

      ' Close the destination file and the source file.
      Close #DestNum
      Close #SourceNum
      
      
    DoCmd.Hourglass False
    
    Shell "C:\AmazonFeeds\production\temp\PutFeed.bat", vbHide
    
    
      Exit Sub

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bobby

ASKER

trying that now...

Bobby

ASKER