Visual Basic Q&A

Symptoms
If you try to run Structured Query Language (SQL) against an Oracledatabase through Data Access Objects (DAO), you can encounter the followingerror:

Run-Time Error 3024
Couldn’t find file C:\Program Files\DevStudio\VB\<schema name>.mdb.This error occurs when you try to specify a schema name in your SQLstatement and you use DAO with Oracle. The <schema name> in the errormessage is whatever schema name you specified for the Oracle object youreference.
Resolution
If you add the dbSQLPassThrough option to the OpenRecordset method, thesample code in the MORE INFORMATION section of this article runs withouterror. You can use brackets ([ ]) around the table name in the SQL string toavoid the error as well:

sql = “SELECT * FROM [scott.emp]” Also, you can use an advanced data access technology, such as ActiveX DataObjects (ADO) to avoid this error.

Symptoms
Pressing the ESC key while the cursor is in the SQL text box of theGeneral Tab in the property page of the Remote Data Control causes a blankGeneral tab. When you click the Colors tab and then click the General tabagain, an application error occurs and Visual Basic ends. The SQL text boxcan be empty or contain characters. Clicking the Cancel button on thewindow correctly closes the window. This behavior only occurs under theWindows 95 operating system.To work around this behavior, programmatically set the properties ofremote data control or use the Properties window of the Remote Data Objectto set the properties.
Resolution
Microsoft has confirmed this to be an issue in the Microsoft productslisted at the beginning of this article. Microsoft is researching thisissue and will post new information here in the Microsoft Knowledge Baseas it becomes available.

Symptoms
When you set the RecordSource property of an ADO Data Control to a different SQL SELECT statement and then try to execute the Refresh method, the following error is returned:

Syntax error in FROM clause.which is followed by:

Run-time error ‘-2147217900(80040e14)’:
Method ‘Refresh’ of object ‘IAdodc’ failedor:

Method ‘Refresh’ of object ‘IAdodc’ failed when attempting to refresh an ADODC after setting the recordsource property to another value.The errors occur if the CommandType property of the ADO Data Control is set to adCmdTable.
Resolution
When the CommandType of the ADO Data Control is set to adCmdTable, “SELECT * From” is automatically prepended to the RecordSource value.
Setting the RecordSource to a table name results in a valid SQL statement, such as SELECT * FROM Tablename.
Setting the RecordSource to a SQL SELECT statement, such as Select * From Tablename, results in a SQL statement ofSelect * From Select * From Tablename, which is an invalid SQL statement.

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.

Symptoms
With SQL Server’s quoted_identifier option set to Off, you may receive the following error:

Run-time error ‘-2147217900 (80040e14)’:
Line 1: Syntax error near ‘tablename’ This error occurs when you are using client-side cursors with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). The error occurs on an ActiveX Data Objects (ADO) recordset’s Update method and may occur on an AddNew method.
Resolution
With ADO client-side cursors, when you invoke an ADO recordset’s AddNew or Update method, the OLE DB Provider prepares a SQL statement to send to SQL Server.
The Microsoft OLE DB Provider for SQL Server automatically quotes identifiers on an ADO recordset’s Update method and may quote identifiers on an AddNew method. Identifiers include table names and field names.
For example, updating the Titles table in the Pubs database with the following code:

MyADORecordet.Update The preceding would be prepared similar to the following:

UPDATE “titles” SET “title”=’Hello World’ WHERE “title_id”=’3′ Note that the table name is in quotes, “titles”, and that each field name is in quotes, “title”, “title_id”, and so on.
If SQL Server’s Quoted_Identifier option is set to Off, SQL Server will not recognize table names and field names enclosed in quotes.
The error “Syntax error near ‘tablename’” occurs.

Symptoms
When attempting to delete or update a record in a SQL Server cursor, the delete or update operation succeeds, but the following error returns:

Run-time error 3146 “ODBC call failed”.
Resolution
This behavior occurs when SQL Server does not return a message indicating the number of rows returned by a statement. SQL Server does not return a message indicating the number of rows affected by a statement after the following commands have been executed on SQL Server:

sp_configure “user options”, 512SET NOCOUNT ON