Solved

Release Jet OleDb connection

Posted on 2009-05-08
29
802 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 8
  • 5
  • +1
29 Comments
 
LVL 10

Expert Comment

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

Author Comment

by:dreinmann
ID: 24339788
Just tried that....didn't work.
0
 
LVL 10

Expert Comment

by:cdebel
ID: 24339865
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 10

Expert Comment

by:cdebel
ID: 24339876
(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
ID: 24339884
can you post your connectionstring (just XXXXXXXXX the password & username)
0
 

Author Comment

by:dreinmann
ID: 24339960
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
ID: 24339967
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
ID: 24340182
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
ID: 24397540
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
ID: 24397849
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
ID: 24398423
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
ID: 24399189
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
ID: 24399382
-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
ID: 24400298
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
 
LVL 83

Expert Comment

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

Author Comment

by:dreinmann
ID: 24412069
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
ID: 24412089
cdebel / Codecruiser....working on trying your suggestions this morning
0
 

Author Comment

by:dreinmann
ID: 24412175
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
ID: 24412431
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
ID: 24412535
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
ID: 24412558
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
ID: 24412620
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
ID: 24412641
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
ID: 24412654
CodeCruiser:
The da, da1, dispose and commenting the cmdbuilder did not work.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24412670
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
ID: 24412752
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
ID: 24412955
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
ID: 24413399
I cant see any problem in the code.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

724 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