Visual Basic Q&A

Symptoms
When you use ODBCDirect with Visual Basic, you receive the following error on accessing a recordset object that was created inside of a transaction that has been committed or rolled back:

3670 Cursor is not valid
Resolution
This error occurs because you are using server-side cursors on your connection and the cursor is being closed when the transaction is committed or rolled back. Whether a server-side cursor is closed on a transaction commit or rollback depends on the database driver that you are using. For the SQL Server driver, the default is to close the server-side cursor on the commit or rollback of a transaction.

Symptoms
When you create a SQL Server local temporary table using the rdoConnection object by calling its .Execute method with default parameters, and then attempt to access the table after the Remote Data Objects (RDO) method has run, you may receive one of the following error messages:

Run-time error ‘40002′: 37000:[Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s)could not be prepared
-or-

Run-time error ‘40002′: S0002:[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid Object Name ‘#<Name of the temporary table>’
Resolution
The creation and use of temporary database tables to facilitate the storage and manipulation of volatile intermediate data is a common programming practice. The default behavior of the SQL Server Open Database Connectivity (ODBC) driver is to create and use temporary stored procedures to run prepared statements. The .Execute method of the rdoConnection object uses the SQLPrepare() and SQLExecute() ODBC application programming interface (API) calls by default to run a SQL statement as a prepared statement. Temporary tables that are created by a stored procedure are automatically dropped when the procedure completes execution. As a result, when you attempt to access a SQL Server temporary table that was created by calling the .Execute method of an rdoConnection object with default parameters, in subsequent statements you receive one of the error messages specified in the “Symptoms” section.