Identifying locked records in table

[Arkadi]

Hi Forum

I have a number of excel workbooks that help us in production and post data into various databases via SQL commands. One of these is a 4D database, which normally is used via its own client. However, I do some updates to these tables from time to time. The problem is, I have not found a way of checking whether the record I need to update is locked, so once in a while these updates return an error. However, it is very generic... "Server rejected the connection" which happens with the 4D write database whenever there is a problem, syntax or otherwise. Thus I can't use the error to trap the condition that the record is simply locked.
Does anyone have any suggestions on how to check whether a record in a table is locked, and if possible by who?
I appreciate any suggestions.

[JieJenn]

Maybe create a UDF to check if a cell is locked, then loop all the records on a table.

Please Login or Register  to view this content.

[Arkadi]

JieJenn, thanks, but while I can modify the records and values in the database, I cannot change its structure, and the database is part of our Job Managment Software. My problem is that when the normal software users are using records through the client program, I cannot run my updates. My programs are addons to the software that drives our production system.

[JieJenn]

Ah. Tough situation. Honestly, without all the details I don't have an answer. Hopefully other experts can jump in provide some insights.

[Leith Ross]

Hello Arkadi,

If you are wanting to check the SQL record status of a Recordset, you would need to check 2 or 3 properties of the recordset: LockType, State, and/or Status. I don't know of any shorter method. Perhaps your SQL version does have something though.

[Arkadi]

Thanks Leith, my biggest problem there is that the odbc driver to connect to the 4D database is not great, and my recordsets will not allow me to write data. When reading I use recordsets a lot, but given their limitations, in this application I use the execute method to post information rather than a recordset update. Because the rs is so limited, I can't get a lot of useful status info out. I was hoping someone here might know a command to actually query the db as to the lock status of the record in question.