J
JB
I have been having hitting the wall with error 3048 "Cannot open any more
databases" and so decided to do some testing to try to resolve this issue as
it relates to listboxes. There has been some discusion about this in the
newsgroups but no hard numbers. I am running Access 2003, sp1, Windows
XP-Pro. Also I did the test using .mdb files but expect the same behavior
with .mde files although I haven't tried it yet.
I will be describing a scenerio with 3 .mdbs, frontend.mdb, scratch.mdb and
data.mdb. Data.mdb resides on a server and each workstation gets the other
two. I am using security so users have no direct access to data.mdb and all
queries are stored RWOP queries. For the test, I used a simple Select query
to populate either the list box directly or a scatch table in either the
scratch.mdb or in Frontend.mdb.
In the first case, the list box is populated with the code
Dim qry As QueryDef
Dim prmClientID As Parameter
Set qry = CurrentDb.QueryDefs("qryData_GetClientList")
Set prmClientID = qry.Parameters!prmClientID
prmClientID = 1
Dim rst As DAO.Recordset
Set rst = qry.OpenRecordset()
With DataList
.RowSourceType = "Table/Query"
Set .Recordset = rst
End With
Set prmID = Nothing
Set rst = Nothing
Set qry = Nothing
This increass the database count by 1.
(DBEngine.Workspaces(0).Databases.Count)
This can be uninitialized by running
With DataList
.RowSourceType = "Table/Query"
Set .Recordset = Nothing
End With
which decreases the database count by 1. I tried also using
.RowSourceType = "value list"
.RowSource = ""
which does empty the list box but does NOT decrease the database count.
Next I tried using a RWOP query to populate a scratch table in the
scratch.mdb and used a RWOP query similar to the above to populate the list
box. The result is the same, as one would expect. Finally I tried the same
thing with a scratch table in the front end .mdb with again the SAME result.
From this, it appears that opening a rst and passing it to the list box
increase the database count by 1 WITHOUT regard to where the table is
located.
The next thing I tried was to link the listbox directly to the table
With DataList
.RowSourceType = "Table/Query"
.RowSource = "tbl_TempClientList"
End With
trying as the source either the table in Scratch or in the front end. In
neither case did the database count increase. In this case, the list box can
be uninitialized with
.RowSourceType = "value list"
.RowSource = ""
These indicates that whether or not the database is split is irrelevant and
that database counts can be avoid by using scratch tables.
When using security, the data .mdb must be secured from all users and access
only allowed via RWOP queries. In order for the rowsource = table to work,
however, the users must have read access to the scratch tables. This is not
a problem because the temp tables are populated with data via a RWOP query
and so never contain anything that the user isn't going to see in the list
box anyway. I use 3 .mdbs because over time the .mdb will grow in size and I
don't want users to be able to run repair and compact, particularly not the
code .mdb. Depending an installed versions and SPs, this can lead to
corrupted files. I remove the menu item and have the user copy a fresh .mdb
from the server when needed.
Next, I ran a loop in which populated a global array of record sets using a
single query until it hit the wall
On Error GoTo HaveError
Dim i As Long
For i = 0 To 199
Dim qry As QueryDef
Dim prmClientID As Parameter
Set qry = CurrentDb.QueryDefs("qryData_GetClientList")
Set prmClientID = qry.Parameters!prmClientID
prmClientID = 1
Set arst(i) = qry.OpenRecordset()
Set prmClientID = Nothing
Set qry = Nothing
DoEvents
DatabaseCount.Value = DBEngine.Workspaces(0).Databases.Count
Next
Exit Sub
HaveError:
DBMsgBox CStr(Err.Number) _
& vbCrLf & vbCrLf _
& Err.Description
I ran this a couple of times and the magic number seems to be 85. The fact
that I am looping over the same query may have some effect but this gives an
idea of the limit. While the loop ran, it ran slower as the database count
increased. It does seem like a very small value considering that Access
allows thousands of table to be open at one time.
databases" and so decided to do some testing to try to resolve this issue as
it relates to listboxes. There has been some discusion about this in the
newsgroups but no hard numbers. I am running Access 2003, sp1, Windows
XP-Pro. Also I did the test using .mdb files but expect the same behavior
with .mde files although I haven't tried it yet.
I will be describing a scenerio with 3 .mdbs, frontend.mdb, scratch.mdb and
data.mdb. Data.mdb resides on a server and each workstation gets the other
two. I am using security so users have no direct access to data.mdb and all
queries are stored RWOP queries. For the test, I used a simple Select query
to populate either the list box directly or a scatch table in either the
scratch.mdb or in Frontend.mdb.
In the first case, the list box is populated with the code
Dim qry As QueryDef
Dim prmClientID As Parameter
Set qry = CurrentDb.QueryDefs("qryData_GetClientList")
Set prmClientID = qry.Parameters!prmClientID
prmClientID = 1
Dim rst As DAO.Recordset
Set rst = qry.OpenRecordset()
With DataList
.RowSourceType = "Table/Query"
Set .Recordset = rst
End With
Set prmID = Nothing
Set rst = Nothing
Set qry = Nothing
This increass the database count by 1.
(DBEngine.Workspaces(0).Databases.Count)
This can be uninitialized by running
With DataList
.RowSourceType = "Table/Query"
Set .Recordset = Nothing
End With
which decreases the database count by 1. I tried also using
.RowSourceType = "value list"
.RowSource = ""
which does empty the list box but does NOT decrease the database count.
Next I tried using a RWOP query to populate a scratch table in the
scratch.mdb and used a RWOP query similar to the above to populate the list
box. The result is the same, as one would expect. Finally I tried the same
thing with a scratch table in the front end .mdb with again the SAME result.
From this, it appears that opening a rst and passing it to the list box
increase the database count by 1 WITHOUT regard to where the table is
located.
The next thing I tried was to link the listbox directly to the table
With DataList
.RowSourceType = "Table/Query"
.RowSource = "tbl_TempClientList"
End With
trying as the source either the table in Scratch or in the front end. In
neither case did the database count increase. In this case, the list box can
be uninitialized with
.RowSourceType = "value list"
.RowSource = ""
These indicates that whether or not the database is split is irrelevant and
that database counts can be avoid by using scratch tables.
When using security, the data .mdb must be secured from all users and access
only allowed via RWOP queries. In order for the rowsource = table to work,
however, the users must have read access to the scratch tables. This is not
a problem because the temp tables are populated with data via a RWOP query
and so never contain anything that the user isn't going to see in the list
box anyway. I use 3 .mdbs because over time the .mdb will grow in size and I
don't want users to be able to run repair and compact, particularly not the
code .mdb. Depending an installed versions and SPs, this can lead to
corrupted files. I remove the menu item and have the user copy a fresh .mdb
from the server when needed.
Next, I ran a loop in which populated a global array of record sets using a
single query until it hit the wall
On Error GoTo HaveError
Dim i As Long
For i = 0 To 199
Dim qry As QueryDef
Dim prmClientID As Parameter
Set qry = CurrentDb.QueryDefs("qryData_GetClientList")
Set prmClientID = qry.Parameters!prmClientID
prmClientID = 1
Set arst(i) = qry.OpenRecordset()
Set prmClientID = Nothing
Set qry = Nothing
DoEvents
DatabaseCount.Value = DBEngine.Workspaces(0).Databases.Count
Next
Exit Sub
HaveError:
DBMsgBox CStr(Err.Number) _
& vbCrLf & vbCrLf _
& Err.Description
I ran this a couple of times and the magic number seems to be 85. The fact
that I am looping over the same query may have some effect but this gives an
idea of the limit. While the loop ran, it ran slower as the database count
increased. It does seem like a very small value considering that Access
allows thousands of table to be open at one time.