Efficient transfer of a table to text file RRS feed

  • Question

  • I have a table in Access (250,000 lines plus) that I need to send to a delimited text file using TAB as the delimiter and continental number format ( my data has 9,999.99 and I need to output 9999,99). I have written code using FileSystemObject to write out the field names then to scroll through the file writing the fields and converting the numbers to the right format. But it takes for ever. I am sure in the distant past I have seen a way of using ADO to connect to a text file and output a recordset. 

    Can anyone point me in the right direction?



    Tuesday, November 22, 2016 6:20 PM

All replies

  • I have not done this so I don't know if this works.  I know ADODB.Stream can read a record and ADODB.Stream can write a text file.  I am not sure how it handles a complete recordset.  In the SQL you may need to create the string with TABS rather than a normal SQL statement.   Have ACCESS do most of the work.  I have found it much faster than using VBA.
    Tuesday, November 22, 2016 7:59 PM
  • I did an experiment and ADODB.Stream doesn't work with recordset only record.  I still think making the string in SQL will be faster.
    Wednesday, November 23, 2016 1:17 PM