[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

Invalid Database Object Reference with subreports

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

  • 7
  • 4
1 Solution
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.
PerksPAuthor Commented:
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
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.

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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
PerksPAuthor Commented:
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?

PerksPAuthor Commented:
Sorry, ignore the bit about maketable queries.  Those are used to setup data for graphs and pie charts that are working fine.
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?
PerksPAuthor Commented:
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.
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.
PerksPAuthor Commented:
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
PerksPAuthor Commented:
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.

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now