Appending data to .txt file from Query

[ingrammx]

Does anyone know how to append data from a MS Access query to a .txt file. I have a process to import data and check for errors. I would like to report the errors to a text file after every import without deleting the previous results (ie the Transfer text method)
Thanks

[NeoPa]

You would have to write code to process through the data (ADO or DAO RecordSet) and write to a text file opened using the Open Statement.
The help system will give you more details on those facilities.

[NeoPa]

Of course, you could also write a new, and separate, file using TransferText, then use Shell to run a COPY command that appends the two files together into a resultant file. There are all sorts of possibilities there.

[ADezii]

@ingrammx
Sometimes, I simply prefer a very Low Level approach such as the following code which will write the Values of 3 Fields in a Recordset to a Comma Delimited Text File. It this Format, it can then be easily Imported into a host of Database, and other varied, Applications.

 
Dim MyDB As DAO.Database
Dim rstEmployees As DAO.Recordset
Dim intFileNum As Integer
intFileNum = FreeFile()
Open "C:\Employee.txt" For Append As #intFileNum
Set MyDB = CurrentDb()
Set rstEmployees = MyDB.OpenRecordset("Employees", dbOpenForwardOnly)
With rstEmployees
  Do While Not .EOF
    Write #intFileNum, ![FirstName], ![LastName], ![Address]
      .MoveNext
  Loop
End With
Close #intFileNum
rstEmployees.Close
Set rstEmployees = Nothing
 

Sample data in Employee.txt

"Nancy","Davolio","507 - 20th Ave. E. Apt. 2A"
"Andrew","Fuller","1313 Mockingbird Lane"
"Janet","Leverling","722 Moss Bay Blvd."
"Margaret","Peacock","4110 Old Redmond Rd."
"Steven","Buchanan","14 Garrett Hill"
"Michael","Suyama","Coventry House Miner Rd."
"Robert","King","Edgeham Hollow Winchester Way"
"Laura","Callahan","4726 - 11th Ave. N.E."
"Anne","Dodsworth","7 Houndstooth Rd."

[NeoPa]

Sorry ADezii. I resurrected this thread by accident yesterday when looking at my first screen of subscribed threads :D

I don't think the OP is likely to see this. It doesn't hurt to have answers visible for searchers though.

[ADezii]

@NeoPa
Gotcha, no problemo!