MS access updating to SQL Server Identity Column potential weakness

[munkee]

Hi All,

For nearly a year now I have been migrating over various local access databases over to SQL server back end. Access purely acts as a simple front end to display the data.

I have realised in one of the latest applications I am converting that maybe.. well.. quite possibly I have a bit of a flaw in my logic for updating the sql server back end with any new data from the front end.

When opening up a detailed view of a top level record, which will contain multiple sets of data from other tables that can be edited/added/deleted to I download all of the relevant records related to that top level ID number. This works perfectly fine. I then allow all the edits the user wants to make on the local cached data which is held in local tables in MS Access.

When it comes to the uploading all of this possibly changed data I simply run an update query on the parent record (unless it is deleted) and from there I clear out all the old data on the sql server in the child tables. Once this is done I bulk upload all the local child table data up to the server to hang off the parent ID number.

The above also works fine, however.. there are auto incrementing identity columns. These will be ever increasing as I just wipe out data and upload new. I am worried that of course eventually I may well hit the 20odd million limit of my int field and crap everything out.

After reading:

http://msdn.microsoft.com/en-us/libr...v=sql.90).aspx

I quote: "First, all existing rows matching the OrderID are deleted from the Order Details table. This is more efficient than attempting to determine which order details have been changed, inserted, or edited since the last update."

Microsoft use the exact method as me.. but they do not seem worried. Should I be? Is this as best practice as you can get? I know by trying to send a connection up to the server every time an ounce of data is changed would make things very accurate but also prone to breakage so this bulk upload method seemed to fit the bill fine but the increment values getting larger and larger is getting me worried.

[ck9663]

I won't question your technique of doing it. If you have another primary key other than the identity column and you're emptying the table, use truncate instead of delete. Truncate re-initialize the identity column.

Good Luck!!!


~~ CK

[munkee]

Thank you for the reply CK a TRUNCATE would not quite fit in this situation as I am deleting using a WHERE clause so I only move related data in and out of the table. I do not mind having my method questioned I am always on the look out for improvement as I am probably quite poorly self taught with a lot of Frankenstein methods to get the job done :-p