I have run into an interesting issue that I have yet to resolve.
I use SQL 2000 server as a backend and Access 2003 as a front end. We manufacture products and keep track of the serial numbers is the database. When I create new records and attempt to autogenerate new serial numbers (using VBA), occasionally SQL will not accept them. I get a "ODBC - Update on a linked table '<table name'> failed".
I should point out that the serial numbers are not the primary key. I also have an autoID column as well.
The weird part is that it only locks out a serial number range. For example I can enter any new serial number from 5001234~5999999 with no errors but new serial numbers 1610098~1619999 will generate that error.
The form to enter the data in access is simple and there are no custom record locks being used. This problem appears to come and go every few months and has recently become a bigger problem as it is occurring more frequently.
I believe SQL must have initialted a Range Lock but I dont know how. Does anyone know anything about this?