When should I use CreateObject to create my recordset objects?

A quick note: NEVER store a recordset in the session object. For a discussion of this see: 
 
    http://www.microsoft.com/mind/1198/ado/a... (tip #6) 
    KB #176056 
 
Most data access tasks can be implemented by using the execute method of the connection object. Why would we want to do this? Well, for one, there is extra overhead in using a recordset object for UPDATE and INSERT functionality. This is because the provider has to translate your code into an equivalent T-SQL statement anyway (the database itself has no knowledge of "addNew" and similar methods). Also, there are many dangerous locks associated with recordsets... most of which are not necessary (especially when performing an INSERT or UPDATE). Your goal should be to get in, tweak your data, and get out as quickly as possible. Using direct statements is the quickest way to do this, since there is much less overhead and no locks associated with your activity. 
 
Another benefit of using an INSERT or UPDATE statement is that it is much easier to debug. You can change conn.execute(sql) to response.write(sql) and immediately see why your statement is throwing an error. With a multi-line transaction using a recordset object, it is translated to an INSERT or UPDATE statement (inefficiently!) on the DB side, so there is no straightforward way to trap errors at the code level. 
 
To use the connection object, simply design a transact-SQL statement for the action you want to use and implement it like so: 
 

<%      sql = "INSERT INTO <table> (fields) VALUES (values)"      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      conn.execute sql, , 129      ' 129 is the constant for adExecuteNoRecords + adCmdText      ...  %>

 
No recordset object is needed for this, and the 129 constant can be used, because there is no need to return data (adExecuteRecords tells the provider that it doesn't need to worry about sending data back upon execution of the query; adCmdText prevents the provider from having to determine at run-time what type of query it is). If there were a need for returned data in the form of a recordset, we would do it like so: 
 

<%      sql = "SELECT field1, field2 FROM <table>"      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      set rs = conn.execute(sql)      ...  %>

 
The command object is also unnecessary overhead that does little, aside from bloat code, create confusion, and require constant declaration (ADOVBS.INC = bad; see Article #2112 for more details). Even for executing stored procedures, you can do it with the connection object alone: 
 

<%      sql = "EXEC SP_doSomething @param1=1, @param2='" & var & "'"      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      set rs = conn.execute(sql)      ...  %>

 
Bob Barrows prefers this short cut: 
 

<%      conn.SP_doSomething 1, var  %>

 
Even a recordcount can be obtained without having to create an entire instance of an object (see Article #2193 for more information). 
 
There are a few exceptions to this rule, of course. When certain ADO methods need to be used, or the cursorType needs to be changed for any other reason (e.g. for paging through a resultset — see Article #2120), it may be necessary to use a recordset object. But for the record, I manage more than a handful of ASP applications, all of which use SQL Server, and I don't have a single ASP page in use that contains the "ADODB.Recordset" progID.