Another Excel/Access question - Runtime Error 3349 - Numeric field Overflow

[minette]

Hi guys - I'm stuck on another Excel/Access problem. When trying to append Excel data to Access, I get the Runtime Error 3349 "Numeric field Overflow" error on the line ".Execute mcstrSQL_INSERT_NEW_DATA_AGY"

The data in Excel is formatted the same as in the Access database, and if I copy the records manually from Excel and paste it into Access, it works.

Help please.......

Const mcstrSQL_INSERT_NEW_DATA_AGY = _
  "Insert into NUPB_TPBSMAGY_Test Select * from Temp"
Sub OPENACCESSTABLE_DEL_INS_RUN_TEST()
    Dim db As DAO.Database
    Dim accApp As Access.Application
    Dim Access_DB As String, DBPath As String
    Dim XLTable As DAO.TableDef, rst As DAO.Recordset
    
    fpath = Worksheets("MACRO").Range("A1").Value
    Access_DB_file = Worksheets("MACRO").Range("B1").Value
    Access_DB = Access_DB_file
    DBPath = fpath & Access_DB_file
    Excel_Path = Worksheets("MACRO").Range("c2").Value
        
    Set db = DAO.OpenDatabase(DBPath)
    With db
    Set XLTable = .CreateTableDef("Temp")
        With XLTable
            .Connect = "Excel 5.0;DATABASE=" & Excel_Path
            .SourceTableName = "AGY_Range" 'This is a named range in Excel
        End With
        .TableDefs.Append XLTable
        .Execute mcstrSQL_INSERT_NEW_DATA_AGY
        .TableDefs.Delete "Temp"
        .Close
    End With
    Set db = Nothing
End Sub

 

[minette]

I have now established that it only errors out on a linked table. Does anyone have any ideas about this please?