Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

Locked records in Access/Ms SQL Server

  • 29-04-2008 11:01AM
    #1
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭


    Hi,

    I wrote an Excel Macro in VBA which imports records into a SQL server database & an access database.

    The import went fine, however the users cannot edit or delete any of the rows imported because of a Write conflict error message.

    I have asked the users to restart their SQL server and the Access clients but it has had no effect. Is there any way of clearing the record locks?


Comments

  • #2
    sicruise
    Registered Users, Registered Users 2 Posts: 378 ✭✭sicruise


    Make sure first of all the user that they are using actually has permission to perform these actions.

    This site HERE seems to have good info on MSSQL locks. I'm more of an mySQL man myself.


  • #3
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭John_Mc


    sicruise wrote: »
    Make sure first of all the user that they are using actually has permission to perform these actions.

    This site HERE seems to have good info on MSSQL locks. I'm more of an mySQL man myself.

    Thanks for your reply. Yes, I'm pretty sure they have permission to be performing these actions.

    That article was helpful but it did not cover how to remove locks applied to rows... :(


  • #4
    amen
    Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    restarting your SQL Server should have cleared locks but that is a last resort.
    You should never really be restarting SQL Server.

    Have you tried on you test/dev environment and do you get the same issue?

    Have you checked the SQL Server Error logs and is the same error there?


  • #5
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭John_Mc


    amen wrote: »
    restarting your SQL Server should have cleared locks but that is a last resort.
    You should never really be restarting SQL Server.

    Have you tried on you test/dev environment and do you get the same issue?

    Have you checked the SQL Server Error logs and is the same error there?

    No, there were no problems like this in the development environment.

    I took a backup of their DB and restored it so they would be similar setups. Will get a copy of the SQL server logs and take a look.

    Thanks


  • #6
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭John_Mc


    John_Mc wrote: »
    No, there were no problems like this in the development environment.

    I took a backup of their DB and restored it so they would be similar setups. Will get a copy of the SQL server logs and take a look.

    Thanks

    I have checked the logs and there doesn't appear to be anything out of the ordinary. Any other options open to me? :confused:


  • Advertisement
  • #7
    tiptap
    Closed Accounts Posts: 317 ✭✭tiptap


    Hi,
    The problem is to do with how your recordset is set.
    I've had this problem on numerous occassions with access and sql server.

    Can you post your code which opens your recordset ?

    Do you have dbopendynaset specified ?


  • #8
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭John_Mc


    tiptap wrote: »
    Hi,
    The problem is to do with how your recordset is set.
    I've had this problem on numerous occassions with access and sql server.

    Can you post your code which opens your recordset ?

    Do you have dbopendynaset specified ?

    I will post the code snippet tomorrow when I'm in work, but I do not recall setting dbopendynaset.

    Can this be fixed without having to reimport the data?

    Thanks


  • #9
    tiptap
    Closed Accounts Posts: 317 ✭✭tiptap


    John_Mc wrote: »
    I will post the code snippet tomorrow when I'm in work, but I do not recall setting dbopendynaset.

    Can this be fixed without having to reimport the data?

    Thanks


    It can indeed, no need for another import...
    I'm away for most of the day tomorrow
    but it's something like
    db.openrecordset("select blah", dbopendynaset)
    try that,
    if it doesn't work, there is another paramater, can't remember the name of, but it implicity forces to open the recordset for read and write..

    dynaset should get you up and running though....
    google the error as well (I'm sure you have)

    It's a pain of an error but easily fixed..

    good luck


  • #10
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭John_Mc


    tiptap wrote: »
    It can indeed, no need for another import...
    I'm away for most of the day tomorrow
    but it's something like
    db.openrecordset("select blah", dbopendynaset)
    try that,
    if it doesn't work, there is another paramater, can't remember the name of, but it implicity forces to open the recordset for read and write..

    dynaset should get you up and running though....
    google the error as well (I'm sure you have)

    It's a pain of an error but easily fixed..

    good luck

    Ok nice one, will try this tomorrow. Thanks a million for your help :)


  • #11
    tiptap
    Closed Accounts Posts: 317 ✭✭tiptap


    John_Mc wrote: »
    Ok nice one, will try this tomorrow. Thanks a million for your help :)


    No worries,
    Ok, I just dug out my old code...

    the other paramater to use is
    dbSeeChanges

    so it should look like

    db.openrecordset("blah",dbopendynaset,dbseechanges)

    Also, have a look at this link, from about half way down about editing recordsets, it's a good link and explains it well.

    http://www.maherassociates.com/mssql.cfm


  • Advertisement
  • #12
    Ginger
    Registered Users, Registered Users 2 Posts: 2,931 ✭✭✭Ginger


    You can check what actions are causing the row locks in the SQL Server Activity monitor and you can kill the process at that level too.

    If you are doing inserts updates, make sure you release the lock as soon as you can..

    You can also implement dirty reads as required, tho not recommended if you application doesnt need them or it will cause issues further donw the line


  • #13
    amen
    Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    you can use sp_who and sp_who2 to see what process are running and what they are doing


    you could use kill processid but that is a bit bold


Advertisement