Can anyone give me a reason (and a way to overcome the problem) as to why SQL 2008 R2 / Access.adp may limit the number of records it can insert into a SQL 2008 R2 table - lets call it "tblJobs".
The number of records in tblJobs is not large - 1000 but when we try to add the 1001 record we keep getting a message "The data will be stored in the database but won't be displayed in the form as the data does not conform to the underlying data structure". (I'm sorry I can't remember the exact error number and/or the exact wording of the error message as I'm away from the clients office and I'm relying on memory)
Looking in the backend database tblJobs table the 1001 record has been inserted. I'm not exceeding the maxium Primary Key as this is set to Bigint and contains the number 50797 while the MS Table Max Records property for the table tblJobs is set to 100000.
After trying a number of things (checking Primary Key / MS Table Max records limits etc) I was struck by the exact number of 1000 records and figured out that this could be a maximum limiting number. I then archive off about half the number of records in table tblJobs into an other table and low and behold when I attempted to add a new record into tblJobs table everything worked correctly - no error messages
I'm at a loss as to why this is happening to this specific tblJobs table as other tables in the database have 10 times the number of records as that of tblJobs.
Researching the problem on the internet indicates it is evident that an SQL 2008 R2 table can hold millions of records so 1000 records is a drop in the ocean.
I'd appreciate any help to resolve this problem. Archiving data from the tblJobs table is a very short term solution and not appropriate over the medium to longer term.
Many thanks in advance