The Filter Property

DAO Recordset objects have the Filter Property. I did not start using it until I started using ADO. Just a coincidence. I mention here for historical reasons. It's my history. Moving right along...

Calling Stored Procedures with ODBC SQL

The very concept of ODBC SQL was not formally introduced to me. So now there is MS-SQL (for Access), t-SQL and ODBC SQL. Microsoft recommends using the ODBC CALL escape sequence of this form:

{[?=]call proc_name[([parameter][,[parameter]]...)]}

It is important to see that question mark as this is Parameter 0 in the Parameters Collection of a Command object for all stored procedures with parameters. For example, this is what the stored proc' get_cust_dates() looks like in a Command object:

With cmdADO
    Set .ActiveConnection = objADO.Connection
    .CommandText = "get_cust_dates"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters.Item("@RETURN_VALUE") = 0
    .Parameters.Item("@all_dates") = 1
    .Parameters.Item("@max_yyyyq") = 19993
    Set rstADO = .Execute
End With

This avoids using our ODBC SQL syntax entirely. However this form uses the ODBC SQL syntax:

With cmdADO
    Set .ActiveConnection = objADO.Connection
    .CommandText = "{call get_cust_dates(1,19993)}"
    .CommandType = adCmdText
    Set rstADO = .Execute
End With

This form requires less parsing by the RPC protocol used by SQLOLEDB (the Provider for SQL 7). However, this form makes it "harder" to Execute the Command with different parameters. ("Harder" means understanding the concept of "parameter markers" or string handling via the Replace() function.) I don't use the Command object to open Forward-only, Read-only Recordsets; I use the Connection object. For more information please see SQL 7 Books Online; search for "Calling a Stored Procedure (OLE DB)."

The NextRecordset Method

I am sure that only a select few DAO programmers know that the NextRecordset Method was available in ODBCDirect Workspaces. I never used it. I must make mention of it because it is indirectly related to a fundamental misunderstanding I had about ADO Recordsets returned from SQLOLEDB. I thought ADO was unfinished or "broken." The only Provider I trusted was the OLE DB Provider for ODBC (MSDASQL). If I tried to use a "native" SQL Server Provider it would occasionally return closed (State = adStateClosed).

It took me over a year (experimenting off and on) to discover that on those "occasions" when the Recordsets came back closed the Recordset was based on a stored procedure SELECTing from a temporary table. This was quite a revelation for me. Microsoft knew this all along. For more details please see MS KB Article Q235340 ("PRB: Error Messaging Referencing #Temp Table with ADO-SQLOLEDB.")

Microsoft says this behavior is by design. To stop this behavior send the command SET NOCOUNT ON before the stored proc' is run. The reason why this behavior is by design is because SQLOLEDB Providers can return multiple Recordsets. If you have ever run a stored proc' from the SQL 7 Query Analyzer and noticed multiple "Records Affected" messages return during execution, then it would not be unreasonable to say that SQLOLEDB "sees" this as well. It follows that ADO's NextRecordet Method can be used for such a stored proc'.

As of this writing I am not certain that I will be using the NextRecordset Method since there is a problem using this method when a connection is closed. I will keep my eyes open. For more details see MS KB article Q193352 ("PRB: NextRecordet fails When Called On a Disconnected Recordset").

The PageSize, PageCount and AbsolutePage Properties

I have yet to use these properties but I know I will. This is why I recommend taking a look at MS KB article Q202125 ("HOWTO: Page Through a Recordset from ASP").