>Many thanks to all who have helped me with this problem. I think I've nearly solved it.
>
>To minimise SQLEXEC calls I now pass a batch of SQL commands to the back end (ie. has the same effect as a stored procedure) as follows...
>
>

>FUNCTION NewId
>LPARAMETER tcIdField                     && field or key to increment
>
>LOCAL lcFieldName, lnConnHandle, lnNewId, lnOrigArea, lnRetVal
>lcFieldName = UPPER(tcIdField)
>lnOrigArea = SELECT(0)                   && area to return to
>lnConnHandle = DefaultConnection()       && get connection handle
>
>IF lnConnHandle<0
>	RETURN -1
>ENDIF
>
>TEXT TO lcSQLCmd TEXTMERGE NOSHOW
>	DECLARE @newId INT
>	
>	WHILE (1=1)
> 	BEGIN
>   	UPDATE UniqueId
>		 SET @newId=nLastNumber+1, nLastNumber=nLastNumber+1
>		 WHERE UPPER(cFieldName)='<<lcFieldName>>'
>		
>		IF @@ROWCOUNT>0
>		BEGIN
>			BREAK
>		END
> 	END
> 	
>	SELECT @newId AS nNewNumber
>ENDTEXT
>
>=SQLEXEC(lnConnHandle,lcSQLCmd,'NewIdTemp')
>
>=SQLDISCONNECT(lnConnHandle)
>SELECT (lnOrigArea)             && restore original area
>
>RETURN NewIdTemp.nNewNumber
>

>
>This is now very fast, but when I test the function in a tight loop to add 10000 new ids, SQL gives-up after about 4000 additions, with the error message...
>
>Connection failed:
>SQLState: '01000'
>SQL server Error: 52
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Open
>(Connect())
>Connection failed:
>SQLState: '08001'
>SQL server Error: 17
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
>or access denied
>
>After pressing OK the process asks me for SQL login details (how can it 'forget' them?) then continues to add another 4000 or so records before crashing again!
>
>The SQLStates in question are 01000 General warning, 08001 Unable to connect to data source. Does anyone know of a good resource listing the SQL Server errors?
>
>Can anyone see anything wrong with my code, or have any other ideas what might be causing this problem?
>
>TIA,
>
>Alan

you want to complicate the simple things.
if you don't check the return value of SQLEXEC() the program cannot work to along

FUNCTION NewId
LPARAMETER tcIdField                     && field or key to increment

LOCAL lcFieldName, lnConnHandle, lnNewId, lnOrigArea, lnRetVal
lcFieldName = UPPER(tcIdField)
lnOrigArea = SELECT(0)                   && area to return to   
lnConnHandle = DefaultConnection()       && get connection handle

IF lnConnHandle<0
	RETURN -1
ENDIF

TEXT TO lcSQLCmd TEXTMERGE NOSHOW 
	DECLARE @newId INT

        SET LOCK_TIMEOUT 5000
   	UPDATE UniqueId
		 SET @newId= nLastNumber = nLastNumber+1 
		 WHERE UPPER(cFieldName) ='<<lcFieldName>>'

	SELECT @newId AS nNewNumber
ENDTEXT

PRIVATE nNewNumber
IF SQLEXEC(lnConnHandle,lcSQLCmd,'NewIdTemp')=1
     nNewNumber = nNewNumber
     USE
ELSE
     nNewNumber   = -1
ENDIF

=SQLDISCONNECT(lnConnHandle)

SELECT (lnOrigArea)             && restore original area

RETURN m.nNewNumber