Permission problems with Access and SQL

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Marcin Zmyslowski
    #1

    Permission problems with Access and SQL

    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_authent icated 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 ***
    Tags: None
    • Justin Hoffman
      #2
      Re: Permission problems with Access and SQL


      "Marcin Zmyslowski" <marcinz@kapy.b ydg.pl> wrote in message
      news:qpKFe.7$O0 1.2247@news.usw est.net...[color=blue]
      > 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_authent icated to have access to these tables in MS Access 2003.
      >
      > Could you help me? I would be very grateful for help...
      >
      > Marcin from Poland[/color]


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



        Comment

        • Marcin Zmyslowski
          #3
          Re: Permission problems with Access and SQL



          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 ***

            Comment

            • Justin Hoffman
              #4
              Re: Permission problems with Access and SQL

              "Marcin Zmyslowski" <marcinz@kapy.b ydg.pl> wrote in message
              news:lfNFe.16$v E3.1934@news.us west.net...[color=blue]
              >
              >
              > 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[/color]

              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.


                Comment

                • Br@dley
                  #5
                  Re: Permission problems with Access and SQL

                  Justin Hoffman <j@b.com> wrote:[color=blue]
                  > "Marcin Zmyslowski" <marcinz@kapy.b ydg.pl> wrote in message
                  > news:lfNFe.16$v E3.1934@news.us west.net...[color=green]
                  >>
                  >>
                  >> I know why the problem has occurred. I didn`t set the unique key[/color][/color]

                  Did you mean setting the Unique Table on the form's properties?
                  [color=blue][color=green]
                  >> while linking SQL tables into Access. Without it there is no update
                  >> permissions.
                  >>
                  >> Marcin[/color][/color]
                  [color=blue]
                  > 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.[/color]


                  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



                    Comment

                    Previous template Next
                    Working...