Microsoft Access (Table locking error) ** Resolved **

[MarkusJ_NZ]

Hi, against my better judgement I have had to use Microsoft Access 2000 to store information for an app that I am writing.

When I go to do an update I receive the following error

"Could not save; currently locked by another user"

I close all connections/ destroy all objects to do with the database as soon as I finish with them so there * is no way * anyone is still connected to the database. Also, I am the only one using the database.

If I wait a few minutes the *.ldb file automatically disappears and then I can update the database without the error appearing.

Any help appreciated

Cheers
Mark

PS. Connection string below is what I am using to connect to the database

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\_Databases\myCrapAccessDatabase.mdb;Persist Security Info=False

================

Hi, I have just flagged Access as being the crap database that it is and have gone back to using SQL server. Things are all working fine now. Thanks for everyones help

Cheers
Markus

[kovan]

make sure youd ont hav ethe database open manuallay while running ur code

this error happens if you have the db open in ACCESS itself and u run the code

[hellswraith]

Or if you have it opened in Server Explorer window in VS.

[MarkusJ_NZ]

Hi Guys, thanks for the recommendations. I don't have the database open locally or via VS.net..

Cheers
Mark

[Carnifex]

Make sure you've closed the connection. This includes when an exception is thrown while the database is open.

[PPCC]

1. manually open the access database and close thedatabase file.
2. try reboot option. hope this will work surely


PPCC

[MarkusJ_NZ]

Hi everyone, thanks for your help. I have tried all the recommendations and I have worked out where in my code it is occurring;

Dim oCOnn As New OleDb.OleDbConnection("Connectionstring")
Dim oCommand As New OleDb.OleDbCommand("UPDATE Pages SET PageText = 'Blah' WHERE PageID = 1", oCOnn)
       oCOnn.Open()
        oCommand.ExecuteNonQuery()
        oCommand.Dispose()
        oCOnn.Close()
        oCOnn = Nothing

Basically, I can run this code once/ twice even three times. After this I get the forementioned error

It's like Access is holding open the database even though no-one is using it...

Thanks in advance and have a great New Years!
Cheers
MarkusJ

[Carnifex]

Shouldn't the 1 in where PageID is be inclosed in quote marks?

Also step thru the code, put the ExecuteNonQuery in a try/catch block and make sure it's not throwing any weird exceptions. Also make sure it is updating the correct number of records by getting the Integer that oCommand.ExecuteNonQuery returns.

[MarkusJ_NZ]

Hi, the 1 is a type integer so the SQL statement is ok. Likewise the statement executes no problem a few times and then falls over..

Thanks for your help
Cheers
MarkusJ