vba ado error

[rhinoman]

In an adp project, I am trying to do 2 table inserts, one to the client or
parent table, then another insert to a related or child table. both tables
are newly created and empty

The first insert works fine, but the second insert gives the following error

INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_ClientSW_Table_ClientTable'. The conflict occurred in database 'CGP',
table 'ClientTable', column 'id'. I

I made i diagram linking the two tables on
ClientTable.id ->ClientSW_Table.ClientId. Does that actually create the
entity relation?

but if I stop the app, and then attempt to do the second write again, using
the same code, it works.

Here is some of the relevant code:
Set cmd = CreateObject("Adodb.Command")
cmd.ActiveConnection = Application.CurrentProject.Connection

strCMD = "Insert into ClientTable(LastName,FirstName) values('" + sLastName
+ "','" + sFirstName + "')"
cmd.CommandText = strCMD
cmd.Execute , nrecs

'get the ClientId value for the newly added record
strSQL = "select max(ClientId) from tbl_CLIENTS"
Set RS = CreateObject("Adodb.RecordSet")
RS.Open strSQL, Application.CurrentProject.Connection, 1 ' 1 =
adOpenKeyset
NewClientRecId = RS(0)
RS.Close
Set RS = Nothing
strCMD = "Insert into ClientSW_Table(ClientId,SWid) VALUES(" +
Str(NewClientRecId) + "," + sSWID + ")"
cmd.CommandText = strCMD
cmd.Execute , nrecs 'This fails


How should I be doing this??
Thanks for any help.
Walter

 

[Sylvain Lafontaine]

Yest, the entity relation has been created by your diagram linking. It's
one of the method of creating a foreign key relationship.

Add an « set nocount ON; » before the first query and a « SELECT @@IDENTITY
AS ID » at the end to retrieve the last inserted identity value. (Won't
work if you have trigger set on the table). Retrieve the @@identity value
(under the ID name here) by opening a recordset instead of doing an Execute
on the command object. Something like:

strCMD = "set nocount ON; Insert into ClientTable(LastName,FirstName)
values('" + sLastName

+ "','" + sFirstName + "') SELECT @@IDENTITY AS ID"

I'm not sure about the use of « ; » here. You can also use newline instead.

For more information, make a search on Google with « "select @@identity" ADO
set nocount ». (Use ADO instead of ADP as keyword.)