Problem adding a record using multiple tables in form 2

[virtualjay]

Hello. I have been using Access for some time, but am very new at using .ADP projects with SQL Server. I have a form which works in every respect, except for when I try to add a record. I am using Access 2002 and SQL Server 2000. I "upsized" my .MDB project. A simplified example will illustrate my problem. I have 3 tables. Each has the same primary key, CLIENTID. Each table has different data about this client, so Table1 has two fields, ClientID (int) and Data1 (varchar). Table2 has two fields, ClientID and Data2. Table3 has two fields, ClientID and Data3. I have set up the relationships, which are simple one-to-one relationships between Table1-Table2 and Table1-Table3. In Table1 I have set ClientID to be an Identity value. I have a form which allows me to view ClientID, Data1, Data2 and Data3. Works great. But when I try to add a new record, I get either "You can't update the record because another user or application deleted it or changed the value of its primary key" or "Invalid input parameter values.Check the status values for details.". I have created exactly the same scenario in an MDB and the form works great. I suspect the problem is related to the timing of when Table1.clientID receives its value. As an "autonumber" field in the .MDB it is displayed as soon as I leave the Table1.data1 field, which is not the case for the SQL "identity" value. I have tried a number of options, all of which have failed so I won't recount them here except to say that selecting any table as the "Unique Table" property of the form has not worked. I also know it appears that I am using three tables, with the same primary key, when it would seem that I might be better off with a single table using ClientID, Data1, Data2 and Data3, but this is not a workable solution for my situation. I need to continue to use the three dinstinct tables, but add information from a single form. I thank you for any help you can provide!

 

[SSapphire]

THis may work.

On the "After Insert" event of your form, manually insert the records.

 

[cmmrfrds]

Normally, in a relational design the primary key in table2 and table3 would not be the primary key in table1, but the primary key in table1 would be a foreign key in table2 and table3. Table2 and table2 would have there own primary keys which can be auto numbers. To be more clear on what you are doing please show the design of each table.

 

[virtualjay]

Originally (as in the .mdb where this works) I did use the ClientID field as the PK in each of the three tables. However, when this did not work in the .ADP with SQL, I did try your suggestion. In my test, I made 3 tables.

Table1 is
ClientID (int, PK, identity),
Data1 (varchar).

Table2 is
Key2 (int, PK, identity),
CLientID (int, FK),
Data2 (varchar).

Table3 is
Key3 (int, PK, identity),
ClientID (int, FK),
Data 3 (varchar).

I set up a secondary index on Table2 and Table3 using ClientID, unique. As such, in the database diagram it correctly shows the one-to-one relationship that I want between these three tables. When I make a simple form that includes all fields it does display any exiting data and it does let me edit the data (note only fields data1, data2 and data3 are the "real" data). However, when I try to add a record I get a "Write Conflict" message. The record for Table1 only is saved. I have the "Unique Table" property of the form set to Table1. What seems a bit strange to me is that if I set the Unique Table form property to either Table2 or Table3 and the results when adding a record are vastly different, even through the relationships between Table1-Table2 and Table1-Table3 are identical. I am just getting back to this this morning and will look at SSapphire idea also. Regards.

 

[SSapphire]

Please check out the following URLS:

http://support.microsoft.com/default.aspx?kbid=295225

http://support.microsoft.com/?kbid=837937

Do you get the "Write Conflict ...The record......Save Record (btn), Copy to Clipboard (btn), Cancel Changes (btn)" message?

 

[virtualjay]

Thanks for the ideas, and the answer to your question is "it depends" (wouldn't you know it). If the form datasource is a SELECT statement, then yes, this is the error message. If the datasource is a SP that selects the data, then the error message is the "You can't update the record because another user or application deleted it or changed the value of its primary key". Note that I am now working with the three tables modified as cmmrfrds suggested, where Table2 and Table3 have their own identity PK fields and the ClientID field is a FK to relate back to Table1. I also tried making Table1.ClientID NOT an identity field (as suggested in one of your links) and instead entering a value manually. The error messages for the SELECT and SP datasources are the same as above. Also, I am running SP3. I would seem to me that I should be able to accomplish this without needing to use subforms, don't you think? It sure was straightforward with an .MDB using DAO....

 

[cmmrfrds]

In sql server you can select from multiple tables but only 1 table in a update statement. The other 2 tables would need separate update statements, which is what the linked subform would do. If you don't want to go with the subforms then make 3 separte update statements and put then behind the beforeupdate event on the Form. Since this would be done through ADO you could wrap then within a begin transaction and commit transaction to make 1 logical transaction.

 

[virtualjay]

I thought that this was one of the improvements offered by the CDM in Access 2002 - the ability to modify all fields in a multi-table view. Then again, I guess I CAN modify the fields, it is only adding (insert) that causes the problem. Also, I have tried and found that I can get this to work without subforms if I am only working with two tables, but it seems to fall apart with three. The reason I am so stubborn about using subforms in this instance it that my example is a very simplified from the actual project, does include 3 similar tables, but with also about 120 other fields plus another (as yet unmentioned) one-to-many joined table which I AM using as a subform. Since this is an existing project being upsized from a .MDB I need to be able to retain the design of the form for the sake of the users - this would just not be possible using subforms because fields from any one table are not necessarily grouped together, but may be spread between the main form and a tab contol. Using an unbound form may be my only option, which make me think I should just skip Access and go straight to VB...... I will keep digging and see if I can find a way to make this work and will return to this post. Thanks so much for your input!

 

[cmmrfrds]

My expierence is with Access 2000. So, new functionality in Access 2002 allows something akind to the dynaset in Access Jet. That sounds good.

How are you joining the 3 tables. Inner Joins on all tables? If so, do you get the same results on a left join from table1 to the others?

 

[virtualjay]

What I really want is to use a left join on Table1-Table2 and an inner join on Table1-Table3. But in the sake of criosity I have tried all inners and all lefts, the results being the same. But the ORDER in which the joins are specified in SELECT statment of the recordsource for the form does have some bearing. If I set the "Unique table" property to Table1, only Table1 data is saved. If I set the "Unique table" property to the LAST table joined in the SELECT statement, the record for Table1 and that last specified table are saved. If I set the "Unique table" property to the FIRST table joined in the SELECT statemnt, no data is saved. In each case the error is also different. I KNOW it's trying to tell me something, but I just don't know what it is. It is interesting, but boy am I wasting a lot of time.... Any more ideas?