--- In [email protected], "johndoematrix" <johndoemat...@...> wrote:
>
> hi i know this is not a flex question but is related to flex anyway. i have
> an access database with two tables A and B. the two are related in that the
> primary key of A(which is autonumber) is related to the foreign key in B. not
> i would like that when i insert data in the two tables A and B the foreign
> key of B is set to equal to the auto generated Primary key of A. How can i do
> this with coldfusion. i dont wanna do it in access itself but in the
> coldfusion query. thanks
>
You probably have two things that you need to do in B. First, unless you are
just "moving" an existing record in B so that it points to a different record
in A than it did originally, you are going to need to CREATE that record in be.
The second thing you'll need to do is to associate that new record in B with
the record in A, but you can actually do this in the INSERT statement to Table
B.
There is no "ordinary" SQL syntax that will allow this to happen, and Access
does not support T-SQL. Even if it did, T-SQL is stored in and run within the
database, and you said that you want to do this solely from within ColdFusion.
So what you'll need is something like this:
"INSERT INTO tableA (field1, field2, field3) VALUES ('a', 'b', 'c')"
"SELECT keyField FROM tableA WHERE insertDate = (SELECT MAX (insertDate) FROM
tableA)
"INSERT INTO tableB (field1, field2, foreignKeyfield) VALUES ('x', 'y',
valueReturnedFromPreviousQuery)"
Note I have no idea how to concatenate in CF, so you'll need to concatenate in
the variable there, rather than just sticking it in like I did.
HTH;
Amy
