Cannot open anymore databases

[Guest]

I have been trying to follow what I thought was best practice splitting a
database into a back end and front end. The front end has some very complex
reports containing 15 - 20 sub-reports. Now with the database split I can
Print these say 5 or 6 times, but then although the print preview works, when
I click on the print icon I start to get this error message 'cannot open
anymore databases'. Merging the database back into one solves the issue BUT
this is not what I want - I would like to be able to add security to the
database and place the front end on user machines with the backend on the
server. I have found an article on the Microsoft web site that explains this
behaviour is due to twice as many tables being opened when split ! Does
anyone have bright suggestions on how to solve this one ? Also question for
Microsoft : could this be classed as some sort of bug (it works to begin with
and then eventually gives up!). I get this issue with Access 2000 and Access
2003.

 

[Allen Browne]

Steve, if you have been researching this, you probably already realize that
this is not a simple issue, and there is not a simple fix. it is not really
a bug but a limitation, i.e. the software is not doing anything that it was
not designed to do, but the memory space set aside for handling
table/database connections is less than what you need it to do (and previous
uses are not releasing resources quickly enough.)

To work around this limitation, you will need to simplify some things. It is
quite easy to see how 15-20 subreports, each sourced by multi-table queries,
will hit this limit. Could you reduce the number of tables in some queries?
Could you reduce the number of subreports? Can you simply close any other
bound forms that might be open at the same time?

If those simple concepts are not adequate, the next stage would be to get
serialize some of the data and close the connections instead of drawing it
all in parallel with the delay before Access times out those connections.
This would mean creating one or more temp tables, and populating them with
some of the data the report and subreports need. Execute an append query to
populate the temp table. Then close the connections (destroying your objects
in code such as recordsets and databases.) Then feed the report from the
temp table, so it and its subs do not need to connect to the back end for
most of their data.

 

[Allen Browne]

Steve, if you have been researching this, you probably already realize that
this is not a simple issue, and there is not a simple fix. it is not really
a bug but a limitation, i.e. the software is not doing anything that it was
not designed to do, but the memory space set aside for handling
table/database connections is less than what you need it to do (and previous
uses are not releasing resources quickly enough.)

To work around this limitation, you will need to simplify some things. It is
quite easy to see how 15-20 subreports, each sourced by multi-table queries,
will hit this limit. Could you reduce the number of tables in some queries?
Could you reduce the number of subreports? Can you simply close any other
bound forms that might be open at the same time?

If those simple concepts are not adequate, the next stage would be to get
serialize some of the data and close the connections instead of drawing it
all in parallel with the delay before Access times out those connections.
This would mean creating one or more temp tables, and populating them with
some of the data the report and subreports need. Execute an append query to
populate the temp table. Then close the connections (destroying your objects
in code such as recordsets and databases.) Then feed the report from the
temp table, so it and its subs do not need to connect to the back end for
most of their data.

 

[Guest]

Allen,

many thanks for that - a case of "great minds thinking alike" here because I
had already started down this road and some of the sub-reports already use a
temporary table. The danger here that I forsee is I could take this further,
but may at the end of the day end up with the very same issue after say 20
quotes have been printed ! I like your idea of doing more in code and seeing
if that helps closing resources etc. My only other thought which I realise
is far more drastic is to change to an SQL backend and an ADP although I have
seen another post on this forum saying ADP is soon to be defunct and .Net is
the way to go ! This is all very well but my client likes the relative ease
of using Access as it is. Anyway many thanks for your input and sharing your
thoughts on this.

 

[Guest]

Allen,

many thanks for that - a case of "great minds thinking alike" here because I
had already started down this road and some of the sub-reports already use a
temporary table. The danger here that I forsee is I could take this further,
but may at the end of the day end up with the very same issue after say 20
quotes have been printed ! I like your idea of doing more in code and seeing
if that helps closing resources etc. My only other thought which I realise
is far more drastic is to change to an SQL backend and an ADP although I have
seen another post on this forum saying ADP is soon to be defunct and .Net is
the way to go ! This is all very well but my client likes the relative ease
of using Access as it is. Anyway many thanks for your input and sharing your
thoughts on this.