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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,669
Messages
5,597,474
Members
414,145
Latest member
lonnie451

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