Splitting up dbs eats ressources

[cbelius]

I am using Access 2003. In my mdb I have 2 identical tables with only 4
fields in each. A formular showing the data. One of the tables is linked
outsite from (back end) the other is internal in the mdb-file.
Ressources is checked with this script:

Dim i As Long, rst(1000) As DAO.Recordset
For i = 1 To 1000
Set rst(i) = CurrentDb.OpenRecordset( _
"Select * from Basis1", dbOpenDynaset)
rst(i).MoveFirst
Debug.Print "No of open objects: " & i
Next i

Running the script with the table "Basis" which is the linked table the
immediate window showed 80 Access objetc available before error 3048.
Running the script with the table "Basis1" which is NOT linked, the result
showed 252.
Splitting up a database eats ressources.
Question: How can I overcome this problem. I need splitting dbs in a
multiuser environment, but cannot open any more databases when 7 formulars
are open simultaneously.
Do I need a SQL solution??
tia.

 

[Albert D. Kallal]

Each user has their own work space, so, unless you running code that opens
80 tables in one shot, you have nothing to worry about.

Furthermore, if you create an instance of the db object, you get more
head room:

Dim rst(1000) As DAO.Recordset
Dim I As Integer
Dim db As DAO.Database

Set db = CurrentDb

For I = 1 To 1000
Set rst(I) = db.OpenRecordset("faxbook1", dbOpenSnapshot)
rst(I).MoveFirst
Debug.Print I
Next I

The above will 250 instances on a linked table.....

Even if you open with dbOpenDynaset you get 125.

However, as I said, this is PER USER. So,

I need splitting dbs in a
multiuser environment, but cannot open any more databases when 7 formulars
are open simultaneously.

What do you mean 7 formulars? You have to explain here?

I been in the computer business wring software from mainframes, assembler,
and have written two complete payroll systems from scratch and I never had
anything close to 100 files needed to be opened at the same time.

My current application has about 5 users, 160 forms, 60+ highly related
tables, and 77 reports. it also has over 30,000 lines of vba code and this
is considered a small application.

So, you might want to explain or clear up what the limit or wall it is your
are hitting in your code. I mean, after you process some data in a table
via an recordset, you can close that table, and those resources are now
again
available for use.

Why such a need to have so many tables open? You might want to give so more
information here, but I generally don't see a limit or problem here.

 

[cbelius]

Thanks Albert!
Surely it help showing the right resources left.
I looked at my codes in my formulars for clauses with "OpenRecordset" to see
if changes could release bindings to objects. One simple formular with no
table attached took up 18 instances (objects) and my main formular with
several bounded tables (queries) took up 56 instances. I was using the open
statement:
Dim rsCpr As New ADODB.Recordset
rsCpr.open "Select * FROM CPR ORDERBY Cpr.CprNr",CodeProject.Connection,
adOpenDynamic
I changed all these codes to your suggestion with DAO.Recordset and Set db =
CurrentDb
No changes in number of instances was noted.
If one formular takes up 56 instances, seven open formular will release the
error message 3048.
I can send you my code for the simple formular that take up 18 instances (4
pages) if you are willing to review my code for changes that can release
resources.' In the main formular that take up 56 instances I use 7
subformulars.
My project have 70 tables, app. 100 forumulars and 50 reports.
I hope you can guide me further on to a solution to my main problem: error
3048
Thanks to you and others who makes comment on this subject.
b.r. cbelius/

 

[Albert D. Kallal]

As I mentioned one thing that helps a lot here is to use ONE instance of the
database
connection object.

So, in place of currentdb you dim a GLOBAL db object in a standard code
module. You can use that everywhere:

public db as dao.Database

Then in your startup code ONCE set

set db = currentdb

So, now use db throughout your application.

As you saw, that allowed me have 250 instances of a reocrdset opened as per
my
first example even with a linked table.

One simple formular with no
table attached took up 18 instances (objects) and my main formular with
several bounded tables (queries) took up 56 instances. I was using the
open
statement:
Dim rsCpr As New ADODB.Recordset

In our last example we were using dao. Now your example is using ADO? I
really can't recommend you mix and match the type data objects. You just
increasing your workload and it means you have to try to manage two diffent
types of data object models. And, that's going to make any type of progress
in limiting resources used more difficult.

I would certainly avoid using two types of data objects in your application.
In fact, I would go so far as to remove the ADO object reference if you not
using it.

For dao, when done with a reocrdset, you go:

rst.Close
set rst = nothing

As mentioned, in the above example, I was able to get 250 linked reocrdsets
open, but if I close them when done...I don't really encounter a limitation
here.

Another issue to consider is why so many separate tables. This hints as
serious flaw in the table designs. For example in place of 12 tables:

Salesjan
SalesFeb
SalesMar

etc., we simply add one "month" column to a table called Sales, and we just
eliminated 12 tables by adding one column to a table.

So, look at normalizing the data...you reduce you table counts.

Stick to one data object model (preferably dao)

Use ONE instance of currentdb put into a variable

Close your reocrdsets when done.

The above few tips should really give you MORE then enough head room for
this type of work...

 

[David W. Fenton]

So, in place of currentdb you dim a GLOBAL db object in a standard
code module. You can use that everywhere:

public db as dao.Database

Then in your startup code ONCE set

set db = currentdb

So, now use db throughout your application.

I've posted it many times, and many people criticise it, but I use a
function for the cached database reference. "dbLocal" can replace
"Set db = CurrentDB" throughout your entire app. The code is after
my signature.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function dbLocal(Optional ysnInitialize As Boolean = True) _
As DAO.Database
' 2003/02/08 DWF added comments to explain it to myself!
' 2005/03/18 DWF changed to use Static variable instead
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent being
' closed (3420) would then be jumping back into the middle
' of an If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String

If Not ysnInitialize Then GoTo closeDB

retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know db variable is Not Nothing, test if it's Open
strTest = dbCurrent.Name

exitRoutine:
Set dbLocal = dbCurrent
Exit Function

closeDB:
If Not (dbCurrent Is Nothing) Then
Set dbCurrent = Nothing
End If
GoTo exitRoutine

errHandler:
Select Case err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If ysnInitialize Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox err.Number & ": " & err.Description, _
vbExclamation, "Error in dbLocal()"
Resume exitRoutine
End Select
End Function