Help with exporting from form

[David]

I have a command button and when pressed I would like it to export to a Tab
delimited file. Right now I have it exported to a comma seperated file. I
cant figure out how to change the code to Tab instead of commas. I checked
the Access help file, but couldnt find anything.

Here is the current Code.
DoCmd.TransferText acExportDelim, , "PlacementExportQry",_
"G:\Testing & Tutoring\ArnoldPlacementData.txt"

Any help would be great

 

[Nikos Yannacopoulos]

David,

I don't know of any way to make Access do it directly. To deal with a
similar need I resorted to opening the source (table or query) as a
recordset and constructing the tab-delimited file through text file
operations as follows:

Sub Export_Delim(vSource As String, vFile As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vLine As String
'Dim vFile As String
'Dim vSource As String

'vSource = "PlacementExportQry"
'vFile = "G:\Testing & Tutoring\ArnoldPlacementData.txt"

Set db = CurrentDb
Set rst = db.OpenRecordset(vSource)
rst.MoveFirst
If rst.RecordCount = 0 Then
MsgBox "No records found!"
GoTo no_records
End If
Open vFile For Output As #1
Do Until rst.EOF
vLine = rst.Fields(0)
For i = 1 To rst.Fields.Count - 1
vLine = vLine & vbTab & rst.Fields(i)
Next
Print #1, vLine
rst.MoveNext
Loop
Close #1

no_records:
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

This sub is made parametric, so it can export any table/query to any file,
as per the parameters passed. If you remove the parameters from the header
and uncomment the commented lines it will work with hardcoded source query
and destination file. Note: requires a valid DAO reference to work.

HTH,
Nikos

P.S. Curious to see if there is a better solution!