Cannot open any more tables error

Please HELP!! I am so frustrated using VB.NET accessing an MS Access database.  I have an application where I am trying to insert records into a table using an OledbDataReader.  When my application starts a class is created and a connection is opened to my database.  The application also uses datatable adapters.  I create and open the connection when the application starts so I can reuse it throughout the application.  The connection is closed when the application stops.  When I run the function I get the error as shown in the attached file.  It occurs randomly.  However if I debug the program and put a break point on the insert function it works fine.  If anyone can help me with this I would appreciate it.  If I could offer more than 500 points I would!  When the error occurs, the line number indicates it is at the oComm.ExecuteReader() statements (either one).
Private Sub ChargeFees_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChargeFees.Click
        Dim i As Integer
        Dim fc As New FeesClass
        Dim tdate As Date = Now()
        Dim smonth, sdays, syear, sdate As String
        Dim imonth, iyear, empProcessed As Integer
 
        Processing.Visible = True
        ChargeFees.Visible = False
 
        ' Setup Progress Bar
        ProgressBar1.Minimum = 1
        ProgressBar1.Maximum = Me.EmployeesDataGridView.RowCount
        ProgressBar1.Step = 1
        ProgressBar1.Visible = True
 
        ' Parse the month selected
        tdate = Today()
        smonth = Me.FiscalYearListBox.Text.Substring(0, Me.FiscalYearListBox.Text.Length - 5)
        imonth = MonthInteger(smonth)
        syear = Me.FiscalYearListBox.Text.Substring(Me.FiscalYearListBox.Text.Length - 4)
        iyear = CInt(syear)
        sdays = tdate.DaysInMonth(iyear, imonth)
        sdate = smonth & "/" & sdays & "/" & syear
 
        empProcessed = 0
        For i = 0 To Me.EmployeesDataGridView.RowCount - 1
            ' Add Admin Fee
            If fc.InsertAdminFee(Me.EmployeesDataGridView.Item(0, i).Value, sdate) = True Then
                empProcessed += 1
            End If
            ProgressBar1.PerformStep()
        Next
        Completed.Visible = True
        MessageBox.Show("Employees Processed: " & empProcessed)
        ChargeFees.Visible = True
    End Sub
 
FeesClass.vb
=================================
    Function InsertAdminFee(ByVal EmpID As Long, ByVal fdate As String) As Boolean
        Dim oComm As OleDbCommand
        Dim oQuery As String
        Dim MonthStr As String
        Dim cnt As Integer = 0
 
        Try
            MonthStr = DetermineMonthFee(fdate)
 
            Try
                ' Format query
                oQuery = "SELECT * FROM Fees WHERE EmployeeID=? AND FeeMonth=?"
                oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID
                oComm.Parameters.Add("@MonthStr", OleDbType.Char).Value = MonthStr
 
                Try
                    ' Perform the Query
                    drFeeTable = oComm.ExecuteReader()
                    Do While drFeeTable.Read
                        If cnt = 0 Then
                            ' Update the New Employee Fee
                            oQuery = "UPDATE Fees SET AdminFee=? WHERE ID=?"
                            oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                            oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75
                            oComm.Parameters.Add("@ID", OleDbType.Integer).Value = drFeeTable("ID")
                            If oComm.ExecuteNonQuery() < 0 Then
                                InsertAdminFee = False
                            Else
                                InsertAdminFee = True
                            End If
                        End If
                        cnt += 1
                    Loop
                    If (Not oComm Is Nothing) Then
                        oComm.Dispose()
                        oComm = Nothing
                    End If
 
                    If cnt = 0 Then
                        ' Fee record does not exist - create it
                        oQuery = "INSERT INTO Fees (FeeMonth, EmployeeID, AdminFee) VALUES (?,?,?)"
                        oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                        oComm.Parameters.Add("@FeeMonth", OleDbType.Char).Value = MonthStr
                        oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID
                        oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75
                        If oComm.ExecuteNonQuery() < 0 Then
                            InsertAdminFee = False
                        Else
                            InsertAdminFee = True
                        End If
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
                    InsertAdminFee = False
 
                End Try
 
                Try
                    If (Not oComm Is Nothing) Then
                        oComm.Dispose()
                        oComm = Nothing
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
 
                End Try
            Catch ex As Exception
                MsgBox(ex.Message & vbCrLf & ex.StackTrace)
                InsertAdminFee = False
 
            End Try
        Catch ex As Exception
            MsgBox(ex.Message & vbCrLf & ex.StackTrace)
            InsertAdminFee = False
        End Try
 
    End Function

Open in new window

toomanytablesopen.png
dyaroshAsked:
Who is Participating?
 
philipjonathanConnect With a Mentor Commented:
I am suspecting this problem is because the database connection timed out.
Try to avoid keeping the connection open for too long, and instead open connection only when database operation needs to be performed, and close it as soon as the operation is done.

Don't worry about the performance at this point in time, the connection is pooled, means only the 1st connection takes longer to initialise, while subsequent ones are faster.

You can create the connection at the beginning of the application (assuming it's not multi-threaded). Then in function InsertAdminFee, just before you create command object, open the connection. Then add Finally clause to the corresponding try block, and close the connection.

Example:
Try
    My.Forms.Startup.pfms.oConn.Open()
    oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
    ...

Catch ...

Finally
    My.Forms.Startup.pfms.oConn.Close()

End Try
0
 
JackOfPHConnect With a Mentor Commented:
>>I create and open the connection when the application starts so I can reuse it throughout the application.  The connection is closed when the application stops.

Do not do the above:

The best practice is to open the connection only when you need it and close it when you no longer need it...


2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
 
	
 
Private Sub ChargeFees_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChargeFees.Click
        Dim i As Integer
        Dim fc As New FeesClass
        Dim tdate As Date = Now()
        Dim smonth, sdays, syear, sdate As String
        Dim imonth, iyear, empProcessed As Integer
 
        Processing.Visible = True
        ChargeFees.Visible = False
 
        ' Setup Progress Bar
        ProgressBar1.Minimum = 1
        ProgressBar1.Maximum = Me.EmployeesDataGridView.RowCount
        ProgressBar1.Step = 1
        ProgressBar1.Visible = True
 
        ' Parse the month selected
        tdate = Today()
        smonth = Me.FiscalYearListBox.Text.Substring(0, Me.FiscalYearListBox.Text.Length - 5)
        imonth = MonthInteger(smonth)
        syear = Me.FiscalYearListBox.Text.Substring(Me.FiscalYearListBox.Text.Length - 4)
        iyear = CInt(syear)
        sdays = tdate.DaysInMonth(iyear, imonth)
        sdate = smonth & "/" & sdays & "/" & syear
 
        empProcessed = 0
        For i = 0 To Me.EmployeesDataGridView.RowCount - 1
            ' Add Admin Fee
            If fc.InsertAdminFee(Me.EmployeesDataGridView.Item(0, i).Value, sdate) = True Then
                empProcessed += 1
            End If
            ProgressBar1.PerformStep()
        Next
        Completed.Visible = True
        MessageBox.Show("Employees Processed: " & empProcessed)
        ChargeFees.Visible = True
    End Sub
 
FeesClass.vb
=================================
    Function InsertAdminFee(ByVal EmpID As Long, ByVal fdate As String) As Boolean
        Dim oComm As OleDbCommand
        Dim oQuery As String
        Dim MonthStr As String
        Dim cnt As Integer = 0
 
        Try
            MonthStr = DetermineMonthFee(fdate)
 
            Try
                ' Format query
                oQuery = "SELECT * FROM Fees WHERE EmployeeID=? AND FeeMonth=?"
                If MyForms.Startup.pfms.oConn.state = ConnectionState.Open then
MyForms.Startup.pfms.oConn.close
end if
 
MyForms.Startup.pfms.oConn.open
 
                oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID
                oComm.Parameters.Add("@MonthStr", OleDbType.Char).Value = MonthStr
 
                Try
                    ' Perform the Query
                    drFeeTable = oComm.ExecuteReader()
                    Do While drFeeTable.Read
                        If cnt = 0 Then
                            ' Update the New Employee Fee
                            oQuery = "UPDATE Fees SET AdminFee=? WHERE ID=?"
                            oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                            oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75
                            oComm.Parameters.Add("@ID", OleDbType.Integer).Value = drFeeTable("ID")
                            If oComm.ExecuteNonQuery() < 0 Then
                                InsertAdminFee = False
                            Else
                                InsertAdminFee = True
                            End If
                        End If
                        cnt += 1
                    Loop
                    If (Not oComm Is Nothing) Then
                        oComm.Dispose()
                        oComm = Nothing
                    End If
 
                    If cnt = 0 Then
                        ' Fee record does not exist - create it
                        oQuery = "INSERT INTO Fees (FeeMonth, EmployeeID, AdminFee) VALUES (?,?,?)"
                        oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                        oComm.Parameters.Add("@FeeMonth", OleDbType.Char).Value = MonthStr
                        oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID
                        oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75
                        If oComm.ExecuteNonQuery() < 0 Then
                            InsertAdminFee = False
                        Else
                            InsertAdminFee = True
                        End If
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
                    InsertAdminFee = False
 
                End Try
 
                Try
                    If (Not oComm Is Nothing) Then
                        oComm.Dispose()
                        oComm = Nothing
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
 
                End Try
            Catch ex As Exception
                MsgBox(ex.Message & vbCrLf & ex.StackTrace)
                InsertAdminFee = False
 
            End Try
        Catch ex As Exception
            MsgBox(ex.Message & vbCrLf & ex.StackTrace)
            InsertAdminFee = False
        End Try
 
    End Function

Open in new window

0
 
JackOfPHConnect With a Mentor Commented:
try this code:
Private Sub ChargeFees_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChargeFees.Click
        Dim i As Integer
        Dim fc As New FeesClass
        Dim tdate As Date = Now()
        Dim smonth, sdays, syear, sdate As String
        Dim imonth, iyear, empProcessed As Integer
 
        Processing.Visible = True
        ChargeFees.Visible = False
 
        ' Setup Progress Bar
        ProgressBar1.Minimum = 1
        ProgressBar1.Maximum = Me.EmployeesDataGridView.RowCount
        ProgressBar1.Step = 1
        ProgressBar1.Visible = True
 
        ' Parse the month selected
        tdate = Today()
        smonth = Me.FiscalYearListBox.Text.Substring(0, Me.FiscalYearListBox.Text.Length - 5)
        imonth = MonthInteger(smonth)
        syear = Me.FiscalYearListBox.Text.Substring(Me.FiscalYearListBox.Text.Length - 4)
        iyear = CInt(syear)
        sdays = tdate.DaysInMonth(iyear, imonth)
        sdate = smonth & "/" & sdays & "/" & syear
 
        empProcessed = 0
        For i = 0 To Me.EmployeesDataGridView.RowCount - 1
            ' Add Admin Fee
            If fc.InsertAdminFee(Me.EmployeesDataGridView.Item(0, i).Value, sdate) = True Then
                empProcessed += 1
            End If
            ProgressBar1.PerformStep()
        Next
        Completed.Visible = True
        MessageBox.Show("Employees Processed: " & empProcessed)
        ChargeFees.Visible = True
    End Sub
 
FeesClass.vb
=================================
    Function InsertAdminFee(ByVal EmpID As Long, ByVal fdate As String) As Boolean
        Dim oComm As OleDbCommand
        Dim oQuery As String
        Dim MonthStr As String
        Dim cnt As Integer = 0
 
        Try
            MonthStr = DetermineMonthFee(fdate)
 
            Try
                ' Format query
                oQuery = "SELECT * FROM Fees WHERE EmployeeID=? AND FeeMonth=?"
                If MyForms.Startup.pfms.oConn.state = ConnectionState.Open then
MyForms.Startup.pfms.oConn.close
end if
 
MyForms.Startup.pfms.oConn.open
 
                oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID
                oComm.Parameters.Add("@MonthStr", OleDbType.Char).Value = MonthStr
 
                Try
                    ' Perform the Query
                    drFeeTable = oComm.ExecuteReader()
                    Do While drFeeTable.Read
                        If cnt = 0 Then
                            ' Update the New Employee Fee
                            oQuery = "UPDATE Fees SET AdminFee=? WHERE ID=?"
                            oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                            oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75
                            oComm.Parameters.Add("@ID", OleDbType.Integer).Value = drFeeTable("ID")
                            If oComm.ExecuteNonQuery() < 0 Then
                                InsertAdminFee = False
                            Else
                                InsertAdminFee = True
                            End If
                        End If
                        cnt += 1
                    Loop
                    If (Not oComm Is Nothing) Then
                        oComm.Dispose()
                        oComm = Nothing
                    End If
 
                    If cnt = 0 Then
                        ' Fee record does not exist - create it
                        oQuery = "INSERT INTO Fees (FeeMonth, EmployeeID, AdminFee) VALUES (?,?,?)"
                        oComm = New OleDbCommand(oQuery, My.Forms.Startup.pfms.oConn)
                        oComm.Parameters.Add("@FeeMonth", OleDbType.Char).Value = MonthStr
                        oComm.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = EmpID
                        oComm.Parameters.Add("@AdminFee", OleDbType.Double).Value = -1.75
                        If oComm.ExecuteNonQuery() < 0 Then
                            InsertAdminFee = False
                        Else
                            InsertAdminFee = True
                        End If
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
                    InsertAdminFee = False
 
                End Try
 
                Try
                    If (Not oComm Is Nothing) Then
                        oComm.Dispose()
                        oComm = Nothing
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message & vbCrLf & ex.StackTrace)
 
                End Try
            Catch ex As Exception
                MsgBox(ex.Message & vbCrLf & ex.StackTrace)
                InsertAdminFee = False
 
            End Try
        Catch ex As Exception
            MsgBox(ex.Message & vbCrLf & ex.StackTrace)
            InsertAdminFee = False
        End Try
 
    End Function

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi dyarosh;
   
From MSDN Library
Cannot open any more tables. (Error 3014)
 You have reached the limit on the number of tables that can be opened at one time. Close one or more tables, and then try the operation again.

There seems to be a limit on the number of open tables.

Fernando
0
 
JackOfPHCommented:
As Fernando Sotto, mentioned in his above code, There is a limit on the number of open tables. Actually in one connection.

So, the best practice is to open the connection only when you need it and close it after you use it in your code.
0
 
dyaroshAuthor Commented:
The consensus seems to be that my connection is timing out and I should open and close the connection each time I need it.  I have some functions that write to multiple tables which exist in different classes.  Should I open a connection in each class or open the connection in the function that calls the multiple database queries?
0
 
dyaroshAuthor Commented:
philipjonathan gave the most complete answer to my query be explaining why the error was occuring which is why I awarded him the most points.  Everyone else gave the same advice about only opening the connection when needed.  I made the change and it works fine now.  Thanks for your help.  It is much appreciated.
0
 
philipjonathanCommented:
Thanks for the accept.
As for your last question, I don't have hard fast rules.
But as a guideline, if you know the multiple write operations are going to be in rapid succession, then it's better to just open the connection once and get the classes to use that connection object. The advantage is if you want to use DB transaction, you can initiate the transaction once, and commit it after all the writes are done. You can't do so with multiple connections in each class.
0
 
dyaroshAuthor Commented:
Thanks for the info.
0
All Courses

From novice to tech pro — start learning today.