H
Hugo Kornelis
Hi all,
Using Access 2003 (Dutch version, so I hope I have translated all terms
to their correct English equivalents) as a front end for a SQL Server
2005 database, I run into a small issue. I can't give the exact scenario
due to intellectual property, but I'll demonstrate by using a similar
scenario.
Let's say I have a table for "things". Each thing can have a long
description, which usually spans multiple lines. Because the lines have
to be stored seperately, an extra table is needed for this. The design
(in SQL Server syntax; I don't know the syntax of Access' CREATE TABLE
statement) is like this:
CREATE TABLE Descriptions
(ThingID int NOT NULL,
LineNumber int NOT NULL,
Decription varchar(120) NOT NULL,
PRIMARY KEY (ThingID, LineNumber),
FOREIGN KEY (ThingID) REFERENCES Things(ThingID),
CHECK (LineNumber > 0)
);
The contents of this table would look like this (use fixed font to
view):
ThingID LineNumber Description
------- ---------- ------------------------------------------------
1 1 First line for thing #1.
1 2 Second line for thing #1.
1 3 Third and last line for thing #1.
2 1 Thing #2 has a single-line description.
4 1 Thing #3 had no description,
4 2 but thing #4 does have one of two or more lines.
(...)
In Access, I have built a form for entering and maintining information
about things. This formm is bound to the dbo_Things linked table. The
form also holds a large, unbound textbox "Description". This textbox is
filled in the form's OnCurrent event, by a sub that queries the
Descriptions table and concatenates all lines together, seperated by
CR/LF characters (chr(13) + chr(10)).
New descriptions often come from .txt files. The users can copy and
pastte the contents of such a file in the Description box, or they can
manually enter a new description or change the existing description. To
save these changes back to the database, the OnExit event of the textbox
contains code to delete all existing descrption lines of a thing, parse
the contents of the textbox (seperating lines at each CR/LF), and store
the new description into the Description table.
This all works fine - except for those few things that need a REALLY
long description. At some point (and I don't know exactly where; I only
know that an 8 kB file does not pose a problem and a 43 kB file does),
Access will not allow me to change the contents of the textbox, even
though it still displays the complete description fine if I use some
other way to get it in SQL Server first. I don't think it's related to
the code, as I get the error message (roughly translated: "the text is
too long to be processed") directly when I try to change the contents,
not when I move the cursor outside the textbox.
Is this a known limitation of Access 2003? And is there any way to work
around this, or do I have to keep using my current, very awkward and
clumsy, workaround for changing those very large descriptions?
Using Access 2003 (Dutch version, so I hope I have translated all terms
to their correct English equivalents) as a front end for a SQL Server
2005 database, I run into a small issue. I can't give the exact scenario
due to intellectual property, but I'll demonstrate by using a similar
scenario.
Let's say I have a table for "things". Each thing can have a long
description, which usually spans multiple lines. Because the lines have
to be stored seperately, an extra table is needed for this. The design
(in SQL Server syntax; I don't know the syntax of Access' CREATE TABLE
statement) is like this:
CREATE TABLE Descriptions
(ThingID int NOT NULL,
LineNumber int NOT NULL,
Decription varchar(120) NOT NULL,
PRIMARY KEY (ThingID, LineNumber),
FOREIGN KEY (ThingID) REFERENCES Things(ThingID),
CHECK (LineNumber > 0)
);
The contents of this table would look like this (use fixed font to
view):
ThingID LineNumber Description
------- ---------- ------------------------------------------------
1 1 First line for thing #1.
1 2 Second line for thing #1.
1 3 Third and last line for thing #1.
2 1 Thing #2 has a single-line description.
4 1 Thing #3 had no description,
4 2 but thing #4 does have one of two or more lines.
(...)
In Access, I have built a form for entering and maintining information
about things. This formm is bound to the dbo_Things linked table. The
form also holds a large, unbound textbox "Description". This textbox is
filled in the form's OnCurrent event, by a sub that queries the
Descriptions table and concatenates all lines together, seperated by
CR/LF characters (chr(13) + chr(10)).
New descriptions often come from .txt files. The users can copy and
pastte the contents of such a file in the Description box, or they can
manually enter a new description or change the existing description. To
save these changes back to the database, the OnExit event of the textbox
contains code to delete all existing descrption lines of a thing, parse
the contents of the textbox (seperating lines at each CR/LF), and store
the new description into the Description table.
This all works fine - except for those few things that need a REALLY
long description. At some point (and I don't know exactly where; I only
know that an 8 kB file does not pose a problem and a 43 kB file does),
Access will not allow me to change the contents of the textbox, even
though it still displays the complete description fine if I use some
other way to get it in SQL Server first. I don't think it's related to
the code, as I get the error message (roughly translated: "the text is
too long to be processed") directly when I try to change the contents,
not when I move the cursor outside the textbox.
Is this a known limitation of Access 2003? And is there any way to work
around this, or do I have to keep using my current, very awkward and
clumsy, workaround for changing those very large descriptions?