Thanks Ken

The SQL identity solution would be ideal when when (and if) we do upsize the proposed app to SQL. However in the mean time the App will be in plain old vanilla VFP and we were planning that the keytable would be accessed using a surrogate key function we found called GETKEY(), which is usually called from the valid condition on the primary key field.

Would acessing and updating this keytable as a view be worthwile at all since nobody elsewhere in the enterprise will ever want to see the contents of this table and it is is never going to be more than 20 small records long so there is unlikely to be any network overhead involved.



>> However what about tables such as the table that keeps all the surrogate key counters? Surely that table must be kept with the application on the server.
>
>I recommend using the SQL identity field feature and scrapping surrogate key table(s) - it works perfectly. if you must (for some reason) keep such a table - it needs to be on the server - but each "next" key should be accessed through a single SQL stored procedure. You would have very little net traffic with this, i.e. passing something small like:
>
>= SQLExec(nConnection, "GetNextKey 'mytable'", 'mycursor')
>
>and getting back just an integer key.
>
>HTH,
>Ken