Re: Two Tables Insert Into with Select @@ Identity

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hmmmm.... Sorry, I wasn't clear. I meant you're using SQL Server as a
back-end (the data store) instead of JET (Access' default DB engine),
but, still using MS Access to access the DB. If you're using SQL Server
as the back end your front-end (FE) will have .adp as a file extension.
You can also just be linking SQL Server tables to an .mdb Access file.
If you're linked to SQL Server or are using an .adp FE you can still use
the SP I demonstrated in my previous post. NOTE: I made an error in the
INSERT commands - you can't enter values into an AutoNumber column w/o
turning on the IDENTITY_INSERT before running the INSERT commands. You
may not want to do this - IOW, let the Identity function work w/o
interference.

-- Allow identity value to be inserted
SET IDENTITY_INSERT table_name ON

INSERT INTO table1 .... etc.

If you're using straight JET you can use VBA to open a Recordset to
insert the data & use the .LastModified property to get the last created
AutoNumber.

I use DAO so something like this (this goes in a Form's VBA module):

Private Sub cmdOK_Click()
' This is the Click event procedure for the CommandButton named "cmdOK"
' It needs the variables that will go into the tables to be in the
' form's Controls. I'll leave that for you to figure out.
' HINT: Me!rec_nbr Me!pin_nbr
'

const TBL1 = "SELECT old_id, rec_nbr, pin_nbr, birth_date " & _
"FROM table1"

' ID_Field will hold the AutoNumber value obtained from entering
' data into table1.
const UPD2 = "INSERT INTO table2 (ID_Field, field2, field3, " & _
"field4) VALUES ("


dim db as dao.database, rs as dao.recordset
dim strSQL as string

' this will hold the just inserted @@Identity
dim intNewAutoNumber as integer

set db = currentdb
set rs = db.openrecordset(TBL1)

with rs
.addnew
' if the old_id is the AutoNumber column
' comment out the next line - AutoNumber columns
' can't be loaded when there already is data in the tbl
!old_id = intID
!rec_nbr = intRecNbr
!pin_nbr = intPIN
!birth_date = dteDOB
.update
.Bookmark = .LastModified

' be sure to use the name of the AutoNumber field in table 1
' on the right side of the assignment statement
intNewAutoNumber = !AutoNumberFieldName

end with

' Run the 2nd INSERT INTO command.
' Add the variables to the VALUES clause
' -- be sure to put in the appropriate delimiters for
' strings and dates. Ex, if field2 was a string:
'
' ... & ",'" & strField2 & "'," & ...

' change variable names to whatever they should be in real life
strSQL = UPD2 & intNewAutoNumber & "," & intField2 & "," & _
intField3 & "," & intField4 & ")"

' just run it
db.Execute strSQL, dbFailOnError

' put in some error handling

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSe00BYechKqOuFEgEQJ2tACgwKRh31qp+mqL5wzkARtMUdvs1wAAoMqi
9H4TZyLPgunDA4tIN0lFnYTW
=IzgL
-----END PGP SIGNATURE-----


ecwhite wrote:
Sorry, I should haVe said that i am using MS Access. sorry for that and I look forward to your helping me with this problem.

Thanks ecwhite.

"MGFoster" wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Using @@IDENTITY implies you're using SQL Server, or Express; therefore,
why don't you just make a stored procedure (SP) to do the inserts - pass
the Form values as the SP's parameters. In the SP you would pick up the
@@IDENTITY value after the first INSERT INTO command and you could use
it in your second INSERT INTO command:

CREATE PROCEDURE UpdateTable1 (
@old_id INTEGER,
@rec_nbr INTEGER,
@pin_nbr INTEGER,
@birth_date DATE,
- -- If field2-4 are the same as the above parameters just ignore
- -- the following parameters, else put in the proper names & data types.
@field1 INTEGER,
@field2 INTEGER,
@field3 INTEGER,
@field4 INTEGER)
AS
DECLARE @LastID INTEGER

INSERT INTO table1 (old_ID, rec_nbr, pin_nbr, birth_date)
VALUES (@old_ID, @rec_nbr, @pin_nbr, @birth_date)

SET @LastID = @@IDENTITY

INSERT INTO table2 (auto_incrementid, field2, field3, field4)
VALUES (@LastID, @field2, @field3, @field4)

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSezcMIechKqOuFEgEQLugQCg4tUYtIvudxSQE13SiPZ8fL5BawcAnAh8
uYvVgoyuCDBhbWScPPtc08vi
=FZaG
-----END PGP SIGNATURE-----

ecwhite wrote:
Hello,

I need help with the sytax to insert into two tables at the same time with a select @@ identity statement. I have the step one of the clause working but need help getting the autoincrement Id and then inserting into the second table e.g

INSERT INTO table1 ( old_ID, rec_nbr, pin_nbr, birth_date)
VALUES (Forms!frm1!txt_old_ID,Forms!frm1!txt_rec_nbr,Forms!frm1!txt_pin_nbr,Forms!frm1!birth_date)

I need to Select @@ Identity and then insert it as the unique Id for table2

INSERT INTO table2 ( auto_incrementid,field2, field3, field4)
VALUES (auto_incrementid,Forms!frm1!field2,Forms!frm1!field3,Forms!frm1!field4);

How do I abort if table1 insert succeds and for some reason table2 insert fails?
.

Loading