Question OleDbException was unhandled. Could not use "checkin.mdb". File already in use...

[JhunBB]

OleDbException was unhandled. Could not use "checkin.mdb". File already in use...

Need help on how to avoid this error message.

The database i am using is an MS Access 2003 mdb file. I develop the system in VS 2010 with 6 users for this database. every time the second user open's the system it debugs directly and pop ups this message "OleDbException was unhandled. Could not user "checkin.mdb". file is in use by user 'Admin'.

I am new to vb.net and still trying my best to dry run the system i develop until i found this error with multiple users.


thank you in advance for your help regarding this problem.

Below is the code that debugs every time i load the form.

Private Function GetQueueNum()
    'Get last queuingNum
        Dim newNum As Integer = 0


        ' Execute the SQL Statement
        Dim qCon As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        qCon.ConnectionString = GetConnString()
[I][B]        qCon.Open() ------------------------------------------------------------->> OleDBException was unhandled[/B][/I]


        'grab the queueing number from counter table
        Dim sqlTop = ("SELECT * FROM Top1CheckIn;")
        Dim sqlCmd As New Data.OleDb.OleDbCommand(sqlTop, qCon)
        Dim dr As Data.OleDb.OleDbDataReader = sqlCmd.ExecuteReader()
        Try
            If dr.HasRows Then
                While dr.Read()
                    newNum = dr("QueueingNum").ToString
                    lblNextQNum.Text = CStr(Format(newNum, "000"))
                    txtCheckInID.Text = dr("CheckInID").ToString
                    lblNextQNum.Refresh()
                End While
                Return True
            Else
                lblNextQNum.Text = " "
                txtCheckInID.Text = 0
                lblNextQNum.Refresh()
                Return False
            End If


        Catch ex As Exception
            Return False
        End Try
   qCon.Close()

End Function

 

[JuggaloBrotha]

A couple of things, as the error tells you the database is in use by an exclusive user which is going to block all other connections to the database. That being said, you're probably using a connection string that's requesting exclusive access to the database but I can't say for sure without seeing the connection string you have. The connection string I typically use for Access 2003 databases is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\checkin.mdb;Persist Security Info=True;Jet OLEDB:Database Password=<YourPasswordHere>;

Also I would like to point out that you've got your database opening code outside of the Try/Catch which is why the exception you got was unhandled. You'll want to move that into the Try/Catch so your app can gracefully handle it and notify the user if needed.

 

[JhunBB]

A couple of things, as the error tells you the database is in use by an exclusive user which is going to block all other connections to the database. That being said, you're probably using a connection string that's requesting exclusive access to the database but I can't say for sure without seeing the connection string you have. The connection string I typically use for Access 2003 databases is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\checkin.mdb;Persist Security Info=True;Jet OLEDB:Database Password=<YourPasswordHere>;

Also I would like to point out that you've got your database opening code outside of the Try/Catch which is why the exception you got was unhandled. You'll want to move that into the Try/Catch so your app can gracefully handle it and notify the user if needed.

Thank you so much JuggaloBrotha for your reply. I will apply the code you suggested and post soon the result.

 

[JhunBB]

Now it works fine... thank you so much for your advise.

procedure i made:

1st: I change the connection string to "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\checkin.mdb;Persist Security Info=false;Jet OLEDBatabase Password=;"

2nd: I copy the mdb file to DataDirectory "bin\debug" folder

3rd: I place all the connection under Try/Catch function

This procedures you suggested really solved my problem i've been struggling for almost a week now.

Thank you very very much. I really appreciate your support.