[AccessD] ADO Recordset error near

Yep.  I am generating the SQL in Access and I test the SQL in a query in Access, where it worked 
just fine.  It is only when trying to give the SQL to an ado recordset object and have that object 
ask SQL Server for the data that the problem reared its ugly head.

It took me a few minutes of head scratching to figure it out.

John W. Colby
www.ColbyConsulting.com

On 3/18/2011 1:42 PM, Shamil Salakhetdinov wrote:
> Hi John --
>
> According to the common usage style It would be more correct to  write  :
>
> SELECT * FROM ( SELECT tmmPersonInterests.* FROM tmmPersonInterests  ) T1
> WHERE T1.PEINT_IDPE = 1
>
> Commom rule: Be as explicit as possible when coding your T-SQL (but you can
> often skip 'AS' particle for brevity)  - and you'll be safe&  you'll reach
> the richness.. :)
>
> Thank you.
>
> --
> Shamil
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: 18 ????? 2011 ?. 19:15
> To: Access Developers discussion and problem solving; Sqlserver-Dba
> Subject: [AccessD] ADO Recordset error near
>
> The following statement fails in SQL Server with "error near WHERE.
>
>
> SELECT * FROM ( SELECT tmmPersonInterests.* FROM tmmPersonInterests  ) WHERE
> PEINT_IDPE = 1
>
> I modified it to add the table name.
>
> SELECT * FROM ( SELECT tmmPersonInterests.* FROM tmmPersonInterests  ) WHERE
> tmmPersonInterests .PEINT_IDPE = 1
>
> It doesn't even compile in SQL Server.
>
> PEINT_IDPE is a field in the table and is type int.
>
> The following compiles and runs in sql server
>
> SELECT tmmPersonInterests.* FROM tmmPersonInterests  WHERE
> tmmPersonInterests.PEINT_IDPE = 1
>
> I am trying to programmatically wrap a sql statement inside of the outer
> statement so that I can have any valid statement in the inner sql statement
> and as long as the inner statement exposes the FK, I can filter the result
> set to only a specific set of records.
>
> When I hover over PEINT_IDPE it says that is not a valid column name.  I
> thought that SQL server would evaluate the inside statement, discover that
> PEINT_IDPE existed in tmmPersonInterests and go.
>
> If (back in access) I just cut and paste this statement into a query window,
> it evaluates and pulls a result set.  It does give me a swearword as the
> alias for the interior sql statement (in QBE in Access).
>
> Taking that as a clue, I changed the statement to
>
> SELECT * FROM ( SELECT tmmPersonInterests.* FROM tmmPersonInterests  ) AS T1
> WHERE PEINT_IDPE = 1
>
> And it compiles and runs in SQL Server.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>