Cannot Open any more databases

[NatHunter]

I am converting a Vb.net program to use ADO.NET instead of ADO - part of the program iterates through a number of customer and customer order type database records and populates the same dataset with the appropriate records. It processes them and then clears out the orders (but leaves the customers in a dataset table).

The program works fine when accessing several customers and their orders, but I get the message

'cannot open any more databases'

When going through batch customer and order processing.

I am using Access 2000 tables.

I have found similar questions and it mentions that there are too many table ids, but I ensure connections to tables are closed after accessing records using cn.close.

Is there any way of seeing how many table ids I have and which tables these are referring to?

Could there be another reason why this error is being generated?

Thanks for any help!

Paul

 

[Mike555]

In addition to closing the connection, are you also setting it to Nothing?

cn.close
cn = Nothing

--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[NatHunter]

Thanks for the reply.

I am not setting the connection to nothing, as I want to reuse it for further calls to retrieve data.

I have a single function which gets gets data from the database (commands and connections are set up beforehand and passed as parameters):

Function PopDataTable(ByVal ods As DataSet, ByVal sTable As String, ByVal sSQL As String, Optional ByVal sdsSQL As String = "", Optional ByVal rowParent As DataRow() = Nothing, Optional ByVal sRelation As String = "", Optional ByVal bForceClearTable As Boolean = False, Optional ByVal oda As OleDbDataAdapter = Nothing, Optional ByRef ocn As OleDbConnection = Nothing, Optional ByVal ocomSel As OleDbCommand = Nothing) As DataRow()
Dim rowR As DataRow
Dim rowSelected() As DataRow
Dim iRecCount As Integer
Dim iSelCount As Integer
Dim shChildCount As Short

PopDataTable = Nothing

If IsNothing(ocn) Then
'use the generic connection
ocn = cnGen
End If

If IsNothing(ocomSel) Then
'use the generic command object
ocomSel = comSelGen
End If

iSelCount = 0
'See if the rows are there
If Not IsNothing(ods.Tables(sTable)) And Not bForceClearTable Then
'if there's no SQL Query, then get children from Parent datarows
If sdsSQL = "" Then
rowSelected = GetChildren(rowParent, sRelation, shChildCount)
Else
rowSelected = ods.Tables(sTable).Select(sdsSQL)
End If
iSelCount = rowSelected.Length
PopDataTable = rowSelected
ElseIf Not IsNothing(ods.Tables(sTable)) And bForceClearTable Then
'Clear the dataset if necessary or required
ods.Tables(sTable).Clear()
End If

'if there are no records execute query
If iSelCount = 0 Then
Try
ocn.Open()

'Set the SelectCommand string and connection...
ocomSel.CommandText = sSQL
ocomSel.Connection = ocn

'set the data adapter to use this information
oda.SelectCommand = ocomSel

'Fill the dataset table
iRecCount = oda.Fill(ods, sTable)

If iRecCount > 0 Then
PopDataTable = ods.Tables(sTable).Select(sdsSQL)
End If


Catch eFillException As System.Exception
MsgBox(eFillException.ToString)
'TODO: handle errors here
Finally
ocn.Close()
ocomSel.Connection.Close()
End Try
End If

Return PopDataTable
End Function