More than one recordset in same connection

Hello,

Not sure if this is a DB or ASP question.
I am using ASP with an Access 2000 DB & ADO

I have an ASP page that is used to edit data in 3 tables. 2 of these tables have a one-one relationship, third table contains State and Country information.

My question is how do I write the correct SQL statement that will extract relevant data from all 3 tables and also another statement that will Update the data in all 3 tables.

Do I create 2 recordsets, one for the tables with a relationship and one for State/Country data?

Appreciate your help

---Part of my Update code---
Dim objConnection, objRecordset
Dim strSQL, strMessage

Set objConnection = Server.CreateObject("ADODB.Connection")
Set objRecordset = Server.CreateObject("ADODB.Recordset")
objConnection.Open Application("ConnectionString")

strSQL = "UPDATE Cars SET " & _
"Model = '" & Replace(Request.Form("Model"), "'", "''") & "', " & _
"Make = '" & Replace(Request.Form("Make"), "'", "''") & "', " & _
"Year = '" & Replace(Request.Form("year"), "'", "''") & "', " & _
"WHERE CarsID = " & Replace(Request.Form("cardid"), "'", "''")
' Confirmation message
strMessage = "updated"

'Use the execute method of the connection object the insert the record
objConnection.execute(strSQL)
objConnection.close
set objConnection = nothing

Hope this points you in the right direction.

You need to join the tables and select the desired fields.

SELECT Cars.Model, Cars.Make, Table2.fieldA FROM Cars INNER JOIN Table2 ON Cars.CarsID = Table2.fieldB WHERE Cars.Year < 1957

You can use multiple joins in a single statement to pull in all 3 tables. Here is one I was using for something else but joins 3 tables and puts selected fields into recordset.

strSQL = "SELECT DISTINCT G.EventID, E.EventTitle, E.StartDate, E.StartTime FROM (GroupEvents G INNER JOIN MembersGroups M ON G.GroupID = M.GroupID) INNER JOIN Events E ON G.EventID = E.EventID WHERE M.MemberID ='"&Session("MemberID")&"' AND M.IsViewed = Yes AND E.StartDate >= #"&DateRangeBegin&"# AND E.StartDate <= #"&DateRangeEnd

Thanks ShermanPeabody.

One followup question, how do you reference the recordset above?

Response.Write(obRecordset("fieldA"))
OR
Response.Write(obRecordset("Table2.fieldA"))

obRecordset("fieldA")


did the rest of it work out for you?

Yes it did, thanks for you help.