Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    6

    Unanswered: ADO locks and tables with computed columns

    Help me I'm melting.

    SQLServer2000
    ADO (access 2000)

    My ADO recordset to a table with an index which includes a computed column is locked in my form. No updates possible. ANY suggestions at this point would be welcome - it's going on hour 6 with this an related probs.

    TIA

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: ADO locks and tables with computed columns

    Originally posted by zarathustra
    Help me I'm melting.

    SQLServer2000
    ADO (access 2000)

    My ADO recordset to a table with an index which includes a computed column is locked in my form. No updates possible. ANY suggestions at this point would be welcome - it's going on hour 6 with this an related probs.

    TIA
    Just to be sure: did you create a primary key on the table?

    Ad Dieleman.

  3. #3
    Join Date
    Feb 2003
    Posts
    6

    Re: ADO locks and tables with computed columns

    Yes. The Primary Key is composite 3 columns with the third column being the computed value.

    SQL2000 is fine with all of this, but I'm not sure what kind of lock ADO is taking out behind the scenes but the DataSheet on my form is locked where Datasheet.Recordset = (select from the table) - roughly.

    Any help is appreciated.

    TIA

  4. #4
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: ADO locks and tables with computed columns

    A few things you can check:

    1) Open the Query Analyzer of SQL Server and check if you can update the table. If not, something's wrong with your permissions.

    2) Open the recordset with cursortype adOpenDynamic. The default is adOpenForwardOnly which doesn't allow any changes on the recordset.
    You'd need a statement like
    'rstRes.Open CursorType:=adOpenDynamic'

    3) I don't know if the recordset will be updateable when using a calculated column in the primary key. You can find this out by adding an identity field which you then use as a primary key and maintaining a unique index for your original primary key.

    Ad Dieleman.

  5. #5
    Join Date
    Feb 2003
    Posts
    6

    Re: ADO locks and tables with computed columns

    Originally posted by Ad Dieleman
    A few things you can check:

    1) Open the Query Analyzer of SQL Server and check if you can update the table. If not, something's wrong with your permissions.

    2) Open the recordset with cursortype adOpenDynamic. The default is adOpenForwardOnly which doesn't allow any changes on the recordset.
    You'd need a statement like
    'rstRes.Open CursorType:=adOpenDynamic'

    3) I don't know if the recordset will be updateable when using a calculated column in the primary key. You can find this out by adding an identity field which you then use as a primary key and maintaining a unique index for your original primary key.

    Ad Dieleman.
    1) would not have been a permission thing.
    2) yes
    3) it's definitely the computed field in the key that's doing the locking - ADO is doing something in the background and is documented terribly (having said that I'm no VB programmer)

    But using an identity PK + the UNIQUE constraint DOES work. Thanks tons for making me rethink. No more bad data for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •