Mekenzie Buhr Mekenzie Buhr - 1 month ago 14
SQL Question

INSERT INTO statement in Access is cutting off at 255 characters

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

Answer

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 MSDNRecordset object