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