Another SQL Server Question

I've installed SQL Server Express and upsized a couple of databases
(tables only). Almost everything works well but I've hit a snag with
one of my queries. If I have a calculated field like: "SchedDelay2:
[SchedDelay]*[Forms]![QDelays]![DateSpan]*60" in the query grid, I get
an "ODBC-call failed" error that is being caused by the reference to a
form field.

If I remove the reference to the field and just have the calculated
field as:
"SchedDelay2: [SchedDelay*60" I don't get the ODBC error. Why does
this query work fine with a Jet backend but gives this problem with an
SQL Server backend?

Also, when I upsize a database, SQL Server retains the table names
from my Jet backend. If I delete the table links and then relink
them, the table names are then prefixed by "dbo". I then have to go
through the tables and rename them all by removing the "dbo" prefix.
Is there an easier way to avoid the "dbo" prefix than this?

Re: Another SQL Server Question

On Thu, 24 Jan 2008 23:32:55 -0800 (PST), josephlee@live.com.au wrote:

SQL Server is a server and does not know anything about your
application or your forms. It just accepts sql statements and will
execute them or return results. Yours is not a valid T-SQL statement.
Same will happen if your queries use VBA functions. T-SQL has a few,
but you certainly can't use custom functions.

No. Unless you re-link using code (check help file for Connect and
RefreshLink).

-Tom.


>I've installed SQL Server Express and upsized a couple of databases
>(tables only). Almost everything works well but I've hit a snag with
>one of my queries. If I have a calculated field like: "SchedDelay2:
>[SchedDelay]*[Forms]![QDelays]![DateSpan]*60" in the query grid, I get
>an "ODBC-call failed" error that is being caused by the reference to a
>form field.
>
>If I remove the reference to the field and just have the calculated
>field as:
>"SchedDelay2: [SchedDelay*60" I don't get the ODBC error. Why does
>this query work fine with a Jet backend but gives this problem with an
>SQL Server backend?
>
>Also, when I upsize a database, SQL Server retains the table names
>from my Jet backend. If I delete the table links and then relink
>them, the table names are then prefixed by "dbo". I then have to go
>through the tables and rename them all by removing the "dbo" prefix.
>Is there an easier way to avoid the "dbo" prefix than this?

Re: Another SQL Server Question

On Jan 26, 12:16=A0am, Tom van Stiphout wrote:
> On Thu, 24 Jan 2008 23:32:55 -0800 (PST), joseph...@live.com.au wrote:
>
> SQL Server is a server and does not know anything about your
> application or your forms. It just accepts sql statements and will
> execute them or return results. Yours is not a valid T-SQL statement.
> Same will happen if your queries use VBA functions. T-SQL has a few,
> but you certainly can't use custom functions.
>
> No. Unless you re-link using code (check help file for Connect and
> RefreshLink).
>
> -Tom.

Thanks Tom.

Re: Another SQL Server Question

On Jan 26, 12:16=A0am, Tom van Stiphout wrote:
> On Thu, 24 Jan 2008 23:32:55 -0800 (PST), joseph...@live.com.au wrote:
>
> SQL Server is a server and does not know anything about your
> application or your forms. It just accepts sql statements and will
> execute them or return results. Yours is not a valid T-SQL statement.
> Same will happen if your queries use VBA functions. T-SQL has a few,
> but you certainly can't use custom functions.
>
> No. Unless you re-link using code (check help file for Connect and
> RefreshLink).
>
> -Tom.

I guess I thought that all queries etc in the Access frontend would
still work as before given that I have only upsized the tables to SQL
Server using the upsizing wizard and am connecting to them through an
ODBC connection.

All of this is very new to me.

Re: Another SQL Server Question

josephlee@live.com.au wrote:
> I guess I thought that all queries etc in the Access frontend would
> still work as before given that I have only upsized the tables to SQL
> Server using the upsizing wizard and am connecting to them through an
> ODBC connection.
>
> All of this is very new to me.

Actually that is completely correct. What can vary is how much of the work
needing to be done by those queries can be passed to the server to perform and
how much has to be processed locally.

The biggest thing is for the WHERE clause to be executed on the server because
that is what limits the rows brought over. Joins and/or other things that might
be just fine with an Access back end might make it impossible for the server to
perform the row filtering. When that happens a lot of unnecessary data is
pulled from the server.

It is rare for a query to not work at all after moving the tables to ODBC links.
It is somewhat common though for a query to not work very efficiently after
doing so.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Another SQL Server Question

On Jan 26, 10:06=A0am, "Rick Brandt" wrote:

> Actually that is completely correct. =A0What can vary is how much of the w=
ork
> needing to be done by those queries can be passed to the server to perform=
and
> how much has to be processed locally.
>
> The biggest thing is for the WHERE clause to be executed on the server bec=
ause
> that is what limits the rows brought over. =A0Joins and/or other things th=
at might
> be just fine with an Access back end might make it impossible for the serv=
er to
> perform the row filtering. =A0When that happens a lot of unnecessary data =
is
> pulled from the server.
>
> It is rare for a query to not work at all after moving the tables to ODBC =
links.
> It is somewhat common though for a query to not work very efficiently afte=
r
> doing so.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt =A0 at =A0 Hunter =A0 dot =A0 com


Thanks for the reply Rick. So do you see anything in the calculated
field in my query grid (original post) that would cause SQL Server to
give the "ODBC-call failed" error that I am seeing? As mentioned in
the original post, if I remove the reference to the form field
([Forms]![QDelays]![DateSpan] from the calculated field in the query
grid the problem goes away.

References to form fields in the "where" section of the query grid
work fine as they always have. It's just the reference to the form
field in the query's calculated field "SchedDelay" that is causing the
problem.

If I switch my query to SQL view, this what it looks like:

SELECT Count(MinerDelays.MDID) AS CountOfMDID, LU_Delays.DelayType,
LU_Delays.DelayDesc, Sum(MinerDelays.DelayMins) AS SumOfDelayMins,
[SchedDelay]*[Forms]![QDelays]![DateSpan]*60 AS SchedDelay2
FROM (LU_Panel INNER JOIN (LU_Crew INNER JOIN (LU_Shift INNER JOIN
ShiftProdDetails ON LU_Shift.ShiftID =3D ShiftProdDetails.Shift) ON
LU_Crew.CrewID =3D ShiftProdDetails.Crew) ON LU_Panel.PanelID =3D
ShiftProdDetails.Panel) INNER JOIN (LU_DelayType INNER JOIN (LU_Delays
INNER JOIN MinerDelays ON LU_Delays.DelayCode =3D MinerDelays.DelayCode)
ON LU_DelayType.DelayType =3D LU_Delays.DelayType) ON
ShiftProdDetails.ProdShiftID =3D MinerDelays.ProdShiftID
WHERE (((LU_Shift.Shift) Like [Forms]![QDelays]![Shift]) AND
((ShiftProdDetails.TxtDate) Between [Forms]![QDelays]![FromDate] And
[Forms]![QDelays]![ToDate]) AND ((LU_Crew.Crew) Like [Forms]![QDelays]!
[Crew]) AND ((LU_Panel.Panel) Like [Forms]![QDelays]![Panel]))
GROUP BY LU_Delays.DelayType, LU_Delays.DelayDesc,
[SchedDelay]*[Forms]![QDelays]![DateSpan]*60
HAVING (((LU_Delays.DelayType) Like [Forms]![QDelays]![DelayCat]))
ORDER BY Sum(MinerDelays.DelayMins) DESC;

The reference in the SELECT statement to the "Datespan" form field on
the "QDelays" form is the problem.

Re: Another SQL Server Question

josephlee@live.com.au wrote:
> On Jan 26, 10:06 am, "Rick Brandt" wrote:
> Thanks for the reply Rick. So do you see anything in the calculated
> field in my query grid (original post) that would cause SQL Server to
> give the "ODBC-call failed" error that I am seeing? As mentioned in
> the original post, if I remove the reference to the form field
> ([Forms]![QDelays]![DateSpan] from the calculated field in the query
> grid the problem goes away.

I cannot think of any cases where I have ever used a form reference in the
SELECT clause so perhaps that IS an example of where you can fool the system
into sending something to the server that it cannot possibly understand.

When I decide which local Access queries to convert to passthroughs or stored
procedures the first category I look at are those queries that have performance
issues. The second category is queries with multiple joins. The more joins,
the less likely that the query will be processed efficiently.

Your query has quite a few joins so I would likely switch to either a
passthrough query where I change the SQL each time in code to add my variable
values or use a passthrough to call a stored procedure on the server. The
latter would also require dynamically building the SQL for a passthrough, but
the SQL would just be an EXEC call to the stored proc so it would be much
simpler.

You could possibly stay with an Access query setup by first writing a query that
does not need the form reference in the SELECT clause and then using that query
as the input to a second query where the form reference is added in.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Another SQL Server Question

On Jan 26, 11:40=A0am, "Rick Brandt" wrote:

> You could possibly stay with an Access query setup by first writing a quer=
y that
> does not need the form reference in the SELECT clause and then using that =
query
> as the input to a second query where the form reference is added in.

Thanks again for the reply Rick. I think I'll go with your latter
suggestion.