ODBC--insert on a linked table failed

[KellyGroom]

Hello

I have an access 2007 database and have migrated all the tables over to SQL 2005 Server Management Studio in order to add security to my database. I used the access wizard for migration via triggers.

One of my main forms is used to add new users or it can also be used to modify any existing user details by first selecting from a combo dropdown in a seperate form. In this main form there is a tabbed control area and each tab contains a linked subform. This all worked fine until I migrated my tables to SQL.

If a user is modified everything still works ok and data can be added or amended in the main form or linked subforms. However when trying to add a new form, data can be added to the main form and first sub form but the moment I click off of the sub form or even try accessing other sub forms (either before or after adding data to the first sub form) I recieve the following error
ODBC--insert on a linked table 'T_User' failed.

I have checked and recreated my relationships in SQL, my database is in a trusted location, all tables have a primary key and I also have foreign keys connecting tables. I have checked in my forms and sub forms that the master and child links are correct and also that the sub forms include the relevant foreign key and finally all data properties are set to Yes for adding or editing records.

I would be grateful for any input or light anyone could shed on this.

Many thanks

Kelly

 

[SQL_Hell]

When you click off, it's probably trying to commit the record, is it possible that you haven't satisfied all of the non nullable columns?

Other than that it's very difficult to determine the cause of the problem with such a vague error message....what does the sqlserver error log tell you?

 

[KellyGroom]

Re: (ODBC--insert on a linked table failed) Failing on Constraints

I checked the sql error logs and there were no logs for my problem.

I have been trying various things and have run sql profiler and have found that I am able to insert data into the 1st form but when I try to enter data into the sub forms it fails. It is failing on constraints and seems as though it is trying to commit the data after each form insert apposed to commiting all data at the end when the overall form is closed.

To elaborate on this, my form F_User is based on the table T_User which has its only primary key and a foreign key linking it to a secretary table, T_Secretary. F_User has a few sub forms, one of which is for recording the secretary details SubF_Secretary. When the secretary is selected the SecID (T_Secretary primary key -Autonumber) is entered into T_User and T_Secretary. This works fine in Access but now the backend is in SQL I am assumuing that F_User tries to commit the inserted data before the sub forms are completed (treating the form and sub forms all as individual transactions) therefore failing as the foreign key field for the Secretary has not been entered in T_User as yet.

My question that I am trying to ask is, how do I get my database to commit the data all at once, after the form and all sub forms have been completed?

Thanks

Kelly

NB: I have sysadmin permissions for SQL.

 

[SQL_Hell]

Hi there

I think you are spot on with your analysis of what the problem is, basically you are going to have to control the transaction now to commit everything at the end and the way to do this is use an unbound form and commit the columns in code using a stored procedure or similiar.

 

[KellyGroom]

Many thanks for your reply. I am afriad that the resolve is out of my realm of experience, SQL and VB programming is an area I've never touched. However, I think I will re-think my design of my form for adding new records to see if I can find an alternative within my level.

Thank you for your help and quick responses.

 

[Banana]

One approach you can look into is to use a pair of temporary tables stored locally in Access and bind your form to the temporary tables. On the form's open event, delete all records from the temporary table, load it with the records your users need to look at (or not at all if it's just data entry). You can then use the form's On Close event or a button's click button to flush the data in temporary tables into the SQL Server as a single atomic transaction, which can be done in code.

HTH.

Also, if you need additional resources: Beginner's Guide to ODBC

HTH.

 

[SQL_Hell]

Banana's option is another good idea, or indeed redesigning your forms could well be the key to getting this right, your main form and subforms need to reflect your tables and relationships... so you don't try to insert data in a different order to the way your relationships are set up.

One thing to remember is sql server auto number fields only update when the record commits which is different to the way access works

 

[KellyGroom]

Thank you both for giving me some more food for thought. As I have no experience of code I tried redesigning my main form for adding new users and have managed to get my form working once again. Once I realised the sequence of what sql was trying to commit I was able to work around it.
Thank you all for your help!
Kelly

 

[SQL_Hell]

No probs, you are more than welcome Thanks for posting back.

Also, well done for getting to the source of your problem by re-arranging the forms, your analysis and thought process has been excellent! kudos to you