is there a way to increase the number of recordsets i can open?

[James]

is there a way to increase the number of recordsets i can open? im limited
to about 85 right now...

 

[Albert D.Kallal]

Well, I have no idea why you need to open so many recordsets? Something
seems wrong, very wrong here...

However, I have no problem opening 200 recordsets at the same time......

I just put the following two code routines in a database, and it runs no
problem.

The following assumes you created a table called table1

Sub maketables()

Dim strSql As String
Dim i As Integer

For i = 2 To 200
Debug.Print "creating table " & i
strSql = "SELECT Table1.* INTO table" & i & " FROM Table1;"
CurrentDb.Execute strSql

Next i


End Sub


Sub opentables()
Dim rstBuf(200) As DAO.Recordset
Dim i As Integer
Dim strTable As String

For i = 1 To 200
Debug.Print "open table " & i
Set rstBuf(i) = CurrentDb.OpenRecordset("table" & i)
Next i

MsgBox "done"
End Sub

So, the above opened 200 tables for me (about 1 second), and did not
complain.

 

[James]

im using access 97 if that matters? p.s. im opening so many because i am
using a recursive function to walk thru a hierarchy of employees. there's
probably better ways to implement this but i already have it working except
for one part and it complains here.

 

[Joerg Ackermann]

Albert D.Kallal:

Well, I have no idea why you need to open so many recordsets?
Something seems wrong, very wrong here...

However, I have no problem opening 200 recordsets at the same
time......

If the tables are linked in current db, the limit is 85.

There's no such limit when you open the recordsets
in that way:

Sub opentables()

Dim db As DAO.Database
Dim rstBuf(200) As DAO.Recordset
Dim i As Integer

Set db = DBEngine.OpenDatabase("f:\backend.mdb")
For i = 1 To 200
Debug.Print "open table " & i
Set rstBuf(i) = db.OpenRecordset("table" & i, dbOpenDynaset)
Next i

MsgBox "done"

End Sub

May be a workaround...

Acki

 

[Joerg Ackermann]

James:

im using access 97 if that matters? p.s. im opening so many because i
am using a recursive function to walk thru a hierarchy of employees.
there's probably better ways to implement this but i already have it
working except for one part and it complains here.

I don't think that's nessessary to have this
count of recordserts to stay 'open'.

Acki

 

[Joerg Ackermann]

Joerg Ackermann:

Albert D.Kallal:

I was playing a while...

The limit is 85 using
....
Set rstBuf(i) = currentdb.OpenRecordset(...
....

The limit is 127 using

set db = CurrentDb
....
Set rstBuf(i) = db.OpenRecordset(...
....

Acki

 

[James]

Thank You! do you know off hand how i can count how many recordsets my db
currently has open?

 

[Joerg Ackermann]

James:

Thank You! do you know off hand how i can count how many recordsets
my db currently has open?

set db = Currentdb
....
debug.print db.Recordsets.Count

Acki

 

[Joerg Ackermann]

James:

I summarize again...

Your problem is not a limit of used recordsets.
The limit is the number of 'used' database instances.
(255 max)

Additionally to last tips:
If you create recordsets recursivly, you should
use *one* database instance that is declared one time outside
your recursive function.

Close each recordset as soon as possible.

Acki

 

[Albert D.Kallal]

There's no such limit when you open the recordsets
in that way:

Sub opentables()

Dim db As DAO.Database
Dim rstBuf(200) As DAO.Recordset
Dim i As Integer
Set db = DBEngine.OpenDatabase("f:\backend.mdb")

Very very interesting......

As mentioned, I also got the 125 open when I "copy" currentdb.

However, your above idea is a "smart idea" workaround (good for you!).

Anyway, at least now we know/have a solution to this limit that I was not
aware of!!