How to unlock database.

[Maya Pawar]

Hi friends,
I have different query in access and vb.net.
Sometimes record are inserted sometimes not.
All coding is correct.
when record is not inserted,it gives an exception that
'could not open;database is locked'
What is this exception???
My database is not having password.
I m using access2007 database.
since Last week, i m confused for this.
Plz anybody can help me???

Thanks in advance.
Plz help meeeeeeeeeeeeeeeeee.

[G_Waddell]

Hi,

It could be a number of things, someone else may be opening your Access file on the network (or locally) or it could be open connections from your site code.

In order to minimise the risk of it being your code, you should open the connection just before you use it and close down the connection immediately after e.g.

Sub UpdateCustomers(ByRef CustomerID As Integer, ByRef Status As Integer)
        Dim cmd As OleDb.OleDbCommand
        Dim conn As OleDb.OleDbConnection
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False"
        With cmd
            .CommandText = "UPDATE Customers Set Status=" & Status & " WHERE CustomerID=" & CustomerID
            .CommandType = CommandType.Text
            .Connection = conn
        End With
        If conn.State <> ConnectionState.Open Then 'open Conn just before execution
            conn.Open()
        End If
        cmd.ExecuteNonQuery()
        If conn.State <> ConnectionState.Closed Then 'close conn immediately after.
            conn.Close()
        End If
    End Sub

[Maya Pawar]

Thanks G_Waddell.
But i tell u that i already use this code in conClass(which contains code for opening or closing connection).
I m sending u code plz check it out and plz tell me what is problem.
Plz help me yaar........

Sub New()
str = New System.Text.StringBuilder
str.Append("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=")
str.Append(AppDomain.CurrentDomain.BaseDirectory)
str.Append("KBPDataBase.accdb")
'str.Append("123.mdb")
con = New OleDbConnection()
con.ConnectionString = str.ToString()
If con.State = ConnectionState.Open Or con.State = ConnectionState.Broken Then
con.Close()
End If
If con.State = ConnectionState.Closed Then
con.Open()
End If
End Sub

[G_Waddell]

Hi,

Is it possible that someone or something is opening your access file locally and therefore locking the DB table? (do you back the DB up for instance?)

Or is this site in Production and more than one person is accessing the same table at a time?

I have a feeling that Access may only allow a single user access to a table at any given time.

I know that theorically the Jet DB engine can allow up to 255 users but it isn't really recommended for Web apps :http://msdn.microsoft.com/en-us/library/ms811092 and
http://support.microsoft.com/kb/222135/en-us

You may want to consider switching to Microsoft SQL Server which is more robust and the Express editions are free.

[G_Waddell]

Something else that occured to me

I m using access2007 database.
since Last week,

What were you using before? And did this only occur since you switched databases?