locked table records on sql 2005 using access97 query

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

    locked table records on sql 2005 using access97 query

    on sql 2005, I've got a view with select permission granted, the view
    just "select * from table"
    using odbc in access97, I linked this view and I create a query to
    retrieve certain fields
    the query properties are 'no locks' and dynaset record type

    but just executing the query as a datasheet, locks some records in the
    original sql server table
    is this how it should work ? seems to me that it should not lock
    anything

    what if I use a snapshot record type, would that not lock records ?


    Tags: None
    • Roger
      #2
      Re: locked table records on sql 2005 using access97 query

      On May 9, 8:05 am, Roger <lesperan...@na tpro.comwrote:
      on sql 2005, I've got a view with select permission granted, the view
      just "select * from table"
      using odbc in access97, I linked this view and I create a query to
      retrieve certain fields
      the query properties are 'no locks' and dynaset record type
      >
      but just executing the query as a datasheet, locks some records in the
      original sql server table
      is this how it should work ? seems to me that it should not lock
      anything
      >
      what if I use a snapshot record type, would that not lock records ?
      I moved the query to sql server, granted select permission, and now
      I'm using this view as a linked table, same problem

      if I just open the table (view) in access, and use the sql activity
      monitor, it shows many page locks, and I not sure why since I'm not
      scrolling through the records

        Comment

        • Rich P
          #3
          Re: locked table records on sql 2005 using access97 query

          Hi Roger,

          The problem you are having is with the ODBC connection. ODBC
          connections are continuous, thus, you will have record locking issues,
          contention issues, and all sorts of other issues because of this
          continuous connection. The fix is to not have a continous connection.
          This is achieved by pulling the data you want to read into the local mdb
          into a local table. You could use an append query to append (Insert
          Into) records from the view to your local table, but a more reliable
          method would be to use ADO.

          ODBC is old technology and it was pretty state of the art when
          everything was small. But, as data processing operations have evolved,
          the demands are exceeding what ODBC (and Access altogehter) was
          originally designed for. The current-permanent fix for all this (in the
          Microsoft environment) is to migrate to disconnected data processing of
          .Net (for data processing operations that involve servers - especially
          the newer ones like sql server 2005 and higher).

          Rich

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

            Comment

            • Rick Brandt
              #4
              Re: locked table records on sql 2005 using access97 query

              Roger wrote:
              on sql 2005, I've got a view with select permission granted, the view
              just "select * from table"
              using odbc in access97, I linked this view and I create a query to
              retrieve certain fields
              the query properties are 'no locks' and dynaset record type
              >
              but just executing the query as a datasheet, locks some records in the
              original sql server table
              is this how it should work ? seems to me that it should not lock
              anything
              >
              what if I use a snapshot record type, would that not lock records ?
              Just don't open the link as a datasheet. Most other uses (forms, reports,
              etc.) will not have this problem.

              --
              Rick Brandt, Microsoft Access MVP
              Email (as appropriate) to...
              RBrandt at Hunter dot com


                Comment

                • Roger
                  #5
                  Re: locked table records on sql 2005 using access97 query

                  On May 13, 5:46 pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:
                  Roger wrote:
                  on sql 2005, I've got a view with select permission granted, the view
                  just "select * from table"
                  using odbc in access97, I linked this view and I create a query to
                  retrieve certain fields
                  the query properties are 'no locks' and dynaset record type
                  >
                  but just executing the query as a datasheet, locks some records in the
                  original sql server table
                  is this how it should work ? seems to me that it should not lock
                  anything
                  >
                  what if I use a snapshot record type, would that not lock records ?
                  >
                  Just don't open the link as a datasheet.  Most other uses (forms, reports,
                  etc.) will not have this problem.
                  >
                  --
                  Rick Brandt, Microsoft Access MVP
                  Email (as appropriate) to...
                  RBrandt   at   Hunter   dot   com
                  turns out the form has the problem too
                  but I found the fix in creating the sql view
                  instead of the view being
                  select * from table

                  I'm using
                  select * from table (nolock)

                  seems to work so far using either datasheet or a form

                    Comment

                    Previous template Next
                    Working...