[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

SQL 2008 RS Limits the number of records it can insert into a table

Hi

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

MLA
0
Adlerm
Asked:
Adlerm
  • 5
  • 4
1 Solution
 
hnasrCommented:
We have:
SQL table
Access form

When, from access form, adding 1 record after 1000 you get the error. But the record is added.
Try adding a record directly to the table, from access. Any problem?
0
 
AdlermAuthor Commented:
Hi Hnasar

Yes - tried that but still experienced the same problem except that instead of getting the message a small red icon appears in the left side of the record. When you move the cursor over the icon it reveals the same message.
0
 
hnasrCommented:
Unable to reproduce the issue, I managed to add more than 1500 records in a table through access project. Tell me more.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AdlermAuthor Commented:
Hi Hnasr

Not a lot more I can add.

I have developed many SQL2008/Access.adp databases with thousands of records added to table(s) without any problems. In one SQL/Access.adp database I imported over 25 million records into a single table.

All I can say is that after I removed a number of records  - to get below the 1000 records  mark - everything work without a hitch. I did this several times (removing 2 or three records so the number of records was under the 1000 count). As soon as  I added new records beyond the 1000 record limit  the application complained and displayed the error message as listed above. In the end I archived half the records from the table and haven't  had any problems since. As only about 20 records are added to the table each day I've brought myself some time before the number of records gets back to the 1000 count. I was hoping to be able resolve this problem before this happens. If it does I'll just keep archiving records until the problem is resolved.
0
 
hnasrCommented:
Try to export the table, drop it, create a new table, and import data back.
0
 
AdlermAuthor Commented:
Hi Hnasr

If only it was that simple.
 
Unfortunately the auto generated Reference Number (Primary Key) is used in other areas of the application (Delivery Dockets) and other applications (Quickbboks - Invoices). If I archive all the records, delete the table, recreate the table and then reimport the archived records they will be allocated a different Reference Number which in turn will cause more trouble as nothing will cross reference if the user needs to query (lookup) the information at a later date. I know I can set the Primary Key to start at a specified number which would be great if all the Reference Numbers in the archived records were in consecutive number order - BUT - they are not due to deletions.
 
Cheers

MLA
0
 
hnasrCommented:
The objective now is to see why the table is not accepting more than 1000 records.

Create a new table from the current one.
Try to add records to the new table.
0
 
AdlermAuthor Commented:
Eventually resolved the problem myself
0
 
hnasrCommented:
May you show your approach in resolving the problem?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now