Field too small to accept input values?

[Jesse Aviles]

I have the following code:

Function ReadInventory()
'---------------------------------------------------------------------------------------
' Procedure : ReadInventory
' DateTime : 2005-06-19 20:44
' Author : Jesse Avilés
' Purpose : Read the scanning gun results and copy the data to a processing table
'---------------------------------------------------------------------------------------
'
Dim rst As ADODB.Recordset
Dim i As Integer
'Dim l As Long
Dim strFolder As String
Dim colGunData As Collection
Dim colProcessedOK As Collection
Dim objFS As FileSystemObject
Dim objTS As TextStream

On Error GoTo ErrorHandler

Set rst = New ADODB.Recordset
strFolder = GetProcLocation()

Set colGunData = New Collection
Set colProcessedOK = New Collection

FindFilesUsingAPI strFolder, colGunData

i = 1
' l = 1

rst.Open "tblConteoInventario", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
For i = 1 To colGunData.Count
Set objFS = New FileSystemObject
Set objTS = objFS.OpenTextFile(colGunData.Item(i))
Do Until objTS.AtEndOfStream
rst.AddNew
Debug.Print "line: " & objTS.ReadLine
---------> rst("txtInventarioID") = objTS.ReadLine <----------------
rst.Update
objTS.SkipLine
Loop
colProcessedOK.Add colGunData.Item(i)
Next i

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set colProcessedOK = Nothing
Set colGunData = Nothing
Exit Function

ErrorHandler:

MsgBox "Unexpected Error: " & Err.Number & vbNewLine & Err.Description & vbNewLine & "In
procedure ReadInventory of Module mdlGetFilesInfo"
Resume ExitHandler
End Function

This code breaks at the line marked -----> <-------
The error given is:
"Error -2147217887
The field is too small to accept the amount of data you attempted to add. Try inserting or
pasting less data."

The field is a text field with a lenght of 50 characters. The value that is supposed to be inserted
is 015000. There are still 44 spaces left unused. The debug.Print statement presented the above
value so I know that it is not trying to insert more than one value. I'm rather puzzled by this,
any help will be greatly appreciated.

 

[Arvin Meyer [MVP]]

The only thing that comes to mind is incompatible datatypes. But that should
generate an Error 13 type mismatch. Try inserting an obvious text value like
your name and see what happens.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I have the following code:

Function ReadInventory()
'---------------------------------------------------------------------------
------------
' Procedure : ReadInventory
' DateTime : 2005-06-19 20:44
' Author : Jesse Avilés
' Purpose : Read the scanning gun results and copy the data to a processing table
'---------------------------------------------------------------------------
------------
'
Dim rst As ADODB.Recordset
Dim i As Integer
'Dim l As Long
Dim strFolder As String
Dim colGunData As Collection
Dim colProcessedOK As Collection
Dim objFS As FileSystemObject
Dim objTS As TextStream

On Error GoTo ErrorHandler

Set rst = New ADODB.Recordset
strFolder = GetProcLocation()

Set colGunData = New Collection
Set colProcessedOK = New Collection

FindFilesUsingAPI strFolder, colGunData

i = 1
' l = 1

rst.Open "tblConteoInventario", CurrentProject.Connection,

adOpenDynamic, adLockPessimistic

 

[Jesse Aviles]

I tried using CStr(objTS.ReadLine) and still had the error.

--
Jesse Avilés
(e-mail address removed)

Reply Only To The Newsgroup

 

[Douglas J. Steele]

While the value may only be 015000, there could be additional padding
characters afterwards.

Try changing what you're printing out:

Debug.Print "line: " & objTS.ReadLine
Debug.Print "length: " & Len(objTS.ReadLine)
rst("txtInventarioID") = objTS.ReadLine


If it turns out that the length is greater than 50 characters, you'll have
to figure out what character is being used for padding (it's likely a Null
characters, which is Chr(0)), and strip them off. Assuming it is a Null,
try:

rst("txtInventarioID") = Left$(objTS.ReadLine, InStr(objTS.ReadLine,
Chr$(0))-1)

 

[Jesse Aviles]

Thats exactly what was happening.. I wasn't able to see them at first, so I added another character
at the end of the debug print statement and there it was. Fix it using Replace (objTS.ReadLine, "
",""). The code below has the problem of the skip line. I was not seeing all the data and it turns
out that the skip line instruction is unnecessary.

There is another problem. I'm testing for objTS.AtEndOfStream in my Do...Loop. It turns out that I
always have an error at the last line. Once all the data is copied, the Do...Loop continues without
finding out that the AtEndOfStream is True. I tried changing the Do...Loop test for vbNull but it
appears that the first character of every line of data is a null character.

Mr. Steele thanks for the article in MS Access Advisor (I belive I have already read something
similar you wrote in Smart Access) titled "Move Local Data to Access". Is the basis of
FindFilesUsingAPI function. It couldn't have come at a better time.

 

[Douglas J. Steele]

Sorry, I never use FSO unless I absolutely have to, so I don't think I can
offer any suggestions. It's extremely rare that I can't do the same things
using just native VBA commands and/or API calls. VBA works fine to read text
files.

 

[Jesse Aviles]

When I search VBA help on opening/reading text files (and VBA decides that I've been good enough to
show me some help) I end up with FSO and ADO.Stream. I thought that ADO.Stream seemed easier so
decided to change the previous posting code. This is the code I ended up with (still had to use FSO
down the road):

Function ReadInventory()
'---------------------------------------------------------------------------------------
' Procedure : ReadInventory
' DateTime : 2005-06-19 20:44
' Author : Jesse Avilés
' Purpose : Read the scanning gun results and copy the data to a processing table
' Move processed files to Processed folder and files with errors to ProcError folder
'---------------------------------------------------------------------------------------
'
Dim rst As ADODB.Recordset
Dim lngTopCount As Long
Dim lng As Long
Dim strFolder As String
Dim strProcessed As String
Dim colGunData As Collection
Dim colProcessedOK As Collection
Dim objTS As ADODB.Stream
Dim strSource As String
Dim objFSO As FileSystemObject

On Error GoTo ErrorHandler

DoCmd.Hourglass True 'Visual clue that something is happening

strFolder = GetProcLocation("txtNoProc") 'Unprocessed files location

Set colGunData = New Collection 'Collection to hold file names of unprocessed files
Set colProcessedOK = New Collection 'Collection to hold file names of processed files

FindFilesUsingAPI strFolder, colGunData 'Populate collection with unprocessed files names

If colGunData.Count = 0 Then
MsgBox "No files to process.", vbExclamation, "No Files"
GoTo ExitHandler
Else
lngTopCount = colGunData.Count
lng = 1

Set rst = New ADODB.Recordset 'Open table that will hold temporary data from all
unprocessed files
rst.Open "tblConteoInventario", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

Set objTS = New ADODB.Stream
'Loop through all the unprocessed files will adding the data contained in each file to the
processing table
For lng = 1 To lngTopCount
strSource = CStr(colGunData.Item(lng))
objTS.Open
objTS.Charset = "ASCII"
objTS.LoadFromFile strSource
Do While objTS.EOS <> True
rst.AddNew
rst("txtInventarioID") = Replace(objTS.ReadText(adReadLine), " ", "")
rst.Update
Loop
colProcessedOK.Add colGunData.Item(lng) 'Add processed file reference so the file
can be moved later
objTS.Close
Next lng

'Reset counters and move files
lngTopCount = colProcessedOK.Count
lng = 1

strProcessed = GetProcLocation("txtProc") 'Get folder address to move files

Set objFSO = New FileSystemObject

For lng = 1 To lngTopCount
objFSO.MoveFile CStr(colProcessedOK.Item(lng)), strProcessed
Next
End If

MsgBox "Files processed and moved.", vbInformation, "Process Status"

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set objTS = Nothing
Set colProcessedOK = Nothing
Set colGunData = Nothing
DoCmd.Hourglass False
Exit Function

ErrorHandler:
MsgBox "Unexpected Error: " & Err.Number & vbNewLine & Err.Description & vbNewLine & "In
procedure ReadInventory of Module mdlGetFilesInfo"
Resume ExitHandler

End Function

This is one of the functions that the above code calls.
Function GetProcLocation(strWhere As String) As String
'---------------------------------------------------------------------------------------
' Procedure : GetProcLocation
' DateTime : 2005-06-19 20:54
' Author : Jesse Avilés
' Purpose : Returns a string with location of unprocessed file folder.
' String is already saved in table tblFileLocations
' Input : strWhere - Name of the text field that contains the location string of the
' Processing state folder.
'---------------------------------------------------------------------------------------
'
Dim rst As ADODB.Recordset

On Error GoTo ErrorHandler

Set rst = New ADODB.Recordset
With rst
.Open "tblFileLocations", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
GetProcLocation = .Fields(strWhere)
End With

ExitHandler:
rst.Close
Set rst = Nothing
Exit Function

ErrorHandler:

MsgBox "Unexpected Error: " & Err.Number & vbNewLine & Err.Description & vbNewLine & "In
procedure GetProcLocation of Module Module1"
Resume ExitHandler
End Function

The FindFileUsingAPI function is from Douglas Steele. You can find it in the MS Access Advisor
website (www.msaccessadvisor.com) if you are member or in the printed magazine. Thanks for the help
offered.

 

[Jesse Aviles]

How can I use VBA to read text files? When I search Help, I end up with FSO and ADO.Stream

 

[Douglas J. Steele]

Dim intFile As Integer
Dim strBuffer As String
Dim strFile As String

strFile = colGunData.Item(i)
' Grab the next available file number to use for reading
intFile = FreeFile
' Open the file for input
Open strFile For Input As #intFile
' Loop until end of file
Do While Not EOF(intFile)
' Read a line from the file into the buffer
Line Input #intFile, strBuffer

' Do your stuff here...

Loop

Close #intFile