Another SQL Server Question

[josephlee]

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?

[Tom van Stiphout]

On Thu, 24 Jan 2008 23:32:55 -0800 (PST), jo*******@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?

[josephlee]

On Jan 26, 12:16*am, Tom van Stiphout <[e-mail]:

On Thu, 24 Jan 2008 23:32:55 -0800 (PST), [e-mail] 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.

[josephlee]

On Jan 26, 12:16*am, Tom van Stiphout <[e-mail]:

On Thu, 24 Jan 2008 23:32:55 -0800 (PST), [e-mail] 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.

[josephlee]

On Jan 26, 11:40*am, "Rick Brandt" <[e-mail]:

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.

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