Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 238279 - Last Review: May 8, 2003 - Revision: 2.1
PRB: Executing Refresh Method of ADO Data Control Causes Syntax and Method Refresh Errors
This article was previously published under Q238279
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' failed
or:
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.
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 of Select * From Select * From Tablename, which is an invalid SQL statement.
There are several possible workarounds:
- Use a table name instead of a SQL SELECT statement for the RecordSource property, so that SELECT * FROM Tablename is generated
- At design time, use a CommandType value of adCmdUnknown. You could then use a table name at design time, and a SQL SELECT statement at run time
- At run time, explicitly specify a CommandType of adCmdText or adCmdUnknown, so that SELECT * FROM is not automatically prepended.
- Certain providers accept SELECT * FROM (SELECT * FROM Tablename) as a valid SELECT statement, so you could try enclosing the SQL SELECT in parentheses. For example, the Jet OLE DB Provider 4.0 accepts this syntax.
Steps to Reproduce Behavior
- In Microsoft Visual Basic 6.0, create a new Standard EXE project. Form1 is created by default.
- From the Project menu, choose Components, and select Microsoft ADO Data Control 6.0 (OLEDB).
- Place an ADO Data Control and a CommandButton onto Form1.
- Set the ConnectionString property of the ADO Data Control to use the Nwind.MDB that comes with Visual Basic.
- Set the RecordSource property to the Customers table. Confirm that the CommandType is type 2 - adCmdTable.
- Add the following code to the click event for the CommandButton:
' Adodc1.CommandType = adCmdText
Adodc1.RecordSource = "Select * from Employees"
Adodc1.Refresh
- Press the F5 key to execute the code. Click on the CommandButton, and note that the errors are returned on the ADODC1.Refresh line of code. Return to design mode.
- Uncomment the line that explicitly sets the ADO Data Control'sCommandType to adCmdUnknown. Press the F5 key to execute the code, and note that no error is returned.
APPLIES TO
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic Enterprise Edition for Windows 6.0
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.1 Service Pack 1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
| kbdatabase kbprb KB238279 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
Prasanth B
- pbpillai NOSPAM-AT-NOSPAM hotmail.com
|
Reported as Irrelevant
|
| Written:
5/26/2006 7:01 PM |
|
|