Visual Basic Q&A

Symptoms
If you issue a DbEngine.RepairDatabase method after you change your project references from Microsoft DAO 3.51 Object Library to Microsoft DAO 3.6 Object Library, you may receive the following error message:

Error # 3251 was generated by DAO.DbEngine.
Operation is not supported for this type of object.Or, you may notice that the method is not available through IntelliSense when you issue a DbEngine.RepairDatabase method.
Resolution
In Data Access Object (DAO) 3.6, the RepairDatabase method is no longer available or supported. This is by design to match Microsoft Jet 4.0.

Symptoms
Jet does not support named QueryDefs on a non-attached ODBC database. A nonattached ODBC database is one that is opened directly with theOpenDatabase method of the WorkSpace object without the use of an .mdb file.
Resolution
The preferred method for opening an external ODBC table is to attach it toan .mdb file. For additional information, please see the followingarticle(s) in the Microsoft Knowledge Base:
150716?(http://support.microsoft.com/kb/150716/EN-US/): DAO: How To Attach to and Create QueryDefs on ODBC Tables
If this method is not appropriate for your application, the followingexample shows how to createQuerydefs with no name:

Set qd = db.CreateQueryDef(“”)qd.SQL = “Select * from authors”

Symptoms
You have a Visual Basic ActiveX DLL that has a method that takes an array of Scripting Dictionary Objects as an argument. It may work fine when your Visual Basic client is run in the Visual Basic integrated development environment (IDE) by using this method, but when you run it as a compiled application, you get the following run-time error message:

Run-time error ‘-2147417848 (80010108)’:
Method ‘~’ of object ‘~’ failedThis only occurs when you use late binding to call the method.
Resolution
Use early binding to work around the problem.

Symptoms
This article describes problems that you may experience when you follow the documentation for the Item Method (General Extensibility) that appears in the following Microsoft Developer Network (MSDN) article:
http://msdn2.microsoft.com/en-us/library/aa301425(VS.71).aspx(http://msdn2.microsoft.com/en-us/library/aa301425(VS.71).aspx)When you paste the sample code from the Example section of the article to a Visual Basic .NET application, and then you try to compile the file, you receive the following error message:

Name ‘DTE’ is not declared.When you try to assign the return value of the EnvDTE.Documents.Item method to an AddIn type variable, you receive the following error message:

An unhandled exception of type ‘System.InvalidCastException’ occurred in ApplicationName.exe
Additional information: Specified cast is not valid.When you pass an Object parameter to the EnvDTE.Documents.Item method, you receive the following error message:

An unhandled exception of type ‘System.Runtime.InteropServices.COMException’ occurred in ApplicationName.exe
Additional information: Type mismatch.
Resolution
You receive the “Name ‘DTE’ is not declared” error message because the variable named DTE is not declared in the sample code that is provided in the MSDN article. You receive theInvalidCastException error message because the MSDN article states that the return value for the EnvDTE.Documents.Item method is an AddIn type. However, the EnvDTE.Documents.Item method returns a Document type object. Therefore, when you try to assign a Document type object to an AddIn type variable, you receive the error message.You receive the Type mismatch error message when you try to pass a parameter that is not an Integer or a String to the EnvDTE.Documents.Item method. The EnvDTE.Documents.Item method is used to gain access to the elements of the EnvDTE.Documents collection. The EnvDTE.Documents.Item method can accept only two types of parameters:Integer – for the index value of the documents in the EnvDTE.Documents collectionString – for the key of the documents in the EnvDTE.Documents collection

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.