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:
| 1. | Use a table name instead of a SQL SELECT statement for the RecordSource property, so that SELECT * FROM Tablename is generated |
| 2. | 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 |
| 3. | At run time, explicitly specify a CommandType of adCmdText or adCmdUnknown, so that SELECT * FROM is not automatically prepended. |
| 4. | 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.
|
