Now working! No idea why but this morning it just started working and no complaints from ADO about having two SQL SELECTs in brackets. Only thing that has changed is that I shut the server down overnight, with hindsight I guess I should have done that yesterday.
Many thanks to both Sergy and Naomi your help.
For the archive here is the ASP code:
cSQL = "SELECT TOP 1 * FROM "_
& "(SELECT TOP 1 * FROM Master " _
& "ORDER BY Id ASC) curMaster "_
& "UNION ALL "_
& "SELECT * FROM "_
& "(SELECT TOP 1 * FROM Archive " _
& "ORDER BY Id ASC) curArchive "_
& "ORDER BY Id ASC"