HLEP..Sql Server UPDATE INNER JOIN QUERY ..

Im using an ADP to connect to a SQL Sqever DB.
In access it was really easy to say

Inner join on table1 and table2 and update columnA from table1 with
columnC from table2 where table1.key = table2.key and table2 columnB =
1 and table2 columnD = 4

I have tried all manner of beasts to get this thing to work..

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))

This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0' part which is really important

Any Help would be great...This way looks like it should work also but I get an error 'ADO Error:
HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
can only show one table in an update qurey?

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0))
WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)|||SAME ADO ERROR WHEN I USE THIS ???

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
FROM GIS_EVENTS_TEMP G INNER JOIN
HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
WHERE (H.UNITID = 1 AND H.LISTORDER = 0)|||(meyvn77@.yahoo.com) writes:
> Im using an ADP to connect to a SQL Sqever DB.
> In access it was really easy to say
> Inner join on table1 and table2 and update columnA from table1 with
> columnC from table2 where table1.key = table2.key and table2 columnB =
> 1 and table2 columnD = 4
>
> I have tried all manner of beasts to get this thing to work..
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
> AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))
> This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0' part which is really important
> Any Help would be great...

Unfortunately, it's not very easy to help if we don't know what
tables you have. The standard recommendation for this type of
problem is to post:

o CREATE TABLE statements of the tables nvolved. (Preferrably
cut down to the columns relevant to the problem.(
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the business problem.

The two first points makes it easy to copy and paste into Query Analyzer,
so a tested solution can be developed. The third point makes it possible
to verify that the solution is correct. And the fourth point gives some
extra information which helps to understand the general problem.

> This way looks like it should work also but I get an error 'ADO Error:
> HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
> can only show one table in an update qurey?
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0))
> WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)

No, but because you are referring to HARMFULEVENT outside the subquery.

> AME ADO ERROR WHEN I USE THIS ???
>
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
> FROM GIS_EVENTS_TEMP G INNER JOIN
> HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
> WHERE (H.UNITID = 1 AND H.LISTORDER = 0)

Here you are mixing use of aliases and table name. Once you have
introduced an alias, you can not refer to the full table name in
the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I don't think that you can write something like this:
SELECT HARMFULEVENT FROM HARMFULEVENT

.adp, .ade, MSDE and security

Hi all,
I have an Access Project for the front end and a MSDE as back end. The
database is in a Server and only administrators have access to it. I've
installed a copy of the .adp on each user's computer. I plan to convert to
..ade once the project is finalized.
I have a UserName and password set up (and saved) to connect the project
file to the server.
Next, I need to set up user level security for the forms. When the project
opens, there is a login form asking for username and password, if valid a
main form (switchboard) opens. Based on the login, the user will have access
to certain areas - subforms. Administrators can open all the subforms on the
main form. Managers don't have access to the admin area. And so on.
I need a set up for the Owners of the Company to be able to manage the
permissions from the project file itself, not having to access the database
manager on the server (thanks Andrea, you saved my life with this one). Also
I have a cmd button to change the user, therefor change the permissions.
Is there a way to do this, like creating some code behind the forms, or do I
need to do it on the SQL server?
I've created a table for users with ID's, passwords and departments. Only
the Company owners should be able to access the form to update these
records...
This is an small company without any IT person around and the users need a
simple, straighforward project to use.
Any help and/or ideas will be greatly appreciated.
gaba
hi,
gaba wrote:
> Hi all,
> I have an Access Project for the front end and a MSDE as back end. The
> database is in a Server and only administrators have access to it.
> I've installed a copy of the .adp on each user's computer. I plan to
> convert to .ade once the project is finalized.
> I have a UserName and password set up (and saved) to connect the
> project file to the server.
> Next, I need to set up user level security for the forms. When the
> project opens, there is a login form asking for username and
> password, if valid a main form (switchboard) opens. Based on the
> login, the user will have access to certain areas - subforms.
> Administrators can open all the subforms on the main form. Managers
> don't have access to the admin area. And so on.
> I need a set up for the Owners of the Company to be able to manage the
> permissions from the project file itself, not having to access the
> database manager on the server (thanks Andrea, you saved my life with
> this one). Also I have a cmd button to change the user, therefor
> change the permissions.
> Is there a way to do this, like creating some code behind the forms,
> or do I need to do it on the SQL server?
> I've created a table for users with ID's, passwords and departments.
> Only the Company owners should be able to access the form to update
> these records...
> This is an small company without any IT person around and the users
> need a simple, straighforward project to use.
> Any help and/or ideas will be greatly appreciated.
I think to understand your indtroduction as you use some sort of user
defined application role...
you have a kind of hardcoded user+pwd used throughout all your app for all
your users and an internal tables referencing the "real users" to their
authorized actions...
thus you can not rely on standard SQL Server policies to block unauthorized
users to access some portions of your project...
if this is not correct, then "amen"... nice to hear you again :D
but if this is correct, you are stuck to write your own authorization
policies.. you have then to write a dialog where all "tasks" are listed and
you have to store, in your actual tables, what is granted and what is not...
you can perhaps define a relation like
tb_Users tb_Groups tb_Permissions
-- -- --
Id |--1 Id 1--| Id
Name | Name |--8 IdGroup
Pwd | |--8 IdTask
IdGroup 8--| | Enable/Disable
|
| tb_Tasks
| --
|--1 Id
Name
IdMaster
IdRif
Permission
(hope is readeble)
where you map users (8=many) to 1 group...
tb_Tasks enumerates all your "securable" activities ...
tb_Permissions stores the actual permissions referencing tb_Tasks and
tb_Groups ...
depending on your needs you can default [Enable/Disable] as desired (always
false, always true)...
or, if you need a very little and simple management, you can even drop that
column and insert a row only for denied (or granted) tasks...
relations always are 1 to many (8 stand for many :D) so that 1 user can only
be member of 1 group...
you know that you have not a "trusted" security policy as anyone can grab
oSql.exe, log in as "whatever" and perform whatever action on the database
depending on the "whatever" login's permissions... more. the "user defined
application role" must be enabled to perform whatever activitiy on the
database it self, and even probably perform backup/restore... quite large
permissions for a "normal" user..
can you reconsider the security design?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Nice to hear from you again. Thanks for your answer. The price of learning
under pressure...
The users of this project are going to use forms to manipulate the data.
They'll never have direct access to the database itself. The groups are going
to be
Administrator - all rights.
managers - limited rights
technicians - limited to their areas
data entry - limited to their areas
I need to design the project in a way that any of the members of these
groups can access the .ade file at any computer and get the information they
are allow to view/change. Also they need to login as another user without
leaving the database.
I'll study your ideas and how I can apply them. I was thinking on putting
some VBA code behind the login form and depending on the username and
password verify the group the user belongs to. Based on this info, I'll
disable the buttons on the main form therefore the user only can access their
areas. Is this aproach too naiive? These users don't know/care much about
computers and/or databases. The only thing they care is to access the
information quickly.
Have a nice weekend. I'll let you know if I get somewhere.
Thanks a lot,
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> I think to understand your indtroduction as you use some sort of user
> defined application role...
> you have a kind of hardcoded user+pwd used throughout all your app for all
> your users and an internal tables referencing the "real users" to their
> authorized actions...
> thus you can not rely on standard SQL Server policies to block unauthorized
> users to access some portions of your project...
> if this is not correct, then "amen"... nice to hear you again :D
> but if this is correct, you are stuck to write your own authorization
> policies.. you have then to write a dialog where all "tasks" are listed and
> you have to store, in your actual tables, what is granted and what is not...
> you can perhaps define a relation like
> tb_Users tb_Groups tb_Permissions
> -- -- --
> Id |--1 Id 1--| Id
> Name | Name |--8 IdGroup
> Pwd | |--8 IdTask
> IdGroup 8--| | Enable/Disable
> |
> | tb_Tasks
> | --
> |--1 Id
> Name
> IdMaster
> IdRif
> Permission
> (hope is readeble)
> where you map users (8=many) to 1 group...
> tb_Tasks enumerates all your "securable" activities ...
> tb_Permissions stores the actual permissions referencing tb_Tasks and
> tb_Groups ...
> depending on your needs you can default [Enable/Disable] as desired (always
> false, always true)...
> or, if you need a very little and simple management, you can even drop that
> column and insert a row only for denied (or granted) tasks...
> relations always are 1 to many (8 stand for many :D) so that 1 user can only
> be member of 1 group...
> you know that you have not a "trusted" security policy as anyone can grab
> oSql.exe, log in as "whatever" and perform whatever action on the database
> depending on the "whatever" login's permissions... more. the "user defined
> application role" must be enabled to perform whatever activitiy on the
> database it self, and even probably perform backup/restore... quite large
> permissions for a "normal" user..
> can you reconsider the security design?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

.adp successor front-end to SQL Server 2005

If rumours are true that .adp is likely to be phased out in future MS Access
versions, what front-end with similar functionality (forms, reports, coding)
would you suggest for development and use with MS SQL Server?Personally, I use VB.Net for everything but reporting. It gives you a lot
of flexibility, and automates many tasks (sometimes too much).
For reporting, you can use a thrid party reporting product like Crystal, or
try out SQL Server Reporting Services. I hate Crystal, and haven't really
worked much with SQL Server Reporting Services, but you can check out
newgroups for either and see what folks have to say about the benefits and
limitations of each.
"Lolik" <Lolik@.discussions.microsoft.com> wrote in message
news:817CD550-356C-4FD5-8D2B-BFE6E22379D1@.microsoft.com...
> If rumours are true that .adp is likely to be phased out in future MS
Access
> versions, what front-end with similar functionality (forms, reports,
coding)
> would you suggest for development and use with MS SQL Server?|||Jim, many thanks for replying.
Is VB 2005 the newer version of VB.Net, or a different branch of VB
altogether?
Which would be easier to learn to achieve .adp similar functionality?
"Jim Underwood" wrote:

> Personally, I use VB.Net for everything but reporting. It gives you a lot
> of flexibility, and automates many tasks (sometimes too much).
> For reporting, you can use a thrid party reporting product like Crystal, o
r
> try out SQL Server Reporting Services. I hate Crystal, and haven't really
> worked much with SQL Server Reporting Services, but you can check out
> newgroups for either and see what folks have to say about the benefits and
> limitations of each.
> "Lolik" <Lolik@.discussions.microsoft.com> wrote in message
> news:817CD550-356C-4FD5-8D2B-BFE6E22379D1@.microsoft.com...
> Access
> coding)
>
>|||2005 is just the latest version of .Net. If your company is already using
.Net I would see what version they are on and go with that. If not, then I
would go with the newest version. Either one is going to be more
programming focused than what you are used to, but I don't think either
version will be any easier or more difficult. A good book on programming in
.Net will be helpful.
"Lolik" <Lolik@.discussions.microsoft.com> wrote in message
news:64F18985-1925-42ED-A212-33273A5EE7CC@.microsoft.com...
> Jim, many thanks for replying.
> Is VB 2005 the newer version of VB.Net, or a different branch of VB
> altogether?
> Which would be easier to learn to achieve .adp similar functionality?
>
> "Jim Underwood" wrote:
>
lot
or
really
and|||Thanks Jim..
"Jim Underwood" wrote:

> 2005 is just the latest version of .Net. If your company is already using
> ..Net I would see what version they are on and go with that. If not, then
I
> would go with the newest version. Either one is going to be more
> programming focused than what you are used to, but I don't think either
> version will be any easier or more difficult. A good book on programming
in
> ..Net will be helpful.
> "Lolik" <Lolik@.discussions.microsoft.com> wrote in message
> news:64F18985-1925-42ED-A212-33273A5EE7CC@.microsoft.com...
> lot
> or
> really
> and
>
>