Export to txt from table causes access failure

  • Thread starter Thread starter KJ
  • Start date Start date
K

KJ

when using the docmd.outputto command to export a table to
a txt file, a windows error occurs and access is closed.
this does not happen with all version of access 2000. what
could cause this? is there a better way to export to text
from VBA?
 
Better is a relative term. However ANOTHER way to export a table to a text
file is to loop through all of the records, build all the fields into a
string, and then export the string to a text file using the PRINT command.
This is old-style, low-level, BASIC I/O programming, but I use it when I
want extra control over the export process.

Something like this:
'************************
Sub ExportTextFile()
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String

Set cnn = CurrentProject.Connection
strDS = cnn.Properties("data source")
Directory = (Mid(strDS, 1, Len(strDS) - Len(Dir(strDS))))

Open Directory & "\TestOutput.txt" For Output As #1

rst.Open "tblMyTable", cnn, adOpenForwardOnly, adLockReadOnly

rst.MoveFirst
Do While Not rst.EOF
MyString = rst!Field1 & ", " & _
rst!Field2 & ", " & _
rst!Field3 & ", " & _
rst!Field4 & ", " & _
rst!Fill_Field5
Print #1, MyString
rst.MoveNext
Loop

ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub

ExportTextFile_Err:

MsgBox Err.Description
Resume ExportTextFile_Exit

End Sub
'************************
 
Back
Top