[AccessD] ADO Recordset error near

In Access 2003 if you go to Tools, Options in the Tables/Queries tab is
an option for SQL Server Compatible Syntax (ANSI 92).  I've never played
with the setting - but maybe if you had that option turned on, your
generated SQL from Access would work as is in SQL Server via ado?

HTH,

Rusty

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, March 18, 2011 2:29 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
> --
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************