INSERT INTO with FOREIGN KEY ???

Hello,

I can't figure out how to manage INSERT INTO in a table having FOREIGN KEY
constraint.

For ex, with ADO, I sent these 2 SQL Commands :

CREATE TABLE Try1(pKTry1 IDENTITY NOT NULL PRIMARY KEY, strValue
nvarchar(10))
CREATE TABLE Try2(pkTry2 IDENTITY NOT NULL PRIMARY KEY, fKTry1 int NOT NULL
strValue2 nvarchar(10), CONSTRAINT fk_Try2_Try1 FOREIGN KEY fKTry1
REFERENCES Try2.fKTry1=Try1.fkTry1)

which create 2 tables with table Try2 having a foreign key.

I populate Try1 with

INSERT INTO Try1(strValue) VALUES('Dummy1')

Now, I need to populate table Try2.

Actually, I use ADO recordset to retreive primary key from Try1.

rstSQL.OPEN "SELECT pKTry1 FROM Try1 WHERE (strValue='Dummy1')"
lngpK = rstSQL("pKTry1").Value

and then

cmdSQL.commandText = "INSERT INTO Try2(fkTry2, strValue2) VALUES(" & lngpK &
",'Dummy2')"
cmdSQL.Execute

This pCode works at the price of an increase in total requests to the server
and implies that the client manages all "string concatenation".

I know that there is a Server side solution to this problem but I don't know
how to manage it with an Access Database (ie no multiline stored procedure).

I try without any success something like

INSERT INTO Try2(fkTry2, strValue2) VALUES((SELECT pKTry1 FROM Try1 WHERE
strValue="Dummy1'),'Dummy2')" => resulting with an ADO error message ???


Any ideas and all help appreciate

TIA


.

Relevant Pages

  • Re: primary key
    ... How do I do this using ADO? ... >> sure you get the primary key from table A after you do an Insert then use ... >> that for your foreign key in table B. The solution is to retrieve ... >>> Geoff ...
    (microsoft.public.dotnet.languages.vb)
  • Re: primary key
    ... How do I do this using ADO? ... Geoff ... > the time you do the Insert into table A and use that for your foreign key ... >> foreign key to a primary key in A. If I add data to both tables and then ...
    (microsoft.public.dotnet.languages.vb)
  • Re: FAQ? factors influencing choice of data type for primary key
    ... >>example, an Autonumber as a primary key, are there ... >>it is a foreign key? ... >don't need to bring the lookup table or any indexes into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Data changes but reverts.
    ... > trust my data to a wizard-built form. ... > 3) What is the primary and foreign key in your relationship ... I did a TABLEUPDATE() and it's now working! ... The symbol field is the primary key in the Symbols table and it is ...
    (microsoft.public.fox.programmer.exchange)
  • RE: Creating an DB for an Office Rota/Skills
    ... Also how do i set up a foreign key? ... primary key because atm i have to type them in eg. 1.2.3.4.5 for each persons ... "Duane Hookom" wrote: ... fields for skills and/or days then you are on your own. ...
    (microsoft.public.access.tablesdbdesign)