NEWBIE: Not an Updatable Query

Hello NG,
SQLServer 2000
Background:
I have been working with an Access2K FE with Access2K BE Tables - I have
just moved all the BE tables to the SQLServer database - I have set up full
permissions to Add,Delete,Modify and Read all the records in all the tables.
All of my code is in the FE database.
2 Questions
1. I try to run the following query:
db.Execute ("UPDATE [Stats Daily Table - Temp] INNER JOIN [Production] ON
([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
Number]) AND ([Stats Daily Table - Temp].[Daily Date] = [Production].[Stats
Date]) " & _
"SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats
Daily Table - Temp]![LB Pulling Orders] + ([Production]![Orders] +
[Production]![WPL Orders]), [Stats Daily Table - Temp].[LB Pulling Lines] =
[Stats Daily Table - Temp]![LB Pulling Lines] + ([Production]![Lines] +
[Production]![WPL Lines]) " & _
"WHERE (([Production].[Department Number]=1) AND
([Production].[Area Number]=102) AND (([Production].[Portion]='LL Lower
Portion') Or ([Production].[Portion]='LU Upper Portion') Or
([Production].[Portion]='LE Leather Portion') Or ([Production].[Portion]='LC
Closeout Portion')));")
It gives me an error that this is not an Updatable Query - It did work
Access to Access.
How can I make this query work - do I have to put the query into the
SQLServer database as a function I need to call for? Do I just need to have
it worded differently for the BE to understand.
2. If this needs to be in the BE - How do I call the query from the FE?
Please Help - I am trying to upgrade to SQLServer - I See its benefits - but
if I cannot understand how to use it I guess I'll be stuck with Access2K's
limitations.
TIAFAH
RandyFor one thing get rid of those !'s in your query between the table and
column names. Try running the query in QA (you can copy n paste it from
here):
UPDATE [Stats Daily Table - Temp]
INNER JOIN [Production]
ON ([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
Number])
AND ([Stats Daily Table - Temp].[Daily Date] = [Production].[Stats Date])
SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats Daily Table -
Temp].[LB Pulling Orders] + ([Production].[Orders] + [Production].[WPL
Orders]),
[Stats Daily Table - Temp].[LB Pulling Lines] = [Stats Daily Table -
Temp].[LB Pulling Lines] + ([Production].[Lines] + [Production].[WPL Lines])
WHERE (([Production].[Department Number]=1)
AND ([Production].[Area Number]=102)
AND (([Production].[Portion]='LL Lower Portion')
OR ([Production].[Portion]='LU Upper Portion')
OR ([Production].[Portion]='LE Leather Portion')
OR ([Production].[Portion]='LC Closeout Portion')));
Also, are you running this using ASP.NET as a front end?
Thanks
"Randy Fritz" <Randyfritz@.sbcglobal.net> wrote in message
news:eD0BclsRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hello NG,
> SQLServer 2000
> Background:
> I have been working with an Access2K FE with Access2K BE Tables - I
> have
> just moved all the BE tables to the SQLServer database - I have set up
> full
> permissions to Add,Delete,Modify and Read all the records in all the
> tables.
> All of my code is in the FE database.
> 2 Questions
> 1. I try to run the following query:
> db.Execute ("UPDATE [Stats Daily Table - Temp] INNER JOIN [Production] ON
> ([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
> Number]) AND ([Stats Daily Table - Temp].[Daily Date] =
> [Production].[Stats
> Date]) " & _
> "SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats
> Daily Table - Temp]![LB Pulling Orders] + ([Production]![Orders] +
> [Production]![WPL Orders]), [Stats Daily Table - Temp].[LB Pulling Lines]
> =
> [Stats Daily Table - Temp]![LB Pulling Lines] + ([Production]![Lines] +
> [Production]![WPL Lines]) " & _
> "WHERE (([Production].[Department Number]=1) AND
> ([Production].[Area Number]=102) AND (([Production].[Portion]='LL Lower
> Portion') Or ([Production].[Portion]='LU Upper Portion') Or
> ([Production].[Portion]='LE Leather Portion') Or
> ([Production].[Portion]='LC
> Closeout Portion')));")
> It gives me an error that this is not an Updatable Query - It did work
> Access to Access.
> How can I make this query work - do I have to put the query into the
> SQLServer database as a function I need to call for? Do I just need to
> have
> it worded differently for the BE to understand.
> 2. If this needs to be in the BE - How do I call the query from the FE?
> Please Help - I am trying to upgrade to SQLServer - I See its benefits -
> but
> if I cannot understand how to use it I guess I'll be stuck with Access2K's
> limitations.
> TIAFAH
> Randy
>|||You might want to learn standard SQL syntax and good programming
practices. Never embed blanks in a data element name unless you want to
destroy portability. If you must use reserved words for a data element
name because you do not know ISO-11179 rules then at least do not use
square brackets. I think this is what you were trying to do:
UPDATE DailyStats
SET lb_pull_orders
= DailyStats.lb_pull_orders
+ (SELECT P1.orders + P1.wpl_orders
FROM Production AS P1
WHERE P1.dept_nbr = 1
AND P1.area_nbr = 102
AND DailyStats.emp_nbr = P1.emp_nbr
AND DailyStats.daily_date = P1.stat_date
AND P1.Portion
IN (LL Lower Portion', 'LU Upper Portion',
'LE Leather Portion''LC Closeout Portion'));|||The typo in the IN predicate notwithstanding, Joe is right about your table
naming convention. But you'll get used to that, Joe is always right :)
Even if porting your Database over to another DBMS isn't a priority, those
extra spaces and hyphens in the names could cause you problems down the road
when you try to access the database via other tools and methods. Consider
using underscore characters instead of spaces in identifiers, or just
eliminate spaces altogether and go with the AlternateCAPSLook, which happens
to be "in" this season thanks to a new reality TV show :)
Here's your IN Clause with the correct number of commas and single quotes:
IN ('LL Lower Portion', 'LU Upper Portion',
'LE Leather Portion','LC Closeout Portion'));
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114129809.103356.5010@.l41g2000cwc.googlegroups.com...
> You might want to learn standard SQL syntax and good programming
> practices. Never embed blanks in a data element name unless you want to
> destroy portability. If you must use reserved words for a data element
> name because you do not know ISO-11179 rules then at least do not use
> square brackets. I think this is what you were trying to do:
>
> UPDATE DailyStats
> SET lb_pull_orders
> = DailyStats.lb_pull_orders
> + (SELECT P1.orders + P1.wpl_orders
> FROM Production AS P1
> WHERE P1.dept_nbr = 1
> AND P1.area_nbr = 102
> AND DailyStats.emp_nbr = P1.emp_nbr
> AND DailyStats.daily_date = P1.stat_date
> AND P1.Portion
> IN (LL Lower Portion', 'LU Upper Portion',
> 'LE Leather Portion''LC Closeout Portion'));
>|||Hi Randy,
When moving over to SQL a load of those update/select queries stop
becomming updatable, the most probable reason is the joins, see if you
can send updates to single tables first. I totally agree with the other
guys, NEVER use blanks in table names/field names use the _ instead for
readablity. Take the time to rename your tables and fields as this will
make your coding easer and more readable.
Good luck
Regards
Alex|||TY Everyone involved,
I Still Have my same problem- First I will work on transferring
everything over to the naming conventions you guys have suggested - I more
than likely will use the underscores in place of spaces as sometimes when
things go wrong others are in here that just do not understand so my naming
of the tables and fields where to make it as simple to understand as
possible for those who do not know how to read code normally.
Second - your queries I am assuming are to be set in the user defined
functions of SQLServer - We use an Access2K FE - How do I call this function
from the front end - The front end is where I have run all of my queries and
the query I gave and the ones you have given still give me the error. what
do I have to do to run the query from an Access FE?.
Again
TIAFAH
Randy
"Michael C#" <xyz@.abcdef.com> wrote in message
news:e4_9e.134$eR1.12@.fe12.lga...
> The typo in the IN predicate notwithstanding, Joe is right about your
table
> naming convention. But you'll get used to that, Joe is always right :)
> Even if porting your Database over to another DBMS isn't a priority, those
> extra spaces and hyphens in the names could cause you problems down the
road
> when you try to access the database via other tools and methods. Consider
> using underscore characters instead of spaces in identifiers, or just
> eliminate spaces altogether and go with the AlternateCAPSLook, which
happens
> to be "in" this season thanks to a new reality TV show :)
> Here's your IN Clause with the correct number of commas and single quotes:
> IN ('LL Lower Portion', 'LU Upper Portion',
> 'LE Leather Portion','LC Closeout Portion'));
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1114129809.103356.5010@.l41g2000cwc.googlegroups.com...
>|||Try pass-thru queries. Tends to be more efficient than relying on Jet as
well. And as far as naming, name your tables for *your* ease of use, not
ours :) The main point about removing spaces is that not all tools deal
equally well with spaces in identifier names, and it could cause you
problems down the road. If you're willing to take that chance though, no
one can force you to rename anything.
"Randy Fritz" <Randyfritz@.sbcglobal.net> wrote in message
news:uSL0Bm1RFHA.1096@.tk2msftngp13.phx.gbl...
> TY Everyone involved,
> I Still Have my same problem- First I will work on transferring
> everything over to the naming conventions you guys have suggested - I more
> than likely will use the underscores in place of spaces as sometimes when
> things go wrong others are in here that just do not understand so my
> naming
> of the tables and fields where to make it as simple to understand as
> possible for those who do not know how to read code normally.
> Second - your queries I am assuming are to be set in the user defined
> functions of SQLServer - We use an Access2K FE - How do I call this
> function
> from the front end - The front end is where I have run all of my queries
> and
> the query I gave and the ones you have given still give me the error. what
> do I have to do to run the query from an Access FE?.
> Again
> TIAFAH
> Randy
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:e4_9e.134$eR1.12@.fe12.lga...
> table
> road
> happens
>|||On Thu, 21 Apr 2005 16:22:21 -0700, Randy Fritz wrote:

>Hello NG,
>SQLServer 2000
>Background:
> I have been working with an Access2K FE with Access2K BE Tables - I hav
e
>just moved all the BE tables to the SQLServer database - I have set up full
>permissions to Add,Delete,Modify and Read all the records in all the tables
.
>All of my code is in the FE database.
(snip)
Hi Randy,
A few points to keep in mind:
1. The UPDATE you posted uses a syntax that only Access understands. The
ANSI standard form of the UPDATE syntax is
UPDATE TableName
SET Column = expression
[ ,Column = expression ] ...
[ WHERE logical-expression ]
SQL Server also supports an alternative UPDATE syntax, much like (but
not the same as!) the special Access syntax you used - simplified:
UPDATE [ TableName | alias ]
SET Column = expression
[ ,Column = expression ] ...
FROM TableName [ [AS] alias ]
[ (jointype) JOIN TableName [ [AS] alias ] ON logical-expression ] ...
[ WHERE logical-expression ]
The SQL Server UPDATE ... FROM syntax has some gotchas that you should
be aware of. The most important is that the end result is inpredictable
when one row in the table to be updated can be joined to more than one
row from the tables in the FROM clause.
2. If you run regular queries on linked tables in Access, then the query
will be completely executed by Access. It will get the data it needs
from SQL Server, combine the data to get the end result, then (if it's
an update query) send the modifications back to SQL Server. This means
that regular queries in Access should still use Access' version of SQL
(Jet SQL), not SQL Server's (Transact-SQL). It also means that things
might get slow, especially if a lot of data has to be fetched from the
server before the end result is generated.
3. If you define a query as a pass-through query, Access will simply
pass the exact SQL code on to SQL Server and SQL Server will execute it.
For these queries to work, you'll have to write your code in Transact
SQL, and you can't use the nice visual drag and drop tools offered by
Access. In most cases, pass-through queries perform faster than "normal"
queries.
(snip)
> 2. If this needs to be in the BE - How do I call the query from the FE?
if you create a pass-thorugh query, then the code will be sent to the BE
for execution. But the recommended best practice is to start writing
stored procedures that perform the tasks that typically need to be done,
then call the stored procedures from the FE.
Check out CREATE PROCEDURE in Books Online for the syntax you need to
create stored procedures.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)