yeah, depends on what flavour of SQL you are using. AFAIK ROW_NUMBER() is only supported on Microsoft SQL Server and Oracle - and not in Access. As @jotorre_riversidedpss.org says, given that you are using Microsoft SQL Server 2005 then provided the data you are querying is stored in SQL, rather than Access, if you pass-through the query to SQL that should be fine.
I recommend that you don't convert dates to text string to sort them (nor for any actions in the WHERE clause) - string conversion will perform much slower than keeping them in their native format or, failing that, performing integer arithmetic actions on them. CONVERT style 111 is Y/M/D I think? if so that would be the normal sort order for a date anyway. If the [DateOfBirth] column includes time (seems a bit unlikely, but ...) then there are ways to do that (i.e. in MS SQL at least) which will be more efficient than a string conversion operation.
SELECT ..., ((A.GOLD* 1000000)+(A.SILVER* 1000)+A.BRONZE) AS CALCS,
OVER (PARTITION BY A.GAMES ORDER BY CALCS desc)
Sadly you also cannot re-use the Alias [CALCS] from the SELECT list in the ORDER BY's OVER() clause ... you would have to repeat the formula there, or use a CTE or similar to (separately) calculate that value in order to be able to only have the formula specified only once (and reuse that value within the query 2+ times)
FROM COUNTRYYEAR AS A, COUNTRYNAME AS B, GAMESLIST AS C
WHERE (((A.GAMES)=[C].[GAMES]) AND ((A.CODE)=[B].[CODE]) AND ((C.TYPE)='S'))
Can't speak for Access, but in MS SQL that style has been superseded for some time and would be better written using JOIN syntax e.g.:
FROM COUNTRYYEAR AS A
JOIN COUNTRYNAME AS B
ON [B].[CODE] = A.CODE
JOIN GAMESLIST AS C
ON [C].[GAMES] = A.GAMES
AND C.TYPE = 'S'
The [ObjectName] square brackets are optional, except where the ObjectName is a reserve word