Getting an error (3163) on MS Access 2003

Question

The error says "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

The database is supposed to pull in information from the Excel sheet that is passed through to it, and import the information into a recordset of the text field. However, the text field, somewhere down the line, has been limited to 50 characters. How can I change the maximum size of the text field? Thank you for any help

This is just part of the code, and the textfield I'm trying to make larger is "Idea_#", which is the last line of the code i posted

Sub Read_Recommendations() On Error GoTo error_code Dim FD As Office.FileDialog Dim xlapp As Excel.Application Dim xlsheet As Excel.Worksheet Dim xlbook As Excel.Workbook

Dim db As Database
Dim rs As Recordset
Dim sql As String

Dim WP As String
Dim row As Integer

Dim File As String
Set db = CurrentDb

Set FD = Application.FileDialog(msoFileDialogOpen)
If FD.Show = True Then
    File = FD.SelectedItems(1)
    Set xlapp = CreateObject("Excel.Application")
    Set xlbook = GetObject(File)
    Set xlsheet = xlbook.Worksheets("Recommendation Approval Form")

    Dim protection As Boolean
    With xlsheet

        'support unprotected worksheets
        protection = xlsheet.ProtectContents
        If protection Then xlsheet.Unprotect "veproject"

        WP = .Range("WP_Number")
        ' Check that active WP and the WP of the uploading form is the same
        ' If WPs are different, awares users and prompts user whether or not to continue
        Dim DifferentProject As String
        If Not get_WP = WP Then
            DifferentProject = MsgBox("You are uploading to the project with WP number: " & WP & " which is not the active project. Do you wish to continue?", vbYesNo)
            If DifferentProject = 7 Then Exit Sub
        End If

        ' Check that WP is correct by checking if it exists in the Record Information table

        ' delete the existing recomendations, we want to keep the most recent recomendations
        ' perhaps change this to a dialog in the future
        sql = "DELETE * from tbl_recomendations WHERE WP_Number = '" & WP & "'"

        db.Execute (sql)
        row = 8

        Set rs = db.OpenRecordset("tbl_recomendations")
        Do While .Range("D" & row) <> ""
            rs.AddNew
            rs("WP_Number") = WP
            rs("Idea_#") = (.Range("C" & row))

........

HansUp HansUp · Accepted Answer · 2013-09-24T21:47:19

In Access, open tbl_recomendations in Design View. It sounds like the Field Size property for Idea_# is set at 50. You can change that up to 255.

If you need to store more than 255 characters in Idea_#, change its Data Type from Text to Memo.

Getting an error (3163) on MS Access 2003

1 Answers