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

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
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....... :)

Code:
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
I have now established that it only errors out on a linked table. Does anyone have any ideas about this please?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,874
Messages
5,834,170
Members
430,262
Latest member
JWDeiley

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top