Link to home
Start Free TrialLog in
Avatar of expexcmd
expexcmd

asked on

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

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"
   ...
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

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.
Avatar of dovholuk
dovholuk

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
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.
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.
expexcmd:

  What version of Access is this?

Jim.
Avatar of expexcmd

ASKER

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
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
<<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.
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
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.
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...?
<<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.
...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..'
<<
'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


 
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.
...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...!?
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.
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
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
ASKER CERTIFIED SOLUTION
Avatar of PashaMod
PashaMod

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial