many to many relationship inserts

[smook]

Hi all

I have a SQL Server 2000 database that I converted from an access database. The interface is still existing in Access...

Basically I have 2 tables in a many to many relationship - Member and Nominators of members. But because the same nominator can nominate many members and a member can be nominated by multiple nominators, i've added a linking table call MemberNomLink that has the MemberId and the NominatorId.

So in my access interface I have a parent form for the Member, and a subform of all the nominators for that member. When i enter, if the nominator is brand new (never nominated before) then I'd just do an insert - which would:

a) insert into the nominator form (giving a new IDENTITY Primary key - Nominator Id) and
b) would insert into the MemberNomLink table - the new NominatorId and the parent form's Member Id.

When I was running this in access, this worked fine by simply using a left join query on my subform between Nominators and MemberNomLink. When I inserted into this query, access seemed to automatically insert nominator, then create the MemberNomLink fine populating it with the new id.

When I'm attached to the SQL Server 2000 db though, it will insert into the nominator table, however it won't insert into the MemberNomLink.

I don't think this is an unusual circumstance but if anyone has done anything like this, I'd like to know if I'm doing something obviously wrong...

thanks in advance
scott

[Jim Doherty]

Hi all

I have a SQL Server 2000 database that I converted from an access database. The interface is still existing in Access...

Basically I have 2 tables in a many to many relationship - Member and Nominators of members. But because the same nominator can nominate many members and a member can be nominated by multiple nominators, i've added a linking table call MemberNomLink that has the MemberId and the NominatorId.

So in my access interface I have a parent form for the Member, and a subform of all the nominators for that member. When i enter, if the nominator is brand new (never nominated before) then I'd just do an insert - which would:

a) insert into the nominator form (giving a new IDENTITY Primary key - Nominator Id) and
b) would insert into the MemberNomLink table - the new NominatorId and the parent form's Member Id.

When I was running this in access, this worked fine by simply using a left join query on my subform between Nominators and MemberNomLink. When I inserted into this query, access seemed to automatically insert nominator, then create the MemberNomLink fine populating it with the new id.

When I'm attached to the SQL Server 2000 db though, it will insert into the nominator table, however it won't insert into the MemberNomLink.

I don't think this is an unusual circumstance but if anyone has done anything like this, I'd like to know if I'm doing something obviously wrong...

thanks in advance
scott

You can achieve the same functionality using a 'trigger' in SQL although I have to say the after_update event of the appropriate NominatorID field in your interface could be used primarily to determine if apropriate values exists in the MemberNomLink table and if not to then insert them immediately following an insertion of a new nominator. You would then simply requery to refresh the interface.

What format frontend are you using MDB or ADP? Have a good look at the ADP format in Access if you are upsizing for the first time. You can choose either of these of course whichever you arecomfortable with, however the ADP format handily exposes views and stored procedures to you in the interface and connects directly to SQL server using UDL Universal data link ie it doesnt use ODBC connectivity. If you need to create tables locally on the client then stick with the MDB format (mdb is back in favour for Access 2007 too)

Have a go at the after update event based on my simple response here and if you get stuck then post your table structure ie 'exact' table and field names and I'll replicate the tables and necessary SQL on my server, create MDB and an ADP solutions to show you the relevant differences and then mail you the files which should work on your system provided the db name is the same too. Don't post your email address though. (if this progresses to that) you would have to PM me with it and I,ll mail you

Regards

Jim :)