Error export to text file - Can't start past position 32767

[Monika Reninger]

I am attempting to export a very large table
(approximately 130 fields, most are memos) to a pipe-
delimited text file. When I attempt to export the data,
I get the following error:
The field <field name> contains a start position
of '33120'. The maximum start position allowed is 32767.

Anyone know a way around this error?

[John Nurick]

Hi Monika,

I think this is a limitation of the Access textfile routines. You can
overcome it by writing your own export routine in VBA, using the
FileSystemObject and TextStream objects to create and access the
textfile.

(I assume you're already familiar with the requirements for a successful
delimited textfile; you'll need to handle all the quoting and doubling
of quote marks yourself.)

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[nliba...@gmail.com]

I found away around by changing the dim type to LONG.
I use the following VBA:
Public Function textStreamRead(fileLocation As String) as Integer
Dim tso As TextStream
Dim strLine As String
Dim lineCount As Integer

lineCount = 0
textStreamRead = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set tso = fso.OpenTextFile(fileLocation, ForReading)

Do While Not tso.AtEndOfStream
strLine = tso.ReadLine()
lineCount = lineCount + 1
Loop
textStreamRead = lineCount - 1 'Header
tso.Close

End Function

When file limit exceeded 32767 I received the Overflow error.
I changed the function data type and line count variable to long.
This is my revision, works like a charm.

Public Function textStreamRead(fileLocation As String) As Long
Dim tso As TextStream
Dim strLine As String
Dim lineCount As Long

lineCount = 0
textStreamRead = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set tso = fso.OpenTextFile(fileLocation, ForReading)

Do While Not tso.AtEndOfStream
strLine = tso.ReadLine()
lineCount = lineCount + 1
Loop
textStreamRead = lineCount - 1 'Header
tso.Close

End Function