works in query window, but not in code

I have this sql that comes from the debug screen... where it fails in
code. [Microsoft][ODBC SQL Server Driver][SQL Server] 'First' is not
a recognized function name. I had this running on another machine
where it did not question the syntax.


INSERT INTO developer_tblQualitySurvey ( Company, Job_Num, QS_Date,
First_Name, Customer_Number, Email, Job_Num_Count ) SELECT
Customers.Company, First(tblJobs.Job_Num) AS FirstOfJob_Num,
First(#12/24/2007#) AS Expr1, Customers.[First Name], Customers.
[Customer Number], Customers.Email, Count(tblJobs.Job_Num) AS
CountOfJob_Num FROM Customers INNER JOIN tblJobs ON Customers.
[Customer Number] = tblJobs.[Customer Number] Where
(((tblJobs.Confirmed)='Yes') AND ((tblJobs.StrikeCall) > #12/17/2007#
And (tblJobs.StrikeCall) <= #12/24/2007#)) GROUP BY Customers.Company,
Customers.[First Name], Customers.[Customer Number], Customers.Email
HAVING(((Customers.EMail) Is Not Null)) WITH OWNERACCESS OPTION;

When I paste it into a query window it runs fine. Any ideas? Does
the query window parse it differently for SQLServer?
developer_tblQualitySurvey is attached SQLServer table and tblJobs and
Customers are attached Jet tables.


Thanks, Preston

Re: works in query window, but not in code

On Mon, 14 Jan 2008 12:58:16 -0800 (PST), pmacdiddie@gmail.com wrote:

It appears the statement is passed to SQL Server for evaluation, and
as you may know T-SQL does not know about First as an aggregate
function. Try rewriting using the Min function.
SQL Server also does not know about WITH OWNERACCESS OPTION.

-Tom.


>I have this sql that comes from the debug screen... where it fails in
>code. [Microsoft][ODBC SQL Server Driver][SQL Server] 'First' is not
>a recognized function name. I had this running on another machine
>where it did not question the syntax.
>
>
>INSERT INTO developer_tblQualitySurvey ( Company, Job_Num, QS_Date,
>First_Name, Customer_Number, Email, Job_Num_Count ) SELECT
>Customers.Company, First(tblJobs.Job_Num) AS FirstOfJob_Num,
>First(#12/24/2007#) AS Expr1, Customers.[First Name], Customers.
>[Customer Number], Customers.Email, Count(tblJobs.Job_Num) AS
>CountOfJob_Num FROM Customers INNER JOIN tblJobs ON Customers.
>[Customer Number] = tblJobs.[Customer Number] Where
>(((tblJobs.Confirmed)='Yes') AND ((tblJobs.StrikeCall) > #12/17/2007#
>And (tblJobs.StrikeCall) <= #12/24/2007#)) GROUP BY Customers.Company,
>Customers.[First Name], Customers.[Customer Number], Customers.Email
>HAVING(((Customers.EMail) Is Not Null)) WITH OWNERACCESS OPTION;
>
>When I paste it into a query window it runs fine. Any ideas? Does
>the query window parse it differently for SQLServer?
>developer_tblQualitySurvey is attached SQLServer table and tblJobs and
>Customers are attached Jet tables.
>
>
>Thanks, Preston

Re: works in query window, but not in code

On Jan 14, 10:13=A0pm, Tom van Stiphout wrote:
> On Mon, 14 Jan 2008 12:58:16 -0800 (PST), pmacdid...@gmail.com wrote:
>
Ok, so i dumped the first and replaced with Min. Also deleted with
owner access. That helped. Thanks

Now the problem is with the # in the dates. Are there some other
drivers to use that will parse the query before it gets to the SQL
server.

As I mentioned this was running ok on Access 2007, it is now in Access
XP, where it does not work.

-Preston

Re: works in query window, but not in code

pmacdiddie@gmail.com wrote in news:804663dc-c57e-4bc6-9f31-
6c281c0a38df@l1g2000hsa.googlegroups.com:

> On Jan 14, 10:13 pm, Tom van Stiphout wrote:
>> On Mon, 14 Jan 2008 12:58:16 -0800 (PST), pmacdid...@gmail.com wrote:
>>
> Ok, so i dumped the first and replaced with Min. Also deleted with
> owner access. That helped. Thanks
>
> Now the problem is with the # in the dates. Are there some other
> drivers to use that will parse the query before it gets to the SQL
> server.
>
> As I mentioned this was running ok on Access 2007, it is now in Access
> XP, where it does not work.
>
> -Preston

For dates in t-sql I generally use
CAST ('2007-10-16' AS DateTime).

When you use Access as front-end for MS-SQL Server connected with ODBC,
ODBC translates your JET type SQL to T-SQL, with, in my opinion,
wonderful effectiveness.

But when you make a direct connection in pre-2007 versions, you must
create the T-SQL yourself. As T-SQL is many times more powerful than
Jet's SQL, this may be worth the struggle.

But, TTBOMK, Access 2007's ACCDB (but not other file formats) Current
Project Connection's provider is "Microsoft.ACE.OLEDB.12.0" and not the
"SQLOLEDB.1" provider with which we are familiar. Perhaps, the
Microsoft.ACE.OLEDB.12.0 provider translated your SQL for you, while that
of Access 2002 did not.

I find the two providers markedly different. For instance,
Microsoft.ACE.OLEDB.12.0 GetString function seems much less powerful than
that of SQLOLEDB. Perhaps I've been remiss but I haven't found the
documentation that emphasizes these differences. You've had a problem
going from 2007 to 2002. I expect there will many more problems going the
other way.

On the other hand this is all new. And one could certainly use an
SQLOLEDB provider from VBA in Access 2007. Probably much more is to be
learned.

Re: works in query window, but not in code

On Jan 15, 2:44=A0am, lyle fairfield wrote:
> pmacdid...@gmail.com wrote in news:804663dc-c57e-4bc6-9f31-
> 6c281c0a3...@l1g2000hsa.googlegroups.com:
>
> > On Jan 14, 10:13=A0pm, Tom van Stiphout wrote:=

> >> On Mon, 14 Jan 2008 12:58:16 -0800 (PST), pmacdid...@gmail.com wrote:
>
> > Ok, so i dumped the first and replaced with Min. =A0Also deleted with
> > owner access. =A0That helped. =A0Thanks
>
> > Now the problem is with the # in the dates. =A0Are there some other
> > drivers to use that will parse the query before it gets to the SQL
> > server.
>
> > As I mentioned this was running ok on Access 2007, it is now in Access
> > XP, where it does not work.
>
> > -Preston
>
> For dates in t-sql I generally use
> CAST ('2007-10-16' AS DateTime).
>
> When you use Access as front-end for MS-SQL Server connected with ODBC,
> ODBC translates your JET type SQL to T-SQL, with, in my opinion,
> wonderful effectiveness.
>
> But when you make a direct connection in pre-2007 versions, =A0you must
> create the T-SQL yourself. As T-SQL is many times more powerful than
> Jet's SQL, this may be worth the struggle.
>
> But, TTBOMK, Access 2007's ACCDB (but not other file formats) Current
> Project Connection's provider is "Microsoft.ACE.OLEDB.12.0" and not the
> "SQLOLEDB.1" provider with which we are familiar. Perhaps, the
> Microsoft.ACE.OLEDB.12.0 provider translated your SQL for you, while that
> of Access 2002 did not.
>
> I find the two providers markedly different. For instance,
> Microsoft.ACE.OLEDB.12.0 GetString function seems much less powerful than
> that of SQLOLEDB. Perhaps I've been remiss but I haven't found the
> documentation that emphasizes these differences. You've had a problem
> going from 2007 to 2002. I expect there will many more problems going the
> other way.
>
> On the other hand this is all new. And one could certainly use an
> SQLOLEDB provider from VBA in Access 2007. Probably much more is to be
> learned.

Based on what you have written, I would say that is what is
happening. My main platform is a 2002 desktop. I have 2007 on my
laptop that I use for developement sometimes. Fortunately, I dont
have any apps running both at this time.

Thanks for your help.

P