CREATE TABLE sql code produces "record too large" error

[perry59]

I have an access database with about a dozen tables which is created programmatically.
each of these tables contains several text fields, as I am only putting small amounts of text in these fields I originally defined them as CHAR (which shows up as "short text" in the access editor).
However, when the code runs I get an error saying "record too large" which seems baffling because if I go back and define them as MEMO (which shows up as "long text" in the access editor), it compiles and runs without problem. After the fact, I can go into the access editor and change them all to short text with no complaints, but I need to do this via my code. Can anyone shed some light on this?
Thanks

here is an example of one of the tables:
Try
sql = "CREATE TABLE Wires " _
& "(WireID memo Not NULL CONSTRAINT PRIMARYKEY PRIMARY KEY, " _
& "CableID int, " _
& "WireDefID int, " _
& "SheetID int, " _
& "DocumentID int, " _
& "SheetName Memo, " _
& "WireLength Double, " _
& "Valid YesNo Not NULL, " _
& "Existing YesNo Not NULL, " _
& "Reference YesNo Not NULL, " _
& "Stowed YesNo Not NULL, " _
& "WireName Memo, " _
& "WireDescription Memo, " _
& "WireSignal Memo, " _
& "ShapeName Memo Not NULL, " _
& "DesignatorPrefix Memo Not NULL, " _
& "Designator Memo Not NULL, " _
& "DesignatorSuffix Memo Not NULL, " _
& "DesignatorSegment Memo Not NULL, " _
& "WireColor Memo, " _
& "WireText Memo Not NULL, " _
& "TestCode Memo, " _
& "FromComponent Memo, " _
& "FromTerminal Memo, " _
& "ToComponent Memo, " _
& "ToTerminal Memo, " _
& "InsertedBy Memo, " _
& "InsertedDate Date, " _
& "UpdatedBy Memo, " _
& "UpdatedDate Date, " _
& "IncludeCutList YesNo Not NULL, " _
& "ExcludeFromBom YesNo Not NULL); "




DatabaseOps.executeNonQuery(sql)
Catch ex As Exception
MessageBox.Show(ex.Message & " - " & ex.Source)
End Try

[isladogs]

Short text can include up to 255 characters.
Memo/Long text can handle a much larger character limit...but cannot be searched / sorted.
Only use memo fields where it is essential.

AFAIK, whilst rich text fields can be formatted, the entire field can only be saved in a single font.
Perhaps you know otherwise?

[davegri]

Looks like vb.net, not Access VBA. I haven't coded in vb.net for about 11 years, creating .aspx pages.

I don't know about vb.net but VBA has a limit on the number of continuation lines allowed. You might try breaking this up by halfway down,

sql = sql & (continue with the bottom half).

[CJ_London]

For accdb there is a maximum character limit of 4000 characters per record (2000 for mdb) so perhaps that limit was reached - suggest specifying the field character size rather than defaulting to 255 chars might keep you within the limit.

Memo fields are actually stored in a separate data page and the record only stores a pointer which is only a few bytes which is why you appear to succeed using a memo field

[perry59]

I thought I had made my question clear, apparently not.
yes, I know the difference between "short text" and "long text". I need short text, but get an error when coding as such. I am forced to use "memo" (long text) or I get a "record too large" error when the code executes.
yes, this is coded in vb.net and the length of the create table statement is not an issue.

[orange]

WireID memo Not NULL CONSTRAINT PRIMARYKEY PRIMARY KEY, ????????

[CJ_London]

Looks to me like you don’t read the responses properly. What don’t you understand about specifying 10 chars rather than the default 255?

Or the fact memo fields are stored separately from the record so have a minimal impact on record size? You have 18 memo fields which equates to 4500+ chars if these are just short text.

Suggest in future post what doesn’t work rather than something that does as we are left guessing at what the issue is