Slow recordset open, memory eating

I search on this forum but didn't find anything about memory consuming and opening recordset in an access database. The problem is when I open my recordset to select ~3 to 50 record in a table containing ~30 000 record. This load 17 megs in memory!! The database is 30 meg! I hope that the open load the complete table in memory instead of the 3 records? This is not a real problem on a lan but on a wan(intranet) it take 1-2 minutes to download 17 megs. Is there a way to avoid loading the complete table?
There's my connection code :

gcnnProjets.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;DATA SOURCE=" & pathBd
gcnnProjets.CursorLocation = adUseClient


the recordset.open that eat 17 megs of memory when opening 3 records in 30 000:

grecContenu.Open "SELECT * FROM Projects WHERE WarrantyStatus = 'New'", gcnnProjets, adOpenDynamic, adLockOptimistic


I have test all lines to find that when I pass over this line, 17 megs is added to the memory used by VB in the task manager. Believe me!

Anyone has an idea to load only the 3 record needed in memory instead of the table? I have to write in this record, so they must be open in read/write mode and I need all fields in the record because any one can be modify

Thanks

Frank

I believe that with Access, there isn't really any other way.

Let me explain. No, too much. Let me sum up. (sorry for "the Princess Bride" reference ;))

The need to build the cursor on the client-side by Access means that the table and the associated indexes need to be loaded for the query to be performed. I believe that you will (not to anybody's satisfaction) find that if you ran the same query from within Access itself, the Mem Usage would "fly like an eagle... to the sea".

so is there a way to send the "SELECT" to access to be execute on the server in the access db and then retrieve the selection?

If no, if is use MySql instead of access, it will be the same problem or not?

No, Access is a "shared-file" database and the Jet database engine doesn't support server-side cursors.

An alternative would be to look at using a server-based database engine like SQL Server (a free, limited version is MSDE).

MySql, although I haven't used personally, is a server-based database engine and should support server-side cursors.

Besides, server-based engines typically handle memory usage a lot better.

thanks for quick reply !! I will migrate to MySql....

IMO, in many ways, MSDE is a better database than is MySQL. IT's certainly a more thorough implementation of a relational database, and it is a much more ansi standard SQL version.