Permission problems with Access and SQL

[Marcin Zmyslowski]

Hello!

I have a few tables in MS SQL Server 2000, where I have admin
permissions for these tables. I have created a blank database in MS
Access 2003, where I linked a table from MS SQL Server 2000. The
problems is that I have only read permissions on these tables in MS
Access 2003, while I have admin permissions in MS SQL Server 2000. Is it
possible to have the sime permission in both MS Access 2003 and MS SQL
Server 2000? Is it possible to add new records from MS Access 2003 into
linked SQL tables? I have created ODBC connection which is
Windows_authenticated to have access to these tables in MS Access 2003.

Could you help me? I would be very grateful for help...

Marcin from Poland

*** Sent via Developersdex http://www.developersdex.com ***

[Justin Hoffman]

"Marcin Zmyslowski" <ma*****@kapy.bydg.pl> wrote in message
news:qp**************@news.uswest.net...

Hello!

I have a few tables in MS SQL Server 2000, where I have admin
permissions for these tables. I have created a blank database in MS
Access 2003, where I linked a table from MS SQL Server 2000. The
problems is that I have only read permissions on these tables in MS
Access 2003, while I have admin permissions in MS SQL Server 2000. Is it
possible to have the sime permission in both MS Access 2003 and MS SQL
Server 2000? Is it possible to add new records from MS Access 2003 into
linked SQL tables? I have created ODBC connection which is
Windows_authenticated to have access to these tables in MS Access 2003.

Could you help me? I would be very grateful for help...

Marcin from Poland

Czesc Marcin
Do these tables have primary keys? Without primary keys Access cannot
update the rows even though you can via the Enterprise Manager.

[Marcin Zmyslowski]

I know why the problem has occurred. I didn`t set the unique key while
linking SQL tables into Access. Without it there is no update
permissions.

Marcin

*** Sent via Developersdex http://www.developersdex.com ***

[Justin Hoffman]

"Marcin Zmyslowski" <ma*****@kapy.bydg.pl> wrote in message
news:lf***************@news.uswest.net...

I know why the problem has occurred. I didn`t set the unique key while
linking SQL tables into Access. Without it there is no update
permissions.

Marcin

If you have any control over the design of the SQL Server tables, then you
should make sure they have a primary key. Then Access would not require you
to set a unique key at all - it would be done automatically. If you cannot
alter the design of the tables, then setting this index may be your only
option.

[Br]

Justin Hoffman <[e-mail]> wrote:

"Marcin Zmyslowski" <ma*****@kapy.bydg.pl> wrote in message
news:lf***************@news.uswest.net...

I know why the problem has occurred. I didn`t set the unique key
Did you mean setting the Unique Table on the form's properties?
while linking SQL tables into Access. Without it there is no update
permissions.

Marcin

If you have any control over the design of the SQL Server tables,
then you should make sure they have a primary key. Then Access would
not require you to set a unique key at all - it would be done
automatically. If you cannot alter the design of the tables, then
setting this index may be your only option.

Also, make sure all BIT fields in your tables have a default value else
you'll end up with the same issue.

And FYI if you want to be able to update VIEWS in Access2000 you need to
add use "create myprocedure WITH VIEW_METADATA ....." to the views
creation script .
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response