How to Update Join Tables?

I have a question about JOIN'ed table in Access. I have 2 tables in my database with one being tbl_Events and the other tbl_VolunteerSchedule. tbl_Events contains information regarding upcoming Events. tbl_VolunteerSchedule contains the names of Volunteers plus information from tbl_Events pertaining to the event the volunteer is volunteering for. What I need is for tbl_Volunteer to Update when I make changes to the Events in tbl_Events. I thought that it was working correctly when I used a LEFT JOIN. However if I make updates to tbl_Events it adding additional record to tbl_Volunteer. It should only Update tbl_VolunteerSchedule when the fields EventID match. Does this make sense? Thanks.


If Request.Form("Action") = "Modify >>>" Then

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=volunteerman"
Updates = Updates & "tbl_Events.ModifiedBy = " & Checkstring(Request("ModifiedBy"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.ModifiedBy = " & Checkstring(Request("ModifiedBy"), ", ")
Updates = Updates & "tbl_Events.DateModified = " & Checkstring(Request("DateModified"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.DateModified = " & Checkstring(Request("DateModified"), ", ")
Updates = Updates & "tbl_Events.EventName = " & Checkstring(Request("EventName"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventName = " & Checkstring(Request("EventName"), ", ")
Updates = Updates & "tbl_Events.EventCity = " & Checkstring(Request("EventCity"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventCity = " & Checkstring(Request("EventCity"), ", ")
Updates = Updates & "tbl_Events.EventLocation = " & Checkstring(Request("EventLocation"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventLocation = " & Checkstring(Request("EventLocation"), ", ")
Updates = Updates & "tbl_Events.EventDate = " & Checkstring(Request("EventDate"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventDate = " & Checkstring(Request("EventDate"), ", ")
Updates = Updates & "tbl_Events.EventStartTime = " & Checkstring(Request("EventStartTime"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventStartTime = " & Checkstring(Request("EventStartTime"), ", ")
Updates = Updates & "tbl_Events.EventEndTime = " & Checkstring(Request("EventEndTime"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventEndTime = " & Checkstring(Request("EventEndTime"), ", ")
Updates = Updates & "tbl_Events.EventInformation = " & Checkstring(Request("EventInformation"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventInformation = " & Checkstring(Request("EventInformation"), ", ")
Updates = Updates & "tbl_Events.EventCoordinator = " & Checkstring(Request("EventCoordinator"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.EventCoordinator = " & Checkstring(Request("EventCoordinator"), ", ")
Updates = Updates & "tbl_Events.CoordinatorPhone = " & Checkstring(Request("CoordinatorPhone"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.CoordinatorPhone = " & Checkstring(Request("CoordinatorPhone"), ", ")
Updates = Updates & "tbl_Events.CoordinatorEmail = " & Checkstring(Request("CoordinatorEmail"), ", ")
Updates = Updates & "tbl_VolunteerSchedule.CoordinatorEmail = " & Checkstring(Request("CoordinatorEmail"), " ")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=volunteerman"
SQL = "UPDATE tbl_Events LEFT JOIN tbl_VolunteerSchedule ON tbl_Events.EventID = tbl_VolunteerSchedule.EventID SET " & Updates & "WHERE tbl_Events.EventID = " & Request.QueryString("EventID")
Conn.Execute(SQL)
Conn.close

Couple things here.

Building strings the way you have here consumes resources. Secondly, all of that crud eats up bandwidth when you pass that query to your database.

Instead of all of that, create a procedure in Access and then create parameter objects for a command object. It's about the same amount of work as what you have, but it's much more readable.

At any rate, since this is such an ugly query to begin with, you still need to create a procedure. Create the procedure as a select query, providing parameters which you KNOW will return the data that you want.

Next: left outer join and right outer join are absolutely interchangeable. It just depends on which table you select (or in your case, update) from first. Obviously in your case, you only want to update the fields where you've not gotten any null values returned. Best I can tell you is to change to a right outer join and see if that gets what you want.

Also, if you establish a parent-child relationship between these two tables and enforce referential integrity, you are unlikely to have the kinds of problems you are having now.

Sorry for the vagueness of the answer, but it's the best I can come up with without knowing the nature of your DB.