Visual Basic Q&A

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
If you attempt to use the Data control to connect to a password-protectedAccess database at design time, you receive the following error message:

Not a valid passwordSpecifically, this error occurs when you set the RecordSource property of the Data control.
Resolution
In the Properties window, set the following properties for the Datacontrol:

PropertyValue of Property————————————-DatabaseNameATEST.MDBConnect;pwd=aaaRecordSourceTable1
NOTE: You must set the DatabaseName property first, then set the connect property. If the properties are not set in this order, you receive the same error message.
IMPORTANT: This functionality is broken in Microsoft Visual Studio 6.0 Service Pack 4 (SP4).

Symptoms
If you attempt to use the Data control to connect to a password-protected Access database at design time, you receive the following error message:

Not a valid passwordSpecifically, this error occurs when you set the RecordSource property of the Data control.
Resolution
To use the Data control with a password-protected Access 2000 or later database, you must set the Recordset property of the data control at run time as follows:In your project, click References from the Project menu, and then select the Microsoft DAO 3.6 Object Library check box.In the Form_Load event of your form that contains the Data control, place the following code where “Data1″ is the name of your Data control. Make sure to change the path, table name, and password in the following code to reference your database.

Private Sub Form_Load()Dim db As DAO.DatabaseDim ws As DAO.WorkspaceDim rst As DAO.RecordsetSet ws = DBEngine.Workspaces(0)Set db = ws.OpenDatabase _(“C:\Atest.mdb”, _False, False, “MS Access;PWD=aaa”)Set rst = db.OpenRecordset(“Table1″, dbOpenDynaset)Set Data1.Recordset = rstEnd Sub

Symptoms
A data control causes an application error at runtime if the data controlcontains with a blank RecordSource property at design time and theUpdateControls method is executed. The application error ends Visual Basic.
Resolution
To work around this bug, at design time, set the data control’sDatabaseName property to a database and the RecordSource property to atable in the database. Other controls that are bound to the data control,such as a text box, should also have a value in the control’s DataSourceproperty