Invalid Database Object Reference with subreports

Posted on 2011-05-05
Last Modified: 2012-05-11
I have created a report in MS Access 2010 (Windows Server 2003 Standard Edition).  The main report contains eight simple sub-reports which run from queries.  There is no code on opening any of the subreports.

The report displays correctly in print preview but when we go to print we get the message Invalid Database Object Reference.  On clicking OK  a further message appears saying that the record source does not exist.  This happens for each subreport.  Once we have clicked past all the messages the report prints without any of the subreport detail.  (All the queries run when checked and the subreports print fine when opened as an individual report.)

We then often get the message Object Invalid or no longer set and all objects vanish from the list of queries tables etc.  On closing and reopening the database they all reappear.  The connection to the network is still live.  The database is split and linked by a drive letter rather than a full server path.

I've tried splitting the report into two so that there are only 4 sub-reports on each one but we still get the same message.

I've also imported everything into a new database in case there was a corruption on the database but still have the same problem.

I've checked the references and there are no missing references listed. Those ticked are listed on the attached document.

I'd really appreciate any help or advice.

Thanks in advance

Question by:PerksP
    LVL 77

    Expert Comment

    Does your application compile without any errors?

    Are you actually using any ADO code in your A2010 application?

    I suspect we will need to see a file that produces the error.

    Author Comment

    Hi Peter

    The database compiles without any errors and there is very little code as it is a reporting suite so is only used to run reports and queries from command buttons.

    I've also compacted and repaired which also goes through fine.

    The data is sensitive so I'll need to put together anonymous data in order to publish it.  Do you have any ideas I could try before I do that?

    Many thanks for your prompt reply
    LVL 77

    Expert Comment

    Is there any other machine you can try the app on?  

    Assuming you need an ADO reference, then you could try selecting Ado2.8 instead of 2.1 although I would not really expect there to be any difference in terms of Access reports.

    Objects going out of scope would only produce a run-time error, so we would have to see the code to comment further.

    LVL 26

    Expert Comment

    I'll take a shot in the dark.

    Do you attempt to shrink any of the sub reports using code?
    2007/2010 have a nasty bug introduced with the advent of the split form, that deep-sixes things badly if you try to shrink a subreport.

    Shot in the dark

    Disable any code that messes with sizes.
    Make the sub reports 0.0007" high and at least 0.0007" apart with CanGrow = Yes.
    Run it.
    If it works, I hit something in the dark, if not, I have no idea

    Author Comment

    Thanks for taking a shot Nick.  I did have CanShrink set to Yes and I've reset that to No and all reports are set about 1cm apart with CanGrow to yes.  Still getting the same problem.

    I've just moved the database to a machine which is still running 2003 and, again, it displays OK but when I try to print I get the message Can't open any more databases.  I guess this eliminates the problem being anything to do with 2010 specifically.

    The queries behing the reports use the following to change a field that contains minutes to hours and minutes:

    HoursConverted: Int([Total Minutes]/60)
    MinutesConverted: Int(([Total Minutes]/60-(Int([Total Minutes]/60)))*60+0.5)
    Hours/Minutes: [HoursConverted] & " Hours " & [MinutesConverted] & " Minutes"
    WTEEquivalent: Round(Int([Total Minutes]/60)/37.5,2)

    Can you see anything here that could cause a problem?

    I've just tried removing all the subreports that run from queries using these expressions and have now got the message that The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.  

    There are some maketable queries that run to set up the data to be used in the queries.  Could these be causing a problem?


    Author Comment

    Sorry, ignore the bit about maketable queries.  Those are used to setup data for graphs and pie charts that are working fine.
    LVL 77

    Expert Comment

    I think both error messages are saying  the same thing - just manifesting the problem in different ways.

    When you run the report do you have other objects open?

    For the 3rd time of asking,.. what is the ADO reference for?  Are you creating ADO connections to the backend?

    Author Comment

    Nothing else is open.

    I'm afraid I don't know what you mean by an ADO connection to the backend.  The tables used are linked via a drive letter P: and using Linked Table Manager so nothing unusual.  The query picks up dates from a local table.

    I'm also not aware of any ADO references or how to check for them.  The references ticked are those automatically selected and no manual changes have been made.    If you can let me know what I should be looking for I'll search it.

    There is no code at all behind any of the subreports and the only code behind the command button is:

        Dim stDocName As String

        stDocName = "SMT_ManagementReport"
        DoCmd.OpenReport stDocName, acPreview
        DoCmd.RunCommand acCmdZoom75

    This is about as complicated as the code gets in this database as it's just a front end for reports.  No data management is done from here at all.

    I'm currently trying to rebuild the report to see if there is any one report that causes the problem but it's not consistent.  I sometimes get the message with more than two subreports and other times I can get to four subreports before it fails.

    I'm trying to find something consistent to give more clues.

    Thanks for sticking with me.
    LVL 77

    Accepted Solution

    I have come across situations where multiple subreports can cause the 'too many databases' but it has been in a context of for example, lots of table-based comboboxes being used for parameters.  

    If you haven't explicitly used an ADO connection then there will not be any - you'd know if you'd used one.

    Are the recordsources for the reports using multiple tables?  Do the tables use lookup fields?
    I can't say that answers to any of these questions will reveal a solution, but it's just more context.

    Author Comment

    The queries do use multiple tables and some of the tables do have lookup fields.

    I'm currently working on the assumption that you suggest i.e. that it's just too much to process so am cutting it down piece by piece.  Will continue over the weekend and report back

    Thanks again

    Author Comment

    I've now used Maketable to put all the data into new tables and run the reports from there instead of running them from the queries which did use multiple tables.

    This seems to have worked so I think it was just information overload.

    Many thanks for your help.


    Author Closing Comment

    These solutions pointed me in the right direction to redesign the way the reports collected the data.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now