Solved

Release Jet OleDb connection

Posted on 2009-05-08
29
781 Views
Last Modified: 2013-12-25
I'm having trouble with releasing a connection to a MSAccess database.
Error:  
System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.

Here's the Scenario:
My program's opening form uses an OleDb connection to an Access database, fills a dataset, then closes and disposes the connection.
Another form opens afterwards (Starting form remains open), and connects to the same database, fills a dataset, then closes and disposes the connection.  (works fine)
...here's where the problem starts.
After the second form finishes and closes, if I try to open it up again, it throws the error above.
If I close the entire program and reopen it, then it works.

Code below shows how I fill the dataset.
Dim cn As New OleDb.OleDbConnection(Switchboard.PrmRtgConnString)

            cn.Open()
 

            Dim da2 As New OleDb.OleDbDataAdapter("SELECT * FROM LocIDsNotUpdtd", cn)

            Dim ds2 As New DataSet()

            da2.FillSchema(ds2, SchemaType.Source, "LocIDsNotUpdtd")

            da2.Fill(ds2, "LocIDsNotUpdtd")

            Dim cmdBuilder2 As New OleDb.OleDbCommandBuilder(da2)
 

            cn.Close()

            cn.Dispose()

Open in new window

0
Comment
Question by:dreinmann
  • 14
  • 8
  • 5
  • +1
29 Comments
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
have you tried to add "GC.Collect()" after your dispose?
0
 

Author Comment

by:dreinmann
Comment Utility
Just tried that....didn't work.
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
i thought it could be because of some delay when releasing the object.

I know it won't solve your problem, but if its a delay problem, you might consider adding "Mode=Share" in your connection string.

I'll try to think about other possible cause of problems and i'll be back...
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
(oh and when i say to do "GC.Collect()", i really mean at each time that you do a dispose on your connection.  So it mean in your main app, in your forms, etc)
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
can you post your connectionstring (just XXXXXXXXX the password & username)
0
 

Author Comment

by:dreinmann
Comment Utility
Connection String below.

I figured that's what you meant on the 'GC.Collect()'.  I placed one after every cn.dispose().
Connection String:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Logistics Manager\PrmRtng.mdb;Jet OLEDB:Database Password=xxxxx"

Open in new window

0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
You might take a look at this site, it might give you some good ideas
http://www.connectionstrings.com/ (especially http://www.connectionstrings.com/access-2007)
0
 

Author Comment

by:dreinmann
Comment Utility
No luck.

Tried - MODE=share deny none....still get the same error.

It's Access 2003, so I have to use Jet 4.0 connection string.
0
 

Author Comment

by:dreinmann
Comment Utility
I have found a solution that works, but it's not a real fix.

Doing an Application.Restart releases the hold on the mdb, and it will work without the error.

However, I'm still open for a real fix without having to restart.
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
I've a book here from Microsoft Press about ADO.NET.  
Here's what they say about OleDbConnection and your problem...

- If you are using a "Connection Pool", then you shoudl use a System.Threading.Thread.Sleep(0) before opening your other form who is trying to open a new connection.

- you could also use ReleaseConnectionPool  of your OleDbConnection object

- you could add this to your connectionn string:
           OLE DB Services=-4
  about this last point, i translate what they say:
       "With this attribute, the .NET OLE DB provider specify that you don't want to use connection pool.  When you call the Close Method of the object OleDbConnection, you REALLY close the connection"

You might give it a try (make sure that you removed your Application.Restart) to see if it work.

If it doesn't work, you might click the REQUEST ATTENTION button on this E-E Question.  Maybe the moderator can raise the flag to a "Guru" because i think its out of my league :-)
0
 

Author Comment

by:dreinmann
Comment Utility
I put the 'OLE DB Services=-4' into all of the OleDb ConnectionStrings and removed the Application.Restart()'s.  

Still got the same original error the 2nd time through the program without restarting.

System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.
0
 
LVL 20

Expert Comment

by:ddayx10
Comment Utility
I don't think connection pooling is going to help you in this scenario. Hmmm.

BTW, what version of Acces are you using?

I'm thinking that something is going wrong in the previous transaction with Access that is stopping the connection from closing....error of some sort that is not being caught by your application. Maybe because you have strategically programmed around the potential of errors or some other reason. Something is holding that connection open, or in your second pass you are trying to access different resources that are unavailable to you due to settings in Access.

1)Try checking the connection state before opening the connection:
       If cn.State = Data.ConnectionState.Open Then cn.Close()
       cn.Open()

2)Try wrapping the connections in the "Using" context:
Using cn As New OleDb.OleDbConnection(Switchboard.PrmRtgConnString)
            cn.Open()
 
            Dim da2 As New OleDb.OleDbDataAdapter("SELECT * FROM LocIDsNotUpdtd", cn)
            Dim ds2 As New DataSet()
            da2.FillSchema(ds2, SchemaType.Source, "LocIDsNotUpdtd")
            da2.Fill(ds2, "LocIDsNotUpdtd")
            Dim cmdBuilder2 As New OleDb.OleDbCommandBuilder(da2)
End Using

3)You might also try disposing of the OleDbCommandBuilder after you've used it:
cmdBuilder2.Dispose()

4)If you haven't already I would walk through the db transactions using the debugger, and see what is occurring. Check the states of the connection, and data as the process runs line by line. Add this at the end of your Sub and check it before the sub exits:

Dim dataState As System.Data.ConnectionState = conn.State

Good luck with that,

dday
0
 

Author Comment

by:dreinmann
Comment Utility
-Access 2003 (11.8166.8221) SP3

-I'll double check for any holes, but all my code is 99.9% always wrapped in a Try...Catch.  The catch sends me an email of any error exceptions.

-Second pass is the same exact process as the first.

Going to give your other suggestions a try.   Thank you very much.
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
Dreinmann, have you tried the ReleaseConnectionPool?  I know that ddayx10 said that it's probably not your problem, but when we use "probably" it mean that it doesn't cost you much to try it...
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Also dispose the dataadapter and may be commandbuilder as well. The dataadapter is using the connection.
0
 

Author Comment

by:dreinmann
Comment Utility
Ok I tried the suggestions, and I can't believe none of them worked.

1)The connection state showed Closed{0} before the cn.Open()

2)Tried wrapping all the connections with "Using"...still same.

3)Tried disposing all the cmdBuilder(s)

4)Went through the debugger...nothing found
0
 

Author Comment

by:dreinmann
Comment Utility
cdebel / Codecruiser....working on trying your suggestions this morning
0
 

Author Comment

by:dreinmann
Comment Utility
When a form closes, it should release any connections...right?   Well, in this case, the form starts the connection and fills a dataset.
Then, it sends the dataset to a module routine via -"PrmRtgTransfer(ByVal ds As DataSet, ByVal ds2 As DataSet)".....that has to be were the connection gets stuck right?  And why the whole application has to be restarted to release it?
I close and dispose the connection before sending it to the module to be processed, but from what I've been reading, sometimes that doesn't matter...is that right?

Any new suggestions, now that I added this new information?
Dim cn As New OleDb.OleDbConnection(Switchboard.PrmRtgConnString)

            cn.Open()
 

            Me.tssStatus.Text = "Processing No-Edit Records"

            Application.DoEvents()

            Dim da2 As New OleDb.OleDbDataAdapter("SELECT * FROM LocIDsNotUpdtd", cn)

            Dim ds2 As New DataSet()

            da2.FillSchema(ds2, SchemaType.Source, "LocIDsNotUpdtd")

            da2.Fill(ds2, "LocIDsNotUpdtd")

            Dim cmdBuilder2 As New OleDb.OleDbCommandBuilder(da2)
 

            Me.tssStatus.Text = "Processing Location Updates"

            Application.DoEvents()

            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM PrmRtgFINAL", cn)

            Dim ds As New DataSet()

            da.FillSchema(ds, SchemaType.Source, "PrmRtgFINAL")

            da.Fill(ds, "PrmRtgFINAL")

            Dim cmdBuilder As New OleDb.OleDbCommandBuilder(da)

            cn.Close()

            cn.Dispose()
 

            PrmRtgTransfer(ds, ds2)

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Add these statements at the bottom.
da.dispose
da2.dispose

Why are you declaring the commandbuilders when you are not using them?
0
 

Author Comment

by:dreinmann
Comment Utility
Just a standard 5 lines I always copy and paste when using/filling/working with datasets.  Most of the time, I'm sending information back and forth.

I think I already tried the dataadatpter dispose(s), but I'll try again....and comment out the cmdbuilder lines.
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
Its always a good idea to dispose of your object anyway because someday (especially if you work with Office Automation), it will never close the instances of the office application, and will load up your memory.
0
 

Author Comment

by:dreinmann
Comment Utility
Is there anyway to make sure the module is releasing all resources when I'm done using it?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
Comment Utility
The trouble with using a module is that its global and never goes out of scope. Try using a class instead.
0
 

Author Comment

by:dreinmann
Comment Utility
CodeCruiser:
The da, da1, dispose and commenting the cmdbuilder did not work.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
There is some other problem. Test by stopping to pass anything to the module. Restarting the app fixes it means that the module keeps some connection open. Do you pass and DataReader object to the module?
0
 

Author Comment

by:dreinmann
Comment Utility
See code below.
---frm6530---

Dim cn As New OleDb.OleDbConnection(Switchboard.PrmRtgConnString)

            cn.Open()
 

            Me.tssStatus.Text = "Processing No-Edit Records"

            Application.DoEvents()

            Dim da2 As New OleDb.OleDbDataAdapter("SELECT * FROM LocIDsNotUpdtd", cn)

            Dim ds2 As New DataSet()

            da2.FillSchema(ds2, SchemaType.Source, "LocIDsNotUpdtd")

            da2.Fill(ds2, "LocIDsNotUpdtd")

            Dim cmdBuilder2 As New OleDb.OleDbCommandBuilder(da2)
 

            Me.tssStatus.Text = "Processing Location Updates"

            Application.DoEvents()

            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM PrmRtgFINAL", cn)

            Dim ds As New DataSet()

            da.FillSchema(ds, SchemaType.Source, "PrmRtgFINAL")

            da.Fill(ds, "PrmRtgFINAL")

            Dim cmdBuilder As New OleDb.OleDbCommandBuilder(da)

            cn.Close()

            cn.Dispose()

            

            PrmRtgTransfer(ds, ds2)
 

---mod6530---  (module)

Public Sub PrmRtgTransfer(ByVal ds As DataSet, ByVal ds2 As DataSet)

        Try

            'frm6530.tssStatus.Text = "Processing No-Edit Records"

            'Application.DoEvents()

            'Dim fileName3 As String = "C:\PrmRtng.ldb"

            'If IO.File.Exists(fileName3) Then

            '    IO.File.Delete(fileName3)

            'End If

            'Application.DoEvents()

            'Dim cn As New OleDb.OleDbConnection(Switchboard.PrmRtgConnString)

            'cn.Open()
 

            'Dim da2 As New OleDb.OleDbDataAdapter("SELECT * FROM LocIDsNotUpdtd", cn)

            'Dim ds2 As New DataSet()

            'da2.FillSchema(ds2, SchemaType.Source, "LocIDsNotUpdtd")

            'da2.Fill(ds2, "LocIDsNotUpdtd")

            'Dim cmdBuilder2 As New OleDb.OleDbCommandBuilder(da2)
 

            'frm6530.tssStatus.Text = "Processing Location Updates"

            'Application.DoEvents()

            'Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM PrmRtgFINAL", cn)

            'Dim ds As New DataSet()

            'da.FillSchema(ds, SchemaType.Source, "PrmRtgFINAL")

            'da.Fill(ds, "PrmRtgFINAL")

            'Dim cmdBuilder As New OleDb.OleDbCommandBuilder(da)

            'cn.Close()

            'cn.Dispose()

            'GC.Collect()

            frm6530.TimeStop()

            'Create New LocMaint.csv with first line:

            '--LocId,DayNo,LoadNo,StopNo,Result,Recorded--

            Dim sw1 As StreamWriter

            '''''REQUIRED FILE'''''

            sw1 = New StreamWriter("G:\\LocMaint.csv", False)

            sw1.AutoFlush = False

            sw1.WriteLine("LocId,DayNo,LoadNo,StopNo,Result,Recorded")

            sw1.Flush()

            Dim str As String = String.Empty

            Dim timeStr As String = Now.ToString("yyMMddhhmmss")

            Dim fileName As String = "G:\LocMaint.csv"

            For Each dr2 As DataRow In ds2.Tables("LocIDsNotUpdtd").Rows

                str = dr2(0).ToString & "," & dr2(1).ToString & "," & dr2(2).ToString & "," & _

                dr2(3).ToString & "," & dr2(4).ToString & "," & dr2(5).ToString

                sw1.WriteLine(str)

            Next

            sw1.Flush()

            frm6530.ResumeTime()
 

            frm6530.scrnCnt = (ds.Tables("PrmRtgFINAL").Rows.Count * 3) + 4

            frm6530.scrnCntDn = frm6530.scrnCnt

            frm6530.records = CInt((frm6530.scrnCnt - 4) / 3)

            frm6530.tssRecRmg.Text = frm6530.records.ToString("000000")

            frm6530.tssTtlScrns.Text = frm6530.scrnCnt.ToString("000000")

            frm6530.tssScrnsRem.Text = frm6530.scrnCntDn.ToString("000000")

            Dim objPos As COhioPosition

            Dim testCntr As Integer = 0

            frm6530.scrnCntDn -= 4

            frm6530.tssScrnsRem.Text = frm6530.scrnCntDn.ToString("000000")

            Switchboard.loopThrottle = 50

            For Each dr As DataRow In ds.Tables("PrmRtgFINAL").Rows

                'Read/Writes information to emulator...no connstring communication

            Next

            'Log off and close frm6530

            If WaitForQual(1, 15, "WCDM") Then

                'F3 Previous Screen

                frm6530.Act6530x1.OhioManager.Sessions.Item(1).Screen.SendAid(61)

                Application.DoEvents()

                If WaitForQual(1, 2, "RKFM") Then

                    'SF12 Logoff

                    frm6530.Act6530x1.OhioManager.Sessions.Item(1).Screen.SendAid(105)

                    Application.DoEvents()

                    If WaitForQual(1, 2, "ESPRO") Then

                        Application.DoEvents()

                    End If

                End If

            End If

            frm6530.Close()

            GC.Collect()

            'Append to Log File when completed

            Dim fileName2 As String = "G:\Log.txt"

            Dim str2 As String = String.Empty

            Dim timeStr2 As String = Now.ToString("yyMMddhhmmss")

            If Not IO.File.Exists(fileName2) Then

                str2 = "Screen,Result,Recorded" & vbNewLine

            End If

            str2 = "UpdateComplete" & ",Success," & timeStr2 & vbNewLine

            My.Computer.FileSystem.WriteAllText(fileName2, str2, True)

            frmMsgBox.Show()

            frmMsgBox.Owner = frmRouting

            frmMsgBox.TopMost = True

            frmMsgBox.Width = 400

            frmMsgBox.Height = 175

            frmMsgBox.lblMsg.Text = "Roadnet Standard Routing successfully updated to TANDEM."

            frmMsgBox.Text = "Complete"

        Catch ex As Exception

            exMailObj.SendMail("email@test.com", Switchboard.RNRgn & " - " & CType(CreateObject("WScript.Network"), IWshRuntimeLibrary.IWshNetwork_Class).ComputerName & "/" & CType(CreateObject("WScript.Network"), IWshRuntimeLibrary.IWshNetwork_Class).UserName, ex.ToString())

        End Try

Open in new window

0
 

Author Comment

by:dreinmann
Comment Utility
Have some other pressing issues...will have to resume this challenge tomorrow.  If you think of any other ideas, go ahead and post them.

I'm thinking the suggestion you had on converting the module to a class would be the thing to do.
(Haven't done a class setup in awhile, I'll have to work on that for a bit)
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
I cant see any problem in the code.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now