>>>>>>>>>Michel- did you ever get closer to tracking down the cause/solution of this problem? If you remember, I had told you that in a couple of months of operation, I had seen the error twice. Well It is now more like twice daily. Two things have changed- 1. more users, and 2. Network problems. We are currently tracking doen the problems NLMs that are causing the network to bog, but it could take awhile. My errorhandler returns the line containing APPEND BLANK as the offender, so I assume that the real offender is in my stored procedure newid(). Here is the code in the DBC:
>>>>>>>>>
>>>>>>>>>FUNCTION NewID(tcAlias)
>>>>>>>>>LOCAL lcAlias, ;
>>>>>>>>> lnID, ;
>>>>>>>>> lcOldReprocess, ;
>>>>>>>>> lnOldArea
>>>>>>>>>lnOldArea = SELECT()
>>>>>>>>>IF PARAMETERS() < 1
>>>>>>>>> lcAlias = UPPER(ALIAS())
>>>>>>>>>ELSE
>>>>>>>>> lcAlias = UPPER(tcAlias)
>>>>>>>>>ENDIF
>>>>>>>>>lcOldReprocess = SET('REPROCESS')
>>>>>>>>>SET REPROCESS TO AUTOMATIC
>>>>>>>>>IF !USED("idnums")
>>>>>>>>> USE temptest!idnums IN 0
>>>>>>>>>ENDIF
>>>>>>>>>SELECT idnums
>>>>>>>>>IF SEEK(lcAlias, "idnums", "table")
>>>>>>>>> IF RLOCK()
>>>>>>>>> lnID = idnums.nextid
>>>>>>>>> REPLACE idnums.nextid WITH idnums.nextid + 1
>>>>>>>>> REPLACE updated with datetime()
>>>>>>>>> UNLOCK
>>>>>>>>> ENDIF
>>>>>>>>>ELSE && Table name was not found in idnums table. Create a new record and initialize it to 1
>>>>>>>>> APPEND BLANK
>>>>>>>>> REPLACE table WITH lcalias
>>>>>>>>> REPLACE nextid WITH 1
>>>>>>>>> REPLACE updated with datetime()
>>>>>>>>> lnID = idnums.nextid
>>>>>>>>>ENDIF
>>>>>>>>>SELECT (lnOldArea)
>>>>>>>>>SET REPROCESS TO lcOldReprocess
>>>>>>>>>RETURN lnID
>>>>>>>>>ENDFUNC
>>>>>>>>>
>>>>>>>>>If anyone can give me any insight as to why this is happening or how I can prevent it, I would be really appreciative. Every time this particular routine crashes, I have to do a bunch of record maintenance. This is costing me more time than I would like to spend on it.
>>>>>>>>
>>>>>>>>Eric,
>>>>>>>> Instead of append blank, why not use an Insert?? This way you only have to have the header of the table you are adding data to for one shot, as apposed to the append blank, then replace ... series..
>>>>>>>>
>>>>>>>>Thanx!
>>>>>>>>
>>>>>>>>Tony Miller
>>>>>>>>Vancouver, Wa
>>>>>>>Ditto to the above! If two people add a new record at the same time the system can possibly see two blank records which will set off the ref int trigger. By using the "INSERT INTO ..." you will avoid that.
>>>>>>>HTH //:^)
>>>>>>I also see another danger...You need to use something like DO WHILE NOT RLOCK()
>>>>>>instead of IF RLOCK(). If the record is locked by another then the number wont get incremented at at, it will simply bypass that section and go on about its merry way. This, in fact, may be the source of your problem!
>>>>>>Let me know if you agree //:^)
>>>>>My sggestion would be something like
>>>>>
>>>>>STORE .F. TO IS_ABORT
>>>>>DO WHILE NOT RLOCK() AND NOT IS_ABORT
>>>>> WAIT WINDOW 'Waiting for record' TIMEOUT 1
>>>>> IF LASTKEY()=27
>>>>> STORE .T. TO IS_ABORT
>>>>> EXIT
>>>>> ENDIF
>>>>>ENDDO
>>>>>
>>>>>IF NOT IS_ABORT
>>>>> lnID = idnums.nextid
>>>>> REPLACE idnums.nextid WITH idnums.nextid + 1, updated with datetime()
>>>>> UNLOCK
>>>>>ENDIF
>>>>>
>>>>>(and so on...)
>>>
>>>I have changed all the APPEND code to INSERT INTO. I have also changed the stored procedure code to read:
>>>
>>>IF SEEK(lcAlias, "idnums", "table")
>>> IF RLOCK()
>>> lnID = idnums.nextid
>>> REPLACE idnums.nextid WITH idnums.nextid + 1
>>> REPLACE updated with datetime()
>>> UNLOCK
>>> ELSE
>>> MESSAGEBOX("Idnums record is locked by another user. Record could not be added to " + lcalias,48+0+0,"Update Error")
>>> ENDIF
>>>ELSE && Table name was not found in idnums table. Create a new record and initialize it to 1
>>> APPEND BLANK
>>> REPLACE table WITH lcalias
>>> REPLACE nextid WITH 1
>>> REPLACE updated with datetime()
>>> lnID = idnums.nextid
>>>ENDIF
>>>
>>>This has done nothing to help my problem. As of 11:00 am this morning, we have received the "Record is in use by another" error 14 times. Sometimes on one table, sometime on another. (The save routine adds records to 2 different tables).
>>>
>>>The problem is directly coordinated with our network performance problems, which in the past, up until now have been bothersome but tolerable. I can't help but think that the network problem has just served to expose a weakness already existent in the program, but I feel like I have done everything by the book. I will be calling MS soon if I can't get the problem solved here. Thanks for any more insight that you can give.
>>
>>
>>
>>Just a an idea,
>>for debugging purpose also print out the value of REPROCESS in your error message. Maybe it is not set to what you think it is.
>>Let me know if you find something.
>
>That would explain alot, if set reprocess was somehow being reset, but I have reviewed my code to specifically check for that. Nonetheless, I added the value of set('reprocess') to my debugger message and will watch for it tomorrow. (The version on all of my workstations doesn't get updated until morning, so I won't see any changes today) Thanks for the suggestion, and I will let you know.

I had that problem with >>RECORD is in use... in a way that I was not far from "bombing" my Computer. The only way to get around this problem was to redesign my whole Application in a way to

1. strictly use INSERT INTO... instead of APPEND BLANK when adding new records to tables

2.strictly use the buffering-possibilities of VFP to an extend that every form in my application has its own private data-session

3. create a transaction class which manages any transaction on the database (with reprocess, begin transaction, rollback, end transaction, multi level transaction and all the rest)

4.invent a new class called "bicentennial_switchboard" (a SciFi-Film with Mick Jagger was on TV that evening and the main object of that film was a so called "bicentennial_switchboard".) This "board..." enables the communication between any application object (e.g. tables, datasessions, controls and so on) and thus automatically controls data transfer between two private data sessions if necessary.

Now the application works fine even if Users are remotely logged onto the database