Inserting records when linked (associate) tables are involved?

[cschroe2]

I am using SQL Server, VisualBasic 6.0 (ActiveX DLL components), MTS, and ASP for a web community development project. Here's my problem: I have a members table, an addresses table, and a linking table called MembersAddresses. This way my data is fully normalized. A member can have more than one address and one address can be linked to more than one member. How do I write a routine (in a VB Class Module) which will insert a new address and insert a new record in the MemberAddresses table to link the new address to the appropiate member who owns the address? In other words the new address needs a corresponding new record in the MemberAddresses table so it can be linked to a member.

Here are the tables:

Members
---------
MemberID
Name
Email
etc...

Addresses
-----------
AddressID
Address
City
etc...

MembersAddresses
------------------
MembersAddressesID
MemberID
AddressID

[Clunietp]

Use 3 separate SQL insert commands, wrapped inside of a transaction. This will ensure integrity

[cschroe2]

Cool. Someone else also gave me the idea that @@IDENTITY would work to pull the AddressID and I think they're right.