Naomi,

Thanks for the suggestion, just needed to add the cursor names as follows and all worked fine in fox:

select * from (select TOP 1 * from Master where ... ORDER BY...) curMaster ;
UNION all ;
select * from (select TOP 1 * from Archive where ... ORDER BY...) curArchive

Unfortunately ADO has it's own issues and doesn't like that SQL, it returns:

Microsoft OLE DB Provider for Visual FoxPro error '80040e14' 

Only a single SQL SELECT statement can be enclosed in parentheses. 

Is there likely to be something different in the data environment between VFP and ADO that I can change?