Sorry, no. I forgot to paste the Test_SQL.close statement in the sample code. Without this statement I would get an error, since I am not allowed to open a recordset which is already opened.

The task is to find 5000 arbitrary records in a big database. In SQL-server the only way to do this is running 5000 queries - or is there any other alternative???

Changing the static SQL statements to a stored procedure did not really make a big difference.

I changed the benchmark to make it a bit more realistic:

I did a query returning 1000 datasets. Then I scanned through the 1000 Records evaluating some field. This procedure I looped 200 times with arbitrary query criteria.

I tested alternatively with DAO and ADO (using Jet) on the same access database on a network server and also on SQL server (same as the file server)

DAO result: 14-15 seconds (DB_OPEN_DYNASET)
DAO result: 20seconds (DB_OPEN_SNAPSHOT)
ADO result: 12-13 seconds (Server cursor)
ADO result 18 seconds (client cursor)
SQL result: 10 seconds (client cursor, which in this case means Read only)
SQL result 80 seconds (Server cursor adLockOptimistic)
SQL result 70 seconds (Server Cursor, adLockReadonly)

So this kind of benchmark says:
DAO and ADO are more or less equal in the performance on a Access database
On SQL with the local cursor read only queries may be obtained very fast with the local cursor
SQL with a read/write option take about 5-8x longer than anything else, even with little SQL-translation overhead

Additionally I found out, that using DB_OPEN_DYNASET with DAO is considerably faster, than DB_OPEN_SNAPSHOT - just the opposite, which may be expected...

For performant database applications this means quite a lot of optimizing work in the code changing to SQL server - and it means, that searching and editing single records will be considerably slower on the SQL server. Surprising for me, anyway