SQL database query

[deadeyedic30]

Posting this for a friend,
I'm in the process of converting a Microsoft Access database to work with a SQL database instead. The SQL database has already been created and I can create new and modify records through the Access front-end but when I go to delete/rename/copy I get an error 'it didn't add 1 records to the table due to key violations'. This, I'm assuming, is because I need to do cascade updates?? I don't know how to go about this."

 

[happytechie]

you need to cascade deletes not updates , the copy will fail because access understands this as a copy of an existing record (and an existing key) the SQL Server database sees it as an insert operation and throws a duplicate key error.

There are lots of ways round the problem but you'll need to give us more information about the design of your form and the design of your database before anyone can help.

How are you managing keys (and FK constraints) in the SQL Server database? How did you manage keys and FK constraints in the access database.

HT

 

[deadeyedic30]

What exact information do you need? I didn't design either the Access database nor the SQL database. It's quite a small database with only 26 tables, 8 of these are the only ones used per transaction and the rest are never edited through the Access front-end. I was to just change the queries and forms to point to the linked SQL tables instead of the previous Access tables. I have got copy working now (the first two tables were being created in wrong order) so it's just the delete and rename that are giving me that error. Any help would be greatly appreciated as I have hit a dead end with it!!