October 11, 2012 at 4:04 pm
Can the Constraint on the PK column just be turned off, run the Delete query and then turn the PK (auto increment) back on?
Just want to know that SQL won't reseed the numbering after the deleted record.
There are no FK relations. No FK data for the record being deleted. All transactions are done with code - tables do not have relationships.
The problem is that ID_Sundry_Descript is a PK (Clustered) int, not null
Is Identity, YES Identity Increment 1, Identity Seed 1
All other columns allow nulls.
Used the SQL Server Migration Assistant for Access to move over this table with the field of type Autocounter.
DELETE FROM Wells_Sundry_Description
WHERE (ID_Sundry_Descript = 133)
It is a newbie question. Appreciate the help. And would appreciate any TSQL code tips before / after the Delete statement.
October 12, 2012 at 8:31 am
Solved: Just wanted to followup
In SQL Server Management Studio - right click on the Wells_Sundry_Description - choose Object Dependencies
This exposed a table that did indeed contain a FK to the legacy table no longer being used - thus preventing the deletion
The Dependent table was an old Legacy table in MS Access. That table was migrated into a new table being used in production.
The Microsoft SQL Server Migration Assistant for Access functioned as instructed and moved the legacy table, along with the Dependencies over to SQL Server
My Front-End Access 2010 DSN-Less connection is built with a table and code at run-time. There was no link to the old legacy table from Access.
With hundreds of tables, most of the legacy tables were never migrated to SQL Server. Missed this one.
This speaks well of how the Microsoft SQL Server Migration Assistant for Access works to match the tables and constraints.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply