What is the best data access method?

[Punchinello]

Can anyone recommend which of the following techniques (or any other) is better, stronger, faster? Data resides on a LAN in SQL Server and filering would be done on indexed field(s) only.

1. Opening a recordset using SQL string:
strSQL = "SELECT * FROM Table1 WHERE KeyValue=" & intValue
rstTable1.Open strSQL, cnxMain, ...

2. Opening a recordset using filter:
rstTable1.Open "Table1", cnxMain, ...
rstTable1.Filter = "KeyValue=" & intValue

And, no, this is not a test -- I am writing an app for a client who already has a big database but I only get a little itty-bitty database for my development environment so I cannot really optimize for speed.

 

[jpbrassard]

I would go with a modified Option 1 - using SQL, but (if possible) using store procedures in the SQL Server db and calling those through either the ADO Connection or Command object's Execute method. Gives you something of a business logic layer in between your front-end and the db, and will make the front-end as "dumb" as is possible, which should reduce the risks of data contamination.

I've also noticed that stored procs seem to be more robust, both in terms of consistency and speed. But that's not nearly any kind of scientific observation - treat it as anecdotal. As always, YMMV.

HTH,
jp