INSERT INTO statement in Access is cutting off at 255 characters

Question

I have a form in Access where users are able to type information in a textbox and hit a button that would run an INSERT INTO statement. Even though I have formatted some of my data types to be Long Texts I'm still getting a run-time error 3271. I would like the user to be able to type more than 255 character into the textbox in the form and have that data to insert a new row in my table:

    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim qdf As DAO.QueryDef
    Set qdf = cdb.CreateQueryDef("", _
            "INSERT INTO AllProjects (ProjectNumber, ProjectTitle, Description, Department, Priority, Status, [Create Date], [% Complete], [Update Notes], Leader, [Initial Target Date], [Complete Date], Category, Feedback, [Strategic Initiatives], Subcategory, [Internal/External], [Case #], Alloy, Goals, Customer, [Market Segment], [Result of Corrective Action], [Initiator], [Representative / PM / CSA], [Team Memebers], [FQA Needed], [WON / LOST / DUNNO]) VALUES (@prjnum, @title, @description, @dept, @prior, @status, @createdate, @ctlcomp, @notes, @lead, @targdate, @compdate, @cat, @feedback, @initiatives, @sub, @internal, @case, @alloy, @goals, @customer, @mktseg, @result, @initiat, @rep, @team, @FQA, @wonloss)")
    qdf.Parameters("@prjnum").Value = ProjectNumber
    qdf.Parameters("@title").Value = Me.Title
    qdf.Parameters("@description").Value = Me.Description
    qdf.Parameters("@dept").Value = Me.Department
    qdf.Parameters("@prior").Value = Me.Priority
    qdf.Parameters("@status").Value = Me.Status
    qdf.Parameters("@createdate").Value = Me.Create_Date
    qdf.Parameters("@ctlcomp").Value = Me.Ctl__Complete
    qdf.Parameters("@notes").Value = Me.Update_Notes
    qdf.Parameters("@lead").Value = Me.Leader
    qdf.Parameters("@targdate").Value = Me.Target_Date
    qdf.Parameters("@compdate").Value = Me.Complete_Date
    qdf.Parameters("@cat").Value = Me.Category
    qdf.Parameters("@feedback").Value = Me.Feedback
    qdf.Parameters("@initiatives").Value = Me.Strategic_Initiatives
    qdf.Parameters("@sub").Value = Me.Sub
    qdf.Parameters("@internal").Value = Me.Internal
    qdf.Parameters("@case").Value = Me.Case
    qdf.Parameters("@alloy").Value = Me.Alloy
    qdf.Parameters("@goals").Value = Me.Goals
    qdf.Parameters("@customer").Value = Me.Customer
    qdf.Parameters("@mktseg").Value = Me.Market_Segment
    qdf.Parameters("@result").Value = Me.result
    qdf.Parameters("@initiat").Value = Me.Initiator
    qdf.Parameters("@rep").Value = Me.RepresentativePMCSA
    qdf.Parameters("@team").Value = Me.TeamMembers
    qdf.Parameters("@FQA").Value = Me.FQANeeded
    qdf.Parameters("@wonloss").Value = Me.WonLostDunno
    qdf.Execute

Possible duplicate of [VBA - Run Time Error 3271 using DAO object](http://stackoverflow.com/questions/37049626/vba-run-time-error-3271-using-dao-object) — Zev Spitz, Nov 07 '16 at 05:56
@ZevSpitz I've seen this. I'm new to this however, could you break it down more so I understand how to implement this solution to my code? Thank you. — Mekenzie Buhr, Nov 07 '16 at 06:00

Zev Spitz · Accepted Answer · 2016-11-07T06:39:56.363

You can work around this by inserting the record with DAO objects, instead of executing an SQL statement. Something like this:

Dim rs As DAO.Recordset
Set rs = cdb.OpenRecordset("AllProjects")
With rs
    .AddNew
    !ProjectNumber = ProjectNumber
    !ProjectTitle = ProjectTitle
    '...
    .Update
End With

DAO Reference on MSDN — Recordset object

It seems that ADO doesn't have this issue, even when executing SQL statements.

You'll need to add a reference (Tools -> References...) to the Microsoft ActiveX Data Objects library (choose the highest version; on my machine it's 6.1).

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.AccessConnection
'query shortened for brevity
cmd.CommandText = _
    "INSERT INTO AllProjects (ProjectNumber, ProjectTitle, Description) " & _
    "VALUES (?, ?, ?)"
cmd.Execute , Array(ProjectNumber, Me.Title, Me.Description)

ADO Reference on MSDN

@MekenzieBuhr Using ADO (an alternate library that can be used to connect to Access databases) seems to work with SQL. See my updated answer. — Zev Spitz, Nov 07 '16 at 06:40

Ghost Developer · Answer 2 · 2016-11-07T05:31:00.103

0

for a text field in access, maximum limit is 255 characters. you will not be able to insert more than 255 characters. look at max allowed characters and use as per requirement. try using a Memo field (Number of characters in a Memo field 65,535.)

edited Nov 07 '16 at 05:31

answered Nov 07 '16 at 05:12

Ghost Developer

1,283
1
10
18

Number of characters in a Text field: 255 Number of characters in a Memo field: 65,535 when entering data through the user interface;1 gigabyte of character storage when entering data programmatically – CustodianOfCode Nov 07 '16 at 05:29
I'm using Access 2016 so yes I have the data type set to "Long Text" which is the same as a Memo field now [The-Memo-data-type-is-now-called-Long-Text](https://support.office.com/en-us/article/The-Memo-data-type-is-now-called-Long-Text-dffe5e34-953e-4451-a05e-fba5d9b564b5) – Mekenzie Buhr Nov 07 '16 at 05:43
@GhostDeveloper This did not fix my problem I already had the field set to memo/long text. – Mekenzie Buhr Nov 07 '16 at 05:54
can you please post the error details (error text + code)? – Ghost Developer Nov 07 '16 at 05:58

INSERT INTO statement in Access is cutting off at 255 characters

2 Answers2