Cannot perform funtion while tables linked

[ZOR]

I have a query that won't run while table links are in force. I had this problem with a select query until making it append. Can I modify the code below to enable it to be run while links are in force? Many thanks.

Set rsAllAthletes = CurrentDb.OpenRecordset("AllAthletes", dbOpenDynaset)
Set rsJUNCTION = CurrentDb.OpenRecordset("JUNCTION", dbOpenDynaset)
Set rsQRYTOP = CurrentDb.OpenRecordset("QRYTOP", dbOpenDynaset)

Do While Not rsAllAthletes.EOF
rsJUNCTION.AddNew
rsJUNCTION.Fields("ID2") = rsAllAthletes.Fields("ID2")
rsQRYTOP.FindFirst ("Athlete = '" & Replace(rsAllAthletes.Fields("Athlete"), "'", "''") & "'")
rsJUNCTION.Fields("AthleteID") = rsQRYTOP.Fields("AthleteID")
rsAllAthletes.MoveNext
rsJUNCTION.Update
Loop

 

[ZOR]

Sorry, fixed. I had my queries in the wrong order, having no second table for the junction table to be created. Regards

 

[PHV]

Why not a simple append query like this ?

INSERT INTO JUNCTION (ID2,AthleteID)
SELECT A.ID2,T.AthleteID
FROM AllAthletes AS A INNER JOIN QRYTOP AS T ON A.Athlete=T.Athlete

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886