Newbie Question - Edit data on SQL Server using Data access pages

I have been and SQL administrator for about 6 hours now. I grabbed a couple
books and have been doing searches online and reading through some MSDN
online stuff. Having never seen SQL but having done some Access it wasn't
too hard to get a database set up on the server.
I managed to import an old access database with tables into the SQL database
on the server. Easy enough. I then managed to set up a new Access Project
on a local machine pointing at the server and connect to the database quite
easily.
Then I built an form based on the primary table. Easy enough. I had to go
into form properties and enable edit so that the form would open. Otherwise
it just opened as a blank form page with no fields.
I can also build data access pages that display all the data in the database
which is wonderful and I'm really starting to see how powerful and wonderful
SQL is going to be.
Only problem is everything I do creates read only. I can do a data access
page, a form or a table but even with edit enabled I only get read only
access to the data. Any attempts to modify data results in the following
message in the taskbar.
On data access page: No message just don't allow edits or typing.
On Form in access: Field "XXX" is based on an expression and cant be
edited.
On Table view: The recordset is not updatable.
I'm sure once I spend a few more hours beating around I'll figure this out
and I'm sure its just because of my naivety in all things SQL but I was
hoping someone would have a quick pointer to get me back on the development
track.
Thanks again for all your help.
Doc
The SQL server user account accessing the SQL server from access has to
have write access.
and more importantly the table needs to have a unique index else you will
not be able to edit the recordset/Table in access.
|||Check if the table has a primary key, if not, create the pk and them relink
the tables.
AMB
"Donald Watson" wrote:

> I have been and SQL administrator for about 6 hours now. I grabbed a couple
> books and have been doing searches online and reading through some MSDN
> online stuff. Having never seen SQL but having done some Access it wasn't
> too hard to get a database set up on the server.
> I managed to import an old access database with tables into the SQL database
> on the server. Easy enough. I then managed to set up a new Access Project
> on a local machine pointing at the server and connect to the database quite
> easily.
> Then I built an form based on the primary table. Easy enough. I had to go
> into form properties and enable edit so that the form would open. Otherwise
> it just opened as a blank form page with no fields.
> I can also build data access pages that display all the data in the database
> which is wonderful and I'm really starting to see how powerful and wonderful
> SQL is going to be.
> Only problem is everything I do creates read only. I can do a data access
> page, a form or a table but even with edit enabled I only get read only
> access to the data. Any attempts to modify data results in the following
> message in the taskbar.
> On data access page: No message just don't allow edits or typing.
> On Form in access: Field "XXX" is based on an expression and cant be
> edited.
> On Table view: The recordset is not updatable.
> I'm sure once I spend a few more hours beating around I'll figure this out
> and I'm sure its just because of my naivety in all things SQL but I was
> hoping someone would have a quick pointer to get me back on the development
> track.
> Thanks again for all your help.
> Doc
>
>
|||It was the missing primary key.
Now I can develop to my hearts content.
Thanks so much for the fast response.
Doc
sql

Newbie Question - Edit data on SQL Server using Data access pages

I have been and SQL administrator for about 6 hours now. I grabbed a couple
books and have been doing searches online and reading through some MSDN
online stuff. Having never seen SQL but having done some Access it wasn't
too hard to get a database set up on the server.
I managed to import an old access database with tables into the SQL database
on the server. Easy enough. I then managed to set up a new Access Project
on a local machine pointing at the server and connect to the database quite
easily.
Then I built an form based on the primary table. Easy enough. I had to go
into form properties and enable edit so that the form would open. Otherwise
it just opened as a blank form page with no fields.
I can also build data access pages that display all the data in the database
which is wonderful and I'm really starting to see how powerful and wonderful
SQL is going to be.
Only problem is everything I do creates read only. I can do a data access
page, a form or a table but even with edit enabled I only get read only
access to the data. Any attempts to modify data results in the following
message in the taskbar.
On data access page: No message just don't allow edits or typing.
On Form in access: Field "XXX" is based on an expression and cant be
edited.
On Table view: The recordset is not updatable.
I'm sure once I spend a few more hours beating around I'll figure this out
and I'm sure its just because of my naivety in all things SQL but I was
hoping someone would have a quick pointer to get me back on the development
track.
Thanks again for all your help.
DocThe SQL server user account accessing the SQL server from access has to
have write access.
and more importantly the table needs to have a unique index else you will
not be able to edit the recordset/Table in access.|||Check if the table has a primary key, if not, create the pk and them relink
the tables.
AMB
"Donald Watson" wrote:
> I have been and SQL administrator for about 6 hours now. I grabbed a couple
> books and have been doing searches online and reading through some MSDN
> online stuff. Having never seen SQL but having done some Access it wasn't
> too hard to get a database set up on the server.
> I managed to import an old access database with tables into the SQL database
> on the server. Easy enough. I then managed to set up a new Access Project
> on a local machine pointing at the server and connect to the database quite
> easily.
> Then I built an form based on the primary table. Easy enough. I had to go
> into form properties and enable edit so that the form would open. Otherwise
> it just opened as a blank form page with no fields.
> I can also build data access pages that display all the data in the database
> which is wonderful and I'm really starting to see how powerful and wonderful
> SQL is going to be.
> Only problem is everything I do creates read only. I can do a data access
> page, a form or a table but even with edit enabled I only get read only
> access to the data. Any attempts to modify data results in the following
> message in the taskbar.
> On data access page: No message just don't allow edits or typing.
> On Form in access: Field "XXX" is based on an expression and cant be
> edited.
> On Table view: The recordset is not updatable.
> I'm sure once I spend a few more hours beating around I'll figure this out
> and I'm sure its just because of my naivety in all things SQL but I was
> hoping someone would have a quick pointer to get me back on the development
> track.
> Thanks again for all your help.
> Doc
>
>|||It was the missing primary key.
Now I can develop to my hearts content.
Thanks so much for the fast response.
Doc

Newbie Question - Edit data on SQL Server using Data access pages

I have been and SQL administrator for about 6 hours now. I grabbed a couple
books and have been doing searches online and reading through some MSDN
online stuff. Having never seen SQL but having done some Access it wasn't
too hard to get a database set up on the server.
I managed to import an old access database with tables into the SQL database
on the server. Easy enough. I then managed to set up a new Access Project
on a local machine pointing at the server and connect to the database quite
easily.
Then I built an form based on the primary table. Easy enough. I had to go
into form properties and enable edit so that the form would open. Otherwise
it just opened as a blank form page with no fields.
I can also build data access pages that display all the data in the database
which is wonderful and I'm really starting to see how powerful and wonderful
SQL is going to be.
Only problem is everything I do creates read only. I can do a data access
page, a form or a table but even with edit enabled I only get read only
access to the data. Any attempts to modify data results in the following
message in the taskbar.
On data access page: No message just don't allow edits or typing.
On Form in access: Field "XXX" is based on an expression and cant be
edited.
On Table view: The recordset is not updatable.
I'm sure once I spend a few more hours beating around I'll figure this out
and I'm sure its just because of my naivety in all things SQL but I was
hoping someone would have a quick pointer to get me back on the development
track.
Thanks again for all your help.
DocThe SQL server user account accessing the SQL server from access has to
have write access.
and more importantly the table needs to have a unique index else you will
not be able to edit the recordset/Table in access.|||Check if the table has a primary key, if not, create the pk and them relink
the tables.
AMB
"Donald Watson" wrote:

> I have been and SQL administrator for about 6 hours now. I grabbed a coup
le
> books and have been doing searches online and reading through some MSDN
> online stuff. Having never seen SQL but having done some Access it wasn't
> too hard to get a database set up on the server.
> I managed to import an old access database with tables into the SQL databa
se
> on the server. Easy enough. I then managed to set up a new Access Projec
t
> on a local machine pointing at the server and connect to the database quit
e
> easily.
> Then I built an form based on the primary table. Easy enough. I had to g
o
> into form properties and enable edit so that the form would open. Otherwi
se
> it just opened as a blank form page with no fields.
> I can also build data access pages that display all the data in the databa
se
> which is wonderful and I'm really starting to see how powerful and wonderf
ul
> SQL is going to be.
> Only problem is everything I do creates read only. I can do a data access
> page, a form or a table but even with edit enabled I only get read only
> access to the data. Any attempts to modify data results in the following
> message in the taskbar.
> On data access page: No message just don't allow edits or typing.
> On Form in access: Field "XXX" is based on an expression and cant be
> edited.
> On Table view: The recordset is not updatable.
> I'm sure once I spend a few more hours beating around I'll figure this out
> and I'm sure its just because of my naivety in all things SQL but I was
> hoping someone would have a quick pointer to get me back on the developmen
t
> track.
> Thanks again for all your help.
> Doc
>
>|||It was the missing primary key.
Now I can develop to my hearts content.
Thanks so much for the fast response.
Doc

newbie question

Hello,
I am studying for my exam and I have a question. I have a sql server
in a domain and when the domain administrator tries to access it, he
has no problems!! But in the literature it says that a domain
administrator must be in a group that has access to the sql server in
order to connect to that server, which is not the case? Does it have
somehing to do with the builtin\administrator account?
Kind regards,
Nico
Yes. By default, a domain admin will get added to the local admin group when
the box joins the domain. That means a domain admin is part of the
builtin\admin group. Thus, he/she has full access to sqlserver.
"Nico" <nico.barbe@.mobiusREMOVEFORSPAM.be> wrote in message
news:eZXCly2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am studying for my exam and I have a question. I have a sql server
> in a domain and when the domain administrator tries to access it, he
> has no problems!! But in the literature it says that a domain
> administrator must be in a group that has access to the sql server in
> order to connect to that server, which is not the case? Does it have
> somehing to do with the builtin\administrator account?
> Kind regards,
> Nico
|||By default, domain admin will be a member of local Administrators group
which essentially maps to BUILTIN\ADMIN account on SQL Server. However if
for some reason, someone has disabled or removed the builtin\admin account
from SQL, if the domain admin is part of another group on the server, and
if that other group is a registered user within SQL Server, then the domain
admin will have access to SQL through this other group even if
builtin\admin is disabled or removed.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

newbie question

Hello,
I am studying for my exam and I have a question. I have a sql server
in a domain and when the domain administrator tries to access it, he
has no problems!! But in the literature it says that a domain
administrator must be in a group that has access to the sql server in
order to connect to that server, which is not the case? Does it have
somehing to do with the builtin\administrator account?
Kind regards,
NicoYes. By default, a domain admin will get added to the local admin group when
the box joins the domain. That means a domain admin is part of the
builtin\admin group. Thus, he/she has full access to sqlserver.
"Nico" <nico.barbe@.mobiusREMOVEFORSPAM.be> wrote in message
news:eZXCly2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am studying for my exam and I have a question. I have a sql server
> in a domain and when the domain administrator tries to access it, he
> has no problems!! But in the literature it says that a domain
> administrator must be in a group that has access to the sql server in
> order to connect to that server, which is not the case? Does it have
> somehing to do with the builtin\administrator account?
> Kind regards,
> Nico|||By default, domain admin will be a member of local Administrators group
which essentially maps to BUILTIN\ADMIN account on SQL Server. However if
for some reason, someone has disabled or removed the builtin\admin account
from SQL, if the domain admin is part of another group on the server, and
if that other group is a registered user within SQL Server, then the domain
admin will have access to SQL through this other group even if
builtin\admin is disabled or removed.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

newbie question

Hello,
I am studying for my exam and I have a question. I have a sql server
in a domain and when the domain administrator tries to access it, he
has no problems!! But in the literature it says that a domain
administrator must be in a group that has access to the sql server in
order to connect to that server, which is not the case? Does it have
somehing to do with the builtin\administrator account?
Kind regards,
NicoYes. By default, a domain admin will get added to the local admin group when
the box joins the domain. That means a domain admin is part of the
builtin\admin group. Thus, he/she has full access to sqlserver.
"Nico" <nico.barbe@.mobiusREMOVEFORSPAM.be> wrote in message
news:eZXCly2jEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am studying for my exam and I have a question. I have a sql server
> in a domain and when the domain administrator tries to access it, he
> has no problems!! But in the literature it says that a domain
> administrator must be in a group that has access to the sql server in
> order to connect to that server, which is not the case? Does it have
> somehing to do with the builtin\administrator account?
> Kind regards,
> Nico|||By default, domain admin will be a member of local Administrators group
which essentially maps to BUILTIN\ADMIN account on SQL Server. However if
for some reason, someone has disabled or removed the builtin\admin account
from SQL, if the domain admin is part of another group on the server, and
if that other group is a registered user within SQL Server, then the domain
admin will have access to SQL through this other group even if
builtin\admin is disabled or removed.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Newbee question about permissions and sp_OACreate

Hi,
I'm creating a trigger that shall monitor a table on inserts and if certain
conditions are met, it should send a mail the administrator.
I do not want to use SQLmail for many reasons so I'm using the extended
procedure sp_OACreate to create an instance of the CDO.Message object
The problem is that only symins can use the extended procedure
sp_OACreate, and of cause I do not want users to be symins.
I think I read or learned somewhere that stored procedures runs under the
security context of the user that created the procedure, so why can't I as
the symin create a my_sendmail procedure that uses the sp_OACreate
extended procedure and grant permission to all users to the my_sendmail
procedure?
Or, is there another way?
Regards,
Jorgen D.First of all, this excedes the purpose of triggers. What has sending mail go
t
to do with ACID? It would be much more efficient if you created a job to
monitor the table and log these special events in a separate table/file and
mail the report/warning.
Second, why is it not possible for you to use SQL Mail? It's far more
efficient than (ab)using sp_OA procedures. And it can be used by any user
without compromising the security.
How about notification services? If mailing these messages was requested by
a manager, you can be pretty sure, he'll start hating it after about a month
.
Either the messages will be too frequent or too infrequent and he'll still
need to keep statistics somewhere, somehow. But I'm just guessing here...
Anyway, seems to me that what you really need is some sort of logical event
logging. This IS the purpose of triggers - propagating changes in one table
to another.
Be careful out there...
ML|||Why do some people answer a question with more questions? (no, thanks for
your input)
My trigger do populate data to other tables (sending work orders to PDA’s,
via merge replication), and when done it’s important that the users
implicated (the ones that gets the work order) gets an mail/sms to inform
them about it (they should react to it immediately)
This mail shall originate form different senders, therefore not SQL-mail
which is based on a single profile (perhaps there is a way around that that
I
didn’t find)
Another reason not to use SQL-mail is in another scenario, that I’m not
going to explain in detail, but the mail should be HTML formatted report
larger than 8000 bytes.
Notification services? Hmmm, I’ll look into that, (as I don’t know what
that
is)
But to get back to my original question….. If I want to use an extended
procedure in my own procedure, how can I set permissions so the ordinary
mortal users can execute that?
Regards,
Jorgen D.
"ML" wrote:

> First of all, this excedes the purpose of triggers. What has sending mail
got
> to do with ACID? It would be much more efficient if you created a job to
> monitor the table and log these special events in a separate table/file an
d
> mail the report/warning.
> Second, why is it not possible for you to use SQL Mail? It's far more
> efficient than (ab)using sp_OA procedures. And it can be used by any user
> without compromising the security.
> How about notification services? If mailing these messages was requested b
y
> a manager, you can be pretty sure, he'll start hating it after about a mon
th.
> Either the messages will be too frequent or too infrequent and he'll still
> need to keep statistics somewhere, somehow. But I'm just guessing here...
> Anyway, seems to me that what you really need is some sort of logical even
t
> logging. This IS the purpose of triggers - propagating changes in one tabl
e
> to another.
> Be careful out there...
>
> ML|||Any user who needs to execute sp_OA procedures must have appropriate
privileges to do so.
I'm sorry, I'm simply curious as to what you're trying to do. You say
certain users must be notified (with a large report, even) of certain change
s
to yor data. And this must be done immediately after those changes were
commited. Why? Do this users then do something? Couldn't that be done
automatically?
It's like in that nuclear plant: "push this button when that light goes on"
- why not just automatically do whatever the button does when conditions are
met which normally tur the light on...?
Instead of sending huge reports you could simply send a link to the report
built by the SQL Reporting Services.
Consider the alternatives before you compromise security.
ML|||Some users are security guards which have to respond to alarms. When they ge
t
notified they should turn on their PDA and connect to the server to get
detailed information of the alarm (address, codes etc.). Other users are
doing emergency road help. So the notification is important to be timely.
They can’t ask the server/system to drive to an incident.
About the reports – They aren’t huge. A single HTML formatted page (tabl
es,
and colours) is larger than 8K which is the limit of SQL-mail, further more
I
can’t get SQL-mail to send in HTML. The reports are requested from the use
rs
PDA to be sent to the customers email. The reports are dynamically created o
n
the server and can’t be sent from the PDA (they don’t have the data)
Conclusion: Since I have to use CDO.Message I have to grant all users public
access to the master database, and then on the sp_OAxxxxx procedures, give
the users EXEC rights. What happened to the ideology “The code of a stored
procedure runs within the security context of the creator”?
"JorgenD" wrote:
> Why do some people answer a question with more questions? (no, thanks for
> your input)
> My trigger do populate data to other tables (sending work orders to PDA’
s,
> via merge replication), and when done it’s important that the users
> implicated (the ones that gets the work order) gets an mail/sms to inform
> them about it (they should react to it immediately)
> This mail shall originate form different senders, therefore not SQL-mail
> which is based on a single profile (perhaps there is a way around that tha
t I
> didn’t find)
> Another reason not to use SQL-mail is in another scenario, that I’m not
> going to explain in detail, but the mail should be HTML formatted report
> larger than 8000 bytes.
> Notification services? Hmmm, I’ll look into that, (as I don’t know wha
t that
> is)
> But to get back to my original question….. If I want to use an extended
> procedure in my own procedure, how can I set permissions so the ordinary
> mortal users can execute that?
> Regards,
> Jorgen D.
>
> "ML" wrote:
>|||You're describing a messaging and reporting system for which web services ar
e
the ideal solution. You have a central data server or a data warehouse and
several clients, that aren't capable of maintaining a 24/7 connection to HQ.
Wouldn't it be more efficient if the clients would only receive short
messages, rather than full reports? Upon the notification they could then
connect to the Report server to see the details of the arising situation and
then decide if they need to download them for off-line use.
That way you can allow access to the same data to the PDA users and to the
desktop users.
You might end up developing a messaging and reporting system using
webservices and smart clients. You could even earn $50 grand!!! :)
(http://www.csdevcompetition.com/)
Giving master database/extended procedure privileges to the Public role is a
bad idea. It is IMHO essential to use a custom role for this - with
explicitly limited privileges.
Also consider the fact that your inserts might slow down due to the needed
complexity of these triggers - that is if the conditions are met frequently.
Of course you'll need a lot of error-handling as well - you don't want to
lock up the server, do you?
ML|||Hi
Look at SQL Server Notification Services to kick of a process where you have
a .NET application send the mail via SMTP.
If you kick off an sp_OA* within a trigger, and it fails, you end up having
a rolled back transaction, possible some e-mails being sent about data that
no longer exists.
If you do not want to use NS, then in your trigger, write a row to another
table indicate that something must be done, and then have SQL Server Agent
process poll the table and send the e-mail.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ML" wrote:

> You're describing a messaging and reporting system for which web services
are
> the ideal solution. You have a central data server or a data warehouse and
> several clients, that aren't capable of maintaining a 24/7 connection to H
Q.
> Wouldn't it be more efficient if the clients would only receive short
> messages, rather than full reports? Upon the notification they could then
> connect to the Report server to see the details of the arising situation a
nd
> then decide if they need to download them for off-line use.
> That way you can allow access to the same data to the PDA users and to the
> desktop users.
> You might end up developing a messaging and reporting system using
> webservices and smart clients. You could even earn $50 grand!!! :)
> (http://www.csdevcompetition.com/)
>
> Giving master database/extended procedure privileges to the Public role is
a
> bad idea. It is IMHO essential to use a custom role for this - with
> explicitly limited privileges.
> Also consider the fact that your inserts might slow down due to the needed
> complexity of these triggers - that is if the conditions are met frequentl
y.
> Of course you'll need a lot of error-handling as well - you don't want to
> lock up the server, do you?
>
> ML