Release Jet OleDb connection

have you tried to add "GC.Collect()" after your dispose?

Just tried that....didn't work.

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...

(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)

can you post your connectionstring (just XXXXXXXXX the password & username)

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"

Select allOpen in new window

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)

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.

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.

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 :-)

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.

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

-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.

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...

Also dispose the dataadapter and may be commandbuilder as well. The dataadapter is using the connection.

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

cdebel / Codecruiser....working on trying your suggestions this morning

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)

Select allOpen in new window

Add these statements at the bottom.
da.dispose
da2.dispose

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

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.

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.

Is there anyway to make sure the module is releasing all resources when I'm done using it?

CodeCruiser:
The da, da1, dispose and commenting the cmdbuilder did not work.

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?

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

Select allOpen in new window

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)

I cant see any problem in the code.