I'd minimize the "with buffering" as it is newest. If possible I'd create a cursor of all the relevant fields from B and C first:

SELECT TOP TableC.Primary_ID, TableB.TableA_FK_ID, TableB.BeginDate ;
        FROM TableC WITH(BUFFERING = .T.)  INNER JOIN TableB WITH(BUFFERING = .T.) ON TableC.Primary_ID == TableB.TableC_FK_ID;
        UPPER(ALLTRIM(TableC.Type)) == "PRIMARY" ;
        ORDER BY 3 DESC INTO CURSOR crsLookUp

*-- hopefully lookup data size is much reduced now, perhaps you add group by 1,2 and Max(begindate)

Select tableA
Scan
EndScan

Just a gut feeling so far. I'd probably try for a total SQL solution first if faced with new dev, but if errors are threatening as you describe I'd go for a total xBase schema as this is easier to log. But this mixture (while I see nothing to point out as "syntactically wrong" at first glance) is not good to visualize and perhaps not the fastest solution.

my 0.02 EUR

thomas