MS Access 2000 - Trouble with Memo Field - updating over 2048 characters
Rory Jakes
a table. I am using 2 forms. The second form is unbound and after
writing to the table I want to display the first form with changes
made to the second form.
I am having trouble with memo fields. How can I allow the user to call
up a record and then edit that record. Now I want to write that edit
back to the table.
My heart ache is that I can't use a memow field with over 2048
characters and the unbound form and an UPDATE command.
Thanks in advance,
Rory Jakes
Sith Lord
<855c5011.02111...@posting.google.com>, rory...@yahoo.com
(Rory Jakes) wrote:
Hmmmm, I created a form and put 3072 'x's into a memo (sql text) and
saved the record to see what SQL was generated (from Profiler), it
wasn't conclusive.
exec sp_executesql N'INSERT INTO "hardware"."dbo"."TestMEmo"
("WibbleMemo") VALUES (@P1)', N'@P1 text', 'xxxxxxxxxxxxxxxx'
When reading the record back I got 3072 'x's, but executing the same
statement from query analyser produced a record with just 16 'x's,
wierd.
You might try using a ADO or DAO recordset to put the values back.
--
Dim Salary As Currency
Dim Overtime As Double
David W. Fenton
<i170tuc6pbhr7fnjo...@4ax.com>:
There is a bug somewhere between Jet and VBA that can cause
concatenate memo field values to return gibberish. I've worked very
hard on this one, thinking it was a ByRef problem, a limitation of
string variables and any number of other things, but could never
solve it. This current problem might be related, especially if
there's any concatenation being done in code.
I have never had a problem with an unbound memo field in any other
context, however.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Dexter Lieu
We're seeing the same behavior in our applications. While I don't have
an explicit solution, I do have some observations and a possible
workaround which may or may not apply to your situation as well.
I've found that users can add records with more than 2048 characters
in a memo field, but once a record has a memo field with more than
2048 characters, attempts to edit that record result in the "Could not
update; currently locked by another session on this machine" error.
In our case, our applications typically work as follows:
1. user selects search criteria which causes a result set to be
displayed (either as a datasheet, a continuous form or a listbox). In
all cases, the result set is read only.
2. user selects one of the items in the result set to edit which
causes another form to be displayed (sometimes bound, sometimes
unbound) and causes the form showing the result set to be hidden
3. user edits data and clicks a "save" button which updates the record
(either by saving the bound record, or via DAO), closes the edit form
and returns the user to the form displaying the result set.
Recently (in the last week or so), I discovered that if I close the
form that displays the result set, then saving the edited record does
NOT cause the error, even if it contains more than 2048 characters.
This is not an ideal solution since I'd rather not have to requery to
get the result set after the user saves the record or cancels the edit
(the memo field(s) are not displayed in the result set and are not
part of the search criteria, so it shouldn't matter if they're
edited), but so far it seems to work.
For what it's worth,
Dexter.
[remove the NO SPAM from the e-mail address to reply]
Rory Jakes
Dexter,
I am using the same scenario, except I am using an unbound form that
is re-formatted to be used to Add or Edit depending on the user's
choice. The point is that I discovered the same work around with
closing a form that "displays the result set" before writing to a
table, no problem! By the way, I am using DAO recordsets. Thanks for
replying.