Error 3349 'Numeric field overflow' when importing text file to linked table

did you inspect the error from  "Nameoftable_importerrors" table ?

Do you have any fields that are sets of numbers? Like a serial number?

Access tries to be smart and wants to interpret a number field to a number whether you wanted it treated as text or not.

About the only way around it is to build your own custom import routine.

Try something like this:

Public Function ImportPipeDelimited()
 
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
Dim DataVal() As String
Dim I As Integer
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("ResultsTable")    '<-- Change to your tablename
 
'Note that I'm not doing a delete query so this just appends to what is already _
 in the table.  At some point in the processing you will need to decide what to _
 delete and when.
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "C:\Temp\SampleFile.txt"        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString            'Read the data in
    InputString = Trim(InputString)  '<-- Make sure there are no trailing spaces.
 
    'This will split the data into an array based on the "|". If you have _
     more columns, you'll need to up number of the array amount.
        DataVal() = Split(InputString, "|")
    'There probably is a better way to do this, but this is the easy way.
    With RS
        .AddNew
        !Field1 = DataVal(1)   '<-- Change to your field name
        !Field2 = DataVal(2)   '<-- Change to your field name
        !Field3 = DataVal(3)   '<-- Change to your field name
        !Field4 = DataVal(4)   '<-- Change to your field name
        !Field5 = DataVal(5)   '<-- Change to your field name
        !Field6 = DataVal(6)   '<-- Change to your field name
        !Field7 = DataVal(7)   '<-- Change to your field name
        !Field8 = DataVal(8)   '<-- Change to your field name
        !Field9 = DataVal(9)   '<-- Change to your field name
        !Field10 = DataVal(10)   '<-- Change to your field name
        .Update
    End With
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function

Select allOpen in new window

There is no import errors table being generated; My guess is that it isn't getting that far.
Jim - if this is a question of Access interpreting numbers as a numeric field, why would it work with a local table, but not a remote (i.e. linked) table?
BTW - both the front and back ends are on my desktop at the moment.

Jim -
I will try your code.

<There is no import errors table being generated>
did you try doing a File > get external data >import ?

<why would it work with a local table, but not a remote (i.e. linked) table?>

are the structure of both table the same?

to do  a test..
right click on the linked table > copy
right click on the database object window > paste

in Paste table as window, select Structure only(local table)

now use the newly created local table for the import, see if you wil get/not get the error

>> Access interpreting numbers as a numeric field, .....

I have no idea why. I had an application that read a fixed with text file that was full of numbers. It worked like a champ for nine months. Then one day, after a Windows SP (not Office) went in, I couldn't get the file to balance.

Access had started to read and handle the one column as numbers on the import. The decimals were now being shifted 2 places. It took me four hours to determine the issue and another hour to build similar code to above.

I have tried doing a File > Get External Data > Import. That is what I was trying to convey in my original post when I said I was getting the same results when I tried doing it 'manually'.
I just re-ran the test again as follows:
1. I imported the data to a local table without error, using the standard application (i.e. Clicking a command button on a form whose click event procedure calls a subroutine in a module which executes the TransferText).
2. I exported the local table (definition only) to the back-end.
3. Deleted the table in the front-end, then linked the table from the back-end.
4. Attempted importing the data to the linked table, got the numeric overflow error.

I have another table in the same database, into which a text file is imported where I am getting the same error.

System specs:
XP Pro Version 5.1.2600 Service Pack 2 Build 2600
Access 2003 (11.6566.8221) SP2

Jet Core Components:
   Jet 3.x   "C:WINNT\System32\msrd3x40.dll"   Version 4.00.9502.0
   Jet 4.0   Not Available
   Text      "C:WINNT\System32\mstext40.dll"   Version 4.00.9502.0

Your best bet is just to create a manual import module similar to my example.

If you need help coding it, give us some sample data and your table layout.

I'm working with the code right now, don't envision any problems setting it up. I'll let you know what comes of it.
Thanks,
Todd

>> yes there is. put the fieldnames in an array fldName()

True. On some of the ones I use similar code I do some error handling and transformation on the import, instead of a separate function. It all depends on what my stop point and my data I'm bringing in.

OK -
Doing the read/write method works. But I am still most curious as to why I am experiencing the problem with the TransferText. Maybe it's just my installation.
While troubleshooting this, I put together a small app that tests both methods of importing text files. I am consistently getting the numeric overflow when I try to TransferText to a linked table. Could you give it a shot and tell me if you experience the same error? Would be most appreciated.
Thanks,
Todd

   Option Explicit
   Option Compare Database
   Private Const strModule As String = "basProcessSR"
   Private Const strDir As String = "C:\Documents and Settings\"
   Private Const strTitle As String = "Select text file to import"
   Private Const strLocalTable As String = "tblOpenSRsImportLocal"
   Private Const strRemoteTable As String = "tblOpenSRsImportLinked"
   Private Const strImportSpecOpenSR As String = "OPEN_SR_ALL Import Specification"
       
   Private Declare Function accOfficeGetFileName _
               Lib "msaccess.exe" Alias "#56" _
               (gfni As FileNameInfo, fOpen As Integer) As Long
   
   Private gfni As FileNameInfo
   Private Type FileNameInfo
      hwndOwner As Long
      strAppName As String
      strDlgTitle As String
      strOpenTitle As String
      strFile As String
      strInitialDir As String
      strFilter As String
      lngFilterIndex As Long
      lngView As Long
      lngFlags As Long
   End Type
 
Public Sub ImportReadWrite(intTableType As Integer)
      Dim strProcedure As String
      Dim intFileNum As Integer
      Dim rst As DAO.Recordset
      Dim strData() As String
      Dim intCount As Integer
      Dim intIndex As Integer
      Dim strInput As String
      Dim strTitle As String
      Dim strTable As String
      Dim db As DAO.Database
      Dim strFile As String
      Dim strSQL As String
      Dim strMsg As String
   
100   On Error GoTo ErrorHandler
110   strProcedure = "ImportReadWrite"
   
120   If intTableType = 1 Then
130      strTable = strLocalTable
140   Else
150      strTable = strRemoteTable
160   End If   'If intTableType = 1
      
170   Set db = CurrentDb
180   Set rst = db.OpenRecordset(strTable)
      
      ' Turn warnings off
190   DoCmd.SetWarnings False
      ' Clear the import table
200   strSQL = "DELETE FROM " & strTable
210   db.Execute strSQL
      ' Turn warnings back on
220   DoCmd.SetWarnings True
      
      ' Get the name of the Pulse text file
240   strFile = GetSRFile(strTitle)
250   If strFile = "" Then
260      GoTo ExitSub
270   End If   'If strFile = ""
 
      'Open the Pulse text file
280   intFileNum = FreeFile()
290   Open strFile For Input Access Read Shared As #intFileNum
 
300   With rst
310      intCount = .Fields.Count - 1
         ' Read through text file
320      While EOF(intFileNum) = False
330         Line Input #intFileNum, strInput
340         strInput = Trim(strInput)
            ' Omit heading lines
350         If IsNumeric(Mid$(strInput, 1, 6)) Then
360            strData() = Split(strInput, "|")
               ' Add record to table
370            .AddNew
380            For intIndex = 0 To intCount
                  If Not Nz(strData(intIndex), "") = "" Then
390                  .Fields(intIndex) = strData(intIndex)
400               End If   'If Not Nz(strData(intIndex)
410            Next        'For intIndex = 0
420            .Update
430         End If         'If IsNumeric(Mid$(strInput
440      Wend              'For intIndex
450      .Close
460   End With             'With rst
470   MsgBox "Done!"
 
ExitSub:
700   Application.SysCmd acSysCmdSetStatus, " "
710   DoCmd.SetWarnings True
720   On Error Resume Next
740   Close #intFileNum
750   Set rst = Nothing
760   Set db = Nothing
770   On Error GoTo 0
790   Exit Sub
 
ErrorHandler:
800   Application.SysCmd acSysCmdSetStatus, " "
810   DoCmd.Hourglass False
820   Select Case Err.Number
         Case 2501
830         Resume ExitSub
840      Case Else
850         strMsg = "Module: " & strModule & _
                  vbCrLf & "Procedure: " & _
                  strProcedure & vbCrLf & _
                  "Error: " & Err.Description & _
                  " (" & Err.Number & ")" & vbCrLf & _
                  "Error Line: '" & Erl & "'"
860         Debug.Print strMsg
870         MsgBox strMsg
880         Resume ExitSub
890   End Select
End Sub
 
Public Sub ImportTransferText(intTableType As Integer)
   Dim strProcedure As String
   Dim strTable As String
   Dim strTitle As String
   Dim db As DAO.Database
   Dim strFile As String
   Dim strSQL As String
   Dim strMsg As String
   
100   On Error GoTo ErrorHandler
110   strProcedure = "ImportTransferText"
   
120   If intTableType = 1 Then
130      strTable = strLocalTable
140   Else
150      strTable = strRemoteTable
160   End If   'If intTableType = 1
   
170   Set db = CurrentDb
      ' Turn warnings off
180   DoCmd.SetWarnings False
      ' Clear the import table
190   strSQL = "DELETE FROM " & strTable
200   db.Execute strSQL
      ' Turn warnings back on
210   DoCmd.SetWarnings True
      
      ' Get the name of the Pulse text file
220   strTitle = "Select OPEN_SR_ALL_MM-DD-YY"
230   strFile = GetSRFile(strTitle)
240   If strFile = "" Then
250      GoTo ExitSub
260   End If   'If strFile = ""
   
      ' Now import the text file
270   DoCmd.TransferText acImportDelim, strImportSpecOpenSR, _
               strTable, strFile, True
   
      ' Get rid of the header records from the table
      ' query selects for non-numeric SR#'s
280   strSQL = "DELETE IsNumeric([SR ID #]) AS ErrRec FROM " & _
               strTable & " WHERE IsNumeric([SR ID #])=False"
290   db.Execute strSQL
300   MsgBox "Done!"
   
ExitSub:
      ' Cleanup
700   Set db = Nothing
710   DoCmd.SetWarnings True
720   Application.SysCmd acSysCmdSetStatus, " "
740   On Error GoTo 0
750   Exit Sub
   
ErrorHandler:
800   Application.SysCmd acSysCmdSetStatus, " "
810   DoCmd.Hourglass False
820   Select Case Err.Number
         Case 2501
830         Resume ExitSub
840      Case Else
850         strMsg = "Module: " & strModule & _
                  vbCrLf & "Procedure: " & _
                  strProcedure & vbCrLf & _
                  "Error: " & Err.Description & _
                  " (" & Err.Number & ")" & vbCrLf & _
                  "Error Line: '" & Erl & "'"
860         Debug.Print strMsg
870         MsgBox strMsg
880         Resume ExitSub
890   End Select
End Sub
 
Private Function GetSRFile(strTitle As String) As String
      Dim strProcedure As String
      Dim strPath As String
      Dim strMsg As String
 
10    On Error GoTo ErrorHandler
20    strProcedure = "GetMdbName"
 
30    With gfni
40       .lngFlags = 0
50       .lngFilterIndex = 0
60       .strFile = ""
70       .strOpenTitle = "Select" & ""
80       .hwndOwner = Application.hWndAccessApp
 
         ' Pulse directory defined at top of module
90       .strInitialDir = strDir
 
100      .strDlgTitle = strTitle
         
110      If GetSRDialog = 0 Then
120         strPath = Trim(.strFile)
130         If Not strPath = "" Then
140            GetSRFile = strPath
150         Else
160            Application.SysCmd acSysCmdSetStatus, "No file selected"
170         End If   'If not strPath = ""
180      End If      'If GetMdbDialog = 0
190   End With       'With gfni
 
ExitFunction:
200   On Error GoTo 0
210   Exit Function
 
ErrorHandler:
220   strMsg = "Module: " & strModule & vbCrLf & _
            "Procedure: " & strProcedure & vbCrLf & _
            "Error: " & Err.Description & _
            " (" & Err.Number & ")" & vbCrLf & _
            "Line Number: " & Erl
230   Debug.Print strMsg
240   MsgBox strMsg
250   Resume ExitFunction
End Function
 
Private Function GetSRDialog() As Long
      Dim FD As Office.FileDialog
      Dim strProcedure As String
      Dim strButton As String
      Dim strTitle As String
      Dim strMsg As String
 
10    On Error GoTo ErrorHandler
20    strProcedure = "GetSRDialog"
30    Application.SysCmd acSysCmdSetStatus, " "
         
40    Set FD = FileDialog(msoFileDialogFilePicker)
50    With FD
60       .InitialFileName = gfni.strInitialDir
70       .ButtonName = gfni.strOpenTitle
80       .TITLE = gfni.strDlgTitle
90       .AllowMultiSelect = False
100      .Filters.Clear
         
110      .Filters.Add "Text Files", "*.txt;*.csv;*.tab;*.asc", 1
120      .Filters.Add "All Files", "*.*", 2
 
130      If .Show Then
140         gfni.strFile = .SelectedItems(1)
150         gfni.strInitialDir = TrimNull(.InitialFileName)
160         GetSRDialog = 0
170      End If   'If .Show
180   End With    'With FD
 
190   Set FD = Nothing
 
ExitFunction:
200   On Error GoTo 0
210   DoCmd.RepaintObject
220   Exit Function
 
ErrorHandler:
230   strMsg = "Module: " & strModule & vbCrLf & _
                  "Procedure: " & strProcedure & vbCrLf & _
                  "Error: " & Err.Description & _
                  " (" & Err.Number & ")" & vbCrLf & _
                  "Line Number: " & Erl
240   MsgBox strMsg
250   Resume ExitFunction
End Function
 
Public Function TrimNull(ByVal strItem As String) As String
   Dim intPos As Integer
   intPos = InStr(strItem, vbNullChar)
   If intPos > 0 Then
       TrimNull = Left(strItem, intPos - 1)
   Else
       TrimNull = strItem
   End If
End Function

Select allOpen in new window

TestTransferText.mdb
TestTransferTextData.mdb
OPEN-SR-ALL-06-10-09-.txt

Thanks for the help.
Todd

Rats,
I had written a post laying out my results and I lost it by not submitting it before awarding the points.
Lim -
Those 1st 3 lines are the issue. I ran another text using a pipe-delimited text file containing 26 text fields(i.e. "aaaa|bbbb|cccc|dddd|...). I then created a matching table in the database, along with a new import spec. The 'TransferText' worked for both the local and linked files. I then inserted the 1st 3 lines from the original text file into the new one. That caused the linked file error.
I don't really know whether to consider this a bug or a quirk. What I do know is that your suggested method of reading the text file and writing to the table works; and that is what I am going to use for this application.
Thanks again,
Todd

I meant 'Jim'
It's been a long day. I'm going home.

Glad to be of assistance. May all your days get brighter and brighter.