Export Access table data to flat file (txt file)

[edmscan]

I have a bit of a ? for the experts here. I have some data in Access which is in a table now (about 480 rows / 1 field containing email addresses) ... and I need to export the data to what I would call a one line comma delimited flat file.



So the text file needs to look like this .. abcdef@email1.com,abcdef@email2.com,abcdef@email3. com ... and on and on til all my 480 email addresses are done on the one line.

I can easily export them to 480 lines .. but that is not acceptable. I do not want to manually have to edit the txt / csv file if I do not have to as this will have to be done on a possible regular basis.

Thanks
- Kevin -

[edmscan]

Because .. I usually figure it out, here is my solution for anyone else that may come across this kind of thing. It may not be pretty .. but it works. I have very little experience with recordsets and writing to files as such so maybe someone else can improve upon this.

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile("C:\*****\Current.txt")


Dim dbsEmail As DAO.Database
Dim rstEmail As DAO.Recordset
Dim strSQL As String

Set dbsEmail = CurrentDb

strSQL = "SELECT Email FROM tblEmail;"

Set rstEmail = dbsEmail.OpenRecordset(strSQL, dbOpenDynaset)

'If the recordset is empty, exit.
If rstEmail.EOF Then Exit Sub


With rstEmail

.MoveFirst

Do Until .EOF

oFile.Write rstEmail.Fields(0)
oFile.Write Chr(44)

.MoveNext

Loop
End With

MsgBox "Completed Processing Emails", vbOKOnly, "Finished Processing Emails"

oFile.Close
Set fso = Nothing
Set oFile = Nothing

rstEmail.Close
dbsEmail.Close

Set rstEmail = Nothing
Set dbsEmail = Nothing

[June7]

An alternative could be looping code that builds string then one Write execution.

[edmscan]

An alternative could be looping code that builds string then one Write execution.

Thanks ..

I am actually more than happy with how it is working. It takes < 1 second to run. So .. performance is not an issue and there will not be more than a minimal amount of added data in the future. It is just not how I would normally export data, but it is what was requested.