Working out how many tables are open?

[Mat]

Currently I am getting the error, "Cannot open any more tables".

I was wondering if there was some code that I can run that counts how
many tables are open in my database. I think it is counting form,
listbox, combo box and also any I have opened in vba code.

Is there a way to reference how many tables I have open?

[Mat]

I have found this link to be of some use as advice.

http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/66fe3fce513a2064/

and

http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/9cd6d218708b9789/

[Mat]

Anyone have some actual code to do an open table count please?

[lyle fairfield]

On Nov 11, 10:07 pm, Mat <matthew....@optusnet.com.au> wrote:
> Anyone have some actual code to do an open table count please?

I thought maybe I'd write some as I was bored today but then I
realized I'd have to define what I meant by "open table" and I
stopped. There must be scores of situations where one might think of a
table as being "open". I suggest you go back to [Currently I am
getting the error, "Cannot open any more tables".] and tell us what
else is happening currently. The only time I have seen this happen is
when someone has something in a loop which someone should not have in
a loop, or when someone has coded an infinite loop, usually the
latter. But I'm told this error could actually happen in a large
involved db. I don't believe that, but I've been told it.
So what is happening when you get this error?

[Mat]

It's definitely to do with the interface and too much going on. IE:
Too many combo boxes, list boxes and other vba code opening tables.

I think a query that uses two tables counts as two open tables towards
the maximum number you can have open. That could easily jump by count
of tables open.

I found this link as well.
http://www.tech-archive.net/Archive/Access/microsoft.public.access.setupconfig/2005-03/0002.html

TableIDs could be chewed up 'like candy' by complex queries using
multiple tables.

Other links of merit:
http://www.access-programmers.co.uk/forums/showthread.php?p=610927

What I guess I wonder is that if access knows somewhere how many table
IDs have been used, surely that is a count that I could get access
too? if there is a limit to the number of tableIDs access must
maintain a count. No one seems to know how to get to that count (from
my searching to date).

[Mat]

A related question that is now concerning me. I decided a while ago to
use queries for everything. So every form has as its recordsource a
query.

Would this mean that some how a query is consuming more tableid's than
if i just had the table as the recordsource?

[lyle fairfield]

Maybe. Look at the code below. It allowed me to open 254 recordsets
based on a table, and only 156 based on a query (Northwinds 2007). In
saying that I would caution a bit of air code on a Saturday morning
doesn't establish anything more than a Maybe. In addition the first
argument of OpenRecordset, RecordSetTypeEnum, seems to influence the
number or TableIDs used with ForwardOnly consuming the fewest.

If I had this problem, depending on the version of Access, I would
consider using ADO to populate all my combo and list boxes. eg.

Private Sub Form_Open(Cancel As Integer)
Dim TSQL As String
TSQL = "SELECT fldGradeID, fldGradeName FROM dbo.tblGrades ORDER
BY fldSequence"
cboGrade.RowSource = CurrentProject.Connection.Execute
(TSQL).GetString(, , ",", ",")
End Sub

and if that didn't solve things I would go to ADO recordsets for my
forms eg (does some other things too)

Private Sub Form_Open(Cancel%)
Dim c As ADODB.Connection
Dim m As ADODB.Command
Dim r As ADODB.recordset

Set c = New ADODB.Connection
With c
' this is NOT a recommended connection procedure
' connect in YOUR way
.ConnectionString = CurrentProject.BaseConnectionString
.ConnectionString = .ConnectionString & ";User ID=" & InputBox
("Enter User Id.", "Login")
.ConnectionString = .ConnectionString & ";Password=" & InputBox
("Enter password.", "Login")
.CursorLocation = adUseClient
.Open
End With

Set m = New ADODB.Command
With m
.ActiveConnection = c
.CommandType = adCmdStoredProc
.CommandText = "spGet4060148Transactions"
Set r = .Execute()
End With

With r
.Find "TransactionID = 56"
If .EOF Then .MoveFirst
End With

' Set Me.recordset = r

End Sub

I have no idea, of course,how Access deals with ADO recordset counts,
but I !!!THINK!!! the 2048 lmit is a JET/ACE thing?

Sub hack1()
Dim z&
Dim r(1 To 2048) As Recordset
On Error Resume Next
For z = 1 To 2048
Err = 0
Set r(z) = DBEngine(0)(0).OpenRecordset("Customers")
If Err <> 0 Then Exit For
Next z
Erase r
Debug.Print z - 1 ' 254
End Sub

Sub hack2()
Dim z&
Dim r(1 To 2048) As Recordset
On Error Resume Next
For z = 1 To 2048
Err = 0
Set r(z) = DBEngine(0)(0).OpenRecordset("SELECT * FROM
Customers WHERE False")
If Err <> 0 Then Exit For
Next z
Erase r
Debug.Print z - 1 '156
End Sub

[lyle fairfield]

I was surprised to find that I could open 2045 table based recordsets
in Access 2002 (Jet 4.0) Windows XP as compared to 254 in Access 2007
(ACE) Windows Vista, each in the corresponding Northwinds version.
Well, OK, I use Access so infrequently now that I may have screwed up.

[Mat]

2045 is a jet or access limit.

I created a blank database with one table and one query and ran this
code:

Dim db As DAO.Database
Dim rec(1 To 3000) As DAO.Recordset
Dim i As Long

i = 0
Set db = CurrentDb
For i = 1 To 2999
Set rec(i) = db.OpenRecordset("table1")
'Set rec(i) = db.OpenRecordset("query1")
Next i

The result for i with table1 was 2043
The result for i with query1 was 409!!!

I then replicated the database:

The result for i with table1 was 1012
The result for i with query1 was 290!!!

That is a whopping difference. Honestly one probably shouldn't have
290 queries open at once anyway.

[hbinc]

Hi Mat,

Interesting result.
In then non-replicated database there is a factor 5 difference between
the number for table1 and the number for query1.
Can you explain that with the number of tables in the query?

HBInc.

[Mat]

I don't know why. One of the links I offered early does mention that
replication uses a lot of table IDs.

[hbinc]

On Nov 18, 3:13 am, Mat <matthew....@optusnet.com.au> wrote:
> I don't know why. One of the links I offered early does mention that
> replication uses a lot of table IDs.

Hi Mat,

That is not exactly what I meant.
You wrote you could open 2043 tables or 409 queries.
How many tables per query did you open? The underlying question is, do
only tables use a TableID, or use queries as such an additional
TableID?

HBInc.

[Mat]

It was a one table query.

[Mat]

So I added a 2nd table and make a query that used both.

The result for i with query2 was 256

[hbinc]

On Nov 18, 9:44 pm, Mat <matthew....@optusnet.com.au> wrote:
> So I added a 2nd table and make a query that used both.
>
> The result for i with query2 was 256

Hi Mat,

I assume that your query is a query defined as Querydef. That would
mean a Querydef is very TableID consuming!
Unlike your decision to use Querydef everywhere, I am probably in a
lucky position. Long time ago I decided to construct the value of the
Recordsource of each form in the OnOpen event of the form. Since that
time my Querydef collection is empty.

HBInc.

[Mat]

> I assume that your query is a query defined as Querydef. That would
> mean a Querydef is very TableID consuming!
> Unlike your decision to use Querydef everywhere, I am probably in a
> lucky position. Long time ago I decided to construct the value of the
> Recordsource of each form in the OnOpen event of the form. Since that
> time my Querydef collection is empty.
>
> HBInc.

I suspect your table id's get used up when the code runs. I can have
as many queries as I want and they only use table IDs when the query
is used.

Yeah I guess it just shows, again, the arbitrary limits they put on
access. Whether that is in place to stop people overusing or misusing
access in environments it is not robust enough for or to just keep
access in its niche is debatable.