Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Angry Unanswered: Locking records through ODBC.

    I am connecting to a MS-Access database through an ODBC connection and I want to lock a particular record using a SQL statement.

    In most databases, issuing something like the following would do the job :

    SELECT col1, col2 FOR UPDATE
    FROM tablename
    WHERE condition

    This doesn't work with MS-Access. Anybody has an idea of how I could lock a record in SQL ? Is there anything such as an "isolation level" for locking in Access? Anyone has a good link/document I can read on how Access handles locking via ODBC ?

    Thanks.

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    The finest record locking detail you will get with access is at a page of records. Record locking is usually done at the recordset object. I would start my investigation there.

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by access_dude
    The finest record locking detail you will get with access is at a page of records. Record locking is usually done at the recordset object. I would start my investigation there.
    Well, what you're talking about is something totally unrelated. I don't care about HOW locking is implemented, I just wanna know how to lock a record via SQL. Access and SQL Server use page locking, MySQL without InnoDb uses table locking, etc. but regardless of HOW it is done, I want to know what SQL syntax I have to use to lock the record!

  4. #4
    Join Date
    Mar 2002
    Posts
    192
    First of all when you say SQL that does not mean SQL Server. Get it right or you'll confuse people that are trying to help you. Secondly, SQL Server can use page locking but can lock on a finer level (down to the row). As for the recordset objects you can use them in DTS to access the query/table in question with record locks so that is completely relavent.

    Anyhow you can set locking via the menus in MS Access.

    Set Options->Advanced->Default record locking to "Edited Record"

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by access_dude
    First of all when you say SQL that does not mean SQL Server. Get it right or you'll confuse people that are trying to help you. Secondly, SQL Server can use page locking but can lock on a finer level (down to the row). As for the recordset objects you can use them in DTS to access the query/table in question with record locks so that is completely relavent.

    Anyhow you can set locking via the menus in MS Access.

    Set Options->Advanced->Default record locking to "Edited Record"
    I mean I am not interested by the way the database manager implements locking (whether it is page based or table based) but rather HOW I can specify via an ODBC connection a SQL STATEMENT to issue a lock.

  6. #6
    Join Date
    Mar 2002
    Posts
    192
    Anyhow my last post should help you with your problem. If not let me know and I'll see if there is anything else i can figure out for you.

Posting Permissions

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