Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Cannot open anymore databases

Discussion in 'Information Technology' started by stevebsc, Jul 28, 2006.

  1. stevebsc

    stevebsc
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    Cannot Find A Thread ? Try "Search by Tag." Announcements Jan 21, 2013
    I cannot Keep My Hair No Longer Sikh Youth Aug 8, 2012
    India Govt cannot change purpose of acquired land: SC Breaking News Oct 6, 2011
    USA TSA: Body Scanners Cannot See Through Turbans Breaking News Jan 14, 2011
    India India cannot help but be complex Breaking News Oct 2, 2010

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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 - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "stevebsc" <stevebsc@discussions.microsoft.com> wrote in message
    news:78E25467-900F-43FD-97F6-3C9BC50B3C92@microsoft.com...
    >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.
     
  4. stevebsc

    stevebsc
    Expand Collapse
    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.

    "Allen Browne" wrote:

    > 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 - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "stevebsc" <stevebsc@discussions.microsoft.com> wrote in message
    > news:78E25467-900F-43FD-97F6-3C9BC50B3C92@microsoft.com...
    > >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.

    >
    >
    >
     

Share This Page