Identity Crisis

I am working with a SQL Server 2005 database that was upsized from a replicated Access 2003 database. We used the SQL Server Migration Assistant for Access 2003.

Because the Access file was replicated, the Autonumber fields were set for Random, as opposed to Increment, so the resulting IDENTITY fields in SQL are spread across the range of an int, which is +/- 2 billion give or take. However, for the SQL database the SSMA set them to Increment, and so new records in some of the tables are approaching the upper limit of an int. Even worse, Access does not recognize bigint which would have been a quick fix; when I tried that all the fields of the table displayed #deleted.

My goal was to find out how much "headroom" there was IOW how many records could be added before reaching the int limit. I found this function that helped to answer my question:

SELECT IDENT_CURRENT('MyTable') + IDENT_INCR('MyTable')

which returns the next identity value for the table. This is not for production, since users can be hitting the db all the time, but it gave me the current highest value. Then, I could calculate how many records could potentially be added to the table. Which in some cases wasn't very much.

Now I need a solution for my identity crisis.