?
Solved

len (memofield.text) error when length exceeds 2036 characters

Posted on 2003-03-12
20
Medium Priority
?
485 Views
Last Modified: 2008-02-01
In a procedure(click-event) I store text from a frm-textbox into a table-memofield using an sql-update.
When len(frm-textbox.text) exceeds 2036 characters(including spaces) following error occurs:

"could not update; currently locked by another session on this machine"
The frm-interface closes, record gets not updated.

When first creating the record there is no problem in storing more then 2036 characters into record/table-memofield.
But after creation edits are no longer possible. Deleting characters in the table-memofield, so len < 2037 makes editting possible again, without errormessage.

Any ideas on limitation/errormessage on length memofields, and how to overcome these?
I don't want to use Solution like tbxverslag_on change (too much triggeroverhead)

if len(me.tbxverslag.text)>2036 then
    msgbox "You've reached max.size of textbox"
   ...
0
Comment
Question by:expexcmd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 2
  • +3
20 Comments
 
LVL 58
ID: 8119053
It's not the max size of the text/memo combination that's the problem as it is the length of an SQL string.  That's the limit.

You need to update it with something other then an SQL statement.

Jim.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 8124189
i'm not so sure about that jim...

i did some testing. i was able to successfully use a string of < 32768 characters as an insert statement... for instance, try:

currentdb.execute "INSERT INTO MyTable (MemoFieldName) VALUES ('" & string(32500, "@") & "')"

the above method will work every time...

i've run into a similar, but not the same, problem when accessing records from a sql server using DAO... maybe it's related?

to get the record i would use a recordset and the field(x) property. i found that when accessing linked servers, a dao recordset based in jet will ONLY return 255 characters. no more.  i had to work around it by using a ODBC workspace and connection object.

what i mean (i don't think that was clear enough) was that instead of using what i ALWAYS used:

set rs = currentdb.openrecordset("SELECT LOTS OF STUFF")

i had to do the following
Set ws = DBEngine.CreateWorkspace( , , , dbUseODBC)
Set con = ws.OpenConnection( , , False, )

and the use con.openrecordset

so my "answer" is good luck... no, just kidding... how are you accessing the data? is it a bound form or are you using recordsets?

dovholuk
0
 
LVL 58
ID: 8126930
dovholuk ,

Well I'll be darned if I can find it, but I remember something on the limit of an SQL statement in Access as 2036 characters.

The only thing I can find though is the published limit for SQL server, which is as your stated 32K.

I'll dig some more.

Jim.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 58
ID: 8126937
Well I just looked at A97 specs and it clearly states that you can have 64,000 characters in an SQL statement, so I'm obviously wrong.

I'd like to know what the heck I'm thinking of.

Jim.
0
 
LVL 58
ID: 8126950
expexcmd:

  What version of Access is this?

Jim.
0
 

Author Comment

by:expexcmd
ID: 8127104
Jim,

We use acces2000. I made a temporary fix by blocking more then 2036 characters, but I'm afraid that tomorrow access decides 200 chararcters is tops...
I've read in the Access documentation that when using DAO the limit on characters for a memofield depends on the size of the database. But not a word concerning specific numbers???

Marc
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 8127216
no time now, but jim i'm 99% sure you're thinking of the length of a listbox / combobox that's set to "value list" and not query.

that limit is most definately 2048

cheers,

dovholuk
0
 
LVL 58
ID: 8127248
<<We use acces2000.>>

 Not what I thought what your were going to say, but it still may be the problem.

  Where is the data actually being stored: in a SQL database or in a JET MDB?
 
  If the later, I thinking the overall record size may be a problem.  Is this a table with many and/or large text fields?

Jim.
0
 

Author Comment

by:expexcmd
ID: 8127630
Jim,

in 1 procedure I update 3 tables, all with same problem.
1 table 22 fields + 5 access systemfields
1 table 3 fields + 5 access systemfields
1 table 3 fields + 5 access systemfields

In all 3 tables I have one - the mentioned -  memofield with parameters: null-length  -  NO
unicode-compressions  -  YES
mandatory -  NO

The database itself is a replicated database and has many tables (>30), number of records in the three is  22000 - 1630 - 1730
The largest table is 45 mb/22000 records/22+5 fields
0
 
LVL 58
ID: 8127881
Doesn't sound like anything out of the ordnairy there.  What I thought might be happening is that you were bumping into the page limit.

When JET stores a record, it must fit completly on one page.  For A97 and prior, the page size was 2K.  For A2000 and up, it was increased to 4K because of the unicode taking 2 bytes in place of 1.

The record is broken into two parts; a "fixed" portion and a variable length portion.

 All fields except for Memo and OLE fields are stored in the fixed portion.  The Memo and OLE fields go into the variable portion, which is a seperate storage area.  A 14 or 16 byte pointer (depends on version) is placed in the fixed portion of the record to point to the start of the change.

 So it's easy to fill up the fixed portion of a record and get "record to large".  The fact that your getting an error on such a specific number (2036 vs 2037) seems to indicate your hitting some type of limit.

To pin this down further:

1. Create a new table with an autonumber and a memo field only.

Can you do this:

currentdb.execute "INSERT INTO MyTable (MemoFieldName) VALUES ('" & string(32500, "@") & "')"

 sucessfully as dovholuk suggested?

2. Does the insert error happen on every record in those 3 tables?

3. Are you up to date on the latest JET service packs (were up to #5 for JET 4.0)

Jim.
0
 

Author Comment

by:expexcmd
ID: 8135702
Jim,

1. No problem. Can insert
2. It is not an insert but an update, and yes, it happens on evere update on every record that reaches the limit
3. Our JET: 4.0 sp 5

However, I noticed a remarkeable thing, which lets me to the conclusion that it is a code problem. When I put a STOP on the code where the update executes:

DoCmd.RunCommand acCmdSaveRecord 'saves record with some default fiels/values

'A STOP AT THE NEXT LINE RESOLVES THE PROBLEM, without the stop the error triggers. A doevents does not help here.
CurrentDb.Execute "UPDATE [tbl bezoekrapportage] SET [tbl bezoekrapportage].verslag = '" & Me.tbxVerslag & "' WHERE [tbl bezoekrapportage].bezoekid = " & intBezoek & " AND [tbl bezoekrapportage].behandelaar = '" & strBehandelaar & "' "


It seems like the procedure needs time, given by the stop...?
0
 
LVL 58
ID: 8135795
<<It seems like the procedure needs time, given by the stop...? >>

  That's totally weird.  Try using a dbEngine.Idle dbRefreshCache in place of the stop.

Jim.
0
 

Author Comment

by:expexcmd
ID: 8135927
...Idle dbRefreshCache in place of the stop... did not help
Works great though with a stop-point...!
Without it still the error:
'3188: could not update currently in use by another session on this machine..'
0
 
LVL 58
ID: 8135981
<<
'3188: could not update currently in use by another session on this machine..'
>>

 Ahh! It's actually a locking error.  You need to add some error trapping again and retry the operation.  Something like this:


'Error constants
Const CNT_ERR_RESERVED = 3000
Const CNT_ERR_COULDNT_UPDATE = 3260
Const CNT_ERR_OTHER = 3262
Const CNT_ERR_NO_CURRENT_ROW = 3021
Const CNT_ERR_LOCKED = 3188

 
Function MyFunction() as integer

 On Error goto MyFunctionError

  ' Do stuff...

MyFunctionExit:
 
  ' Cleanup

  Exit function

MyFunctionError:
  'Table locked by another user
    If Err = CNT_ERR_RESERVED Or Err = CNT_ERR_COULDNT_UPDATE Or Err = CNT_ERR_OTHER  or Err = CNT_ERR_LOCKED Then
        intLockCount = intLockCount + 1
        If intLockCount > 5 Then
            MyFunction = False
            Resume MyFunctionExit
        Else
            DoEvents
            DBEngine.Idle DB_FREELOCKS
            lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
            For lngX = 1 To lngWait
                DoEvents
            Next lngX
            Resume
        End If
    Else
      Msgbox "unexpected error"
      MyFunction = False
      Resume MyFunctionExit
    End If

End Function


 
0
 
LVL 58
ID: 8135989
To explain a bit, JET is multi-threaded (the default is 3).  You've got some background operations going on that are not finishing up by the time you get to the next statement.

  You may need to make the intLockCount limit a little higher.

Jim.
0
 

Author Comment

by:expexcmd
ID: 8136627
...You may need to make the intLockCount limit a little higher...

I set the intlockcount limit to 10, (6/7/8/9 did no good) and that helps to reach about 3043 characters, but this is very hard to reach. (long waiting)

When I on the other hand run the procedure again while waiting, it does work but I get error 2467 (object not reachable (I think because frm closed allready)

Solution: run procedure twice...!?
0
 
LVL 58
ID: 8151665
I really don't understand what's going on.  Sounds like that in some way, shape, or form, your stepping on your own toes.

That is your doing something in one place that conflicts with something that's going on else where.

Without digging into a lot more detail, I'm not sure I can provide any more help.

Jim.
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8785553
Hi expexcmd,
This question has been abandoned and needs to be finalized (98 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
LVL 18

Expert Comment

by:1William
ID: 8889010
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, refund points
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 8935536
Per recommendation,

PashaMod
CS Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question