Locked records in Access/Ms SQL Server

John_Mc

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?

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.

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

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?

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

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?

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 ?

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

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

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

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

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

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