Trying to salvage a report (1 Viewer)

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 19:54
Joined
Feb 28, 2001
Messages
17,717
G. - The error log entry that Demax182 posted means it WAS Access that crashed, or more specifically it is ACE that crashed out from under Access, which then means Access is getting dragged down by ACE. It is a memory management violation. There is no doubt this time because the exception code of 0xc0000005 is absolutely unequivocal. That is why I suggested it was a bad install, but the re-install with subsequent failure seems to rule that out, too.

There is one other thing, though. Demax182, when you make the new DB and copy over everything, how many controls do you have on that report?
More specifically, how many controls are in each section and in total of the report? (I'm talking "Design Mode" where the sections are not yet active. Are we talking 5, 50, 500, ... or more?

Do you have any "conditional compile" situations in the report's VBA code?

Do you have any group-by considerations in the report and if so, do any of them involve any kind of functions?

EDIT: I suddenly had a brainstorm. (Or maybe a brain burp.... equal odds with me.) Why is ACE getting involved at all? Has to be that it is trying to validate the recordset underlying the report. What can you tell us about the recordset on which the report is based?
 

arnelgp

error reading drive A:
Local time
Today, 08:54
Joined
May 7, 2009
Messages
10,253
you may try to upload a copy your report with the associated table and query.
you can delete all records from the table.
have it in zip file.
i will test my Access Recovery program if it can fix your report.
 

demax182

New member
Local time
Yesterday, 19:54
Joined
Jul 31, 2020
Messages
18
G. - The error log entry that Demax182 posted means it WAS Access that crashed, or more specifically it is ACE that crashed out from under Access, which then means Access is getting dragged down by ACE. It is a memory management violation. There is no doubt this time because the exception code of 0xc0000005 is absolutely unequivocal. That is why I suggested it was a bad install, but the re-install with subsequent failure seems to rule that out, too.

There is one other thing, though. Demax182, when you make the new DB and copy over everything, how many controls do you have on that report?
More specifically, how many controls are in each section and in total of the report? (I'm talking "Design Mode" where the sections are not yet active. Are we talking 5, 50, 500, ... or more?

Do you have any "conditional compile" situations in the report's VBA code?

Do you have any group-by considerations in the report and if so, do any of them involve any kind of functions?

EDIT: I suddenly had a brainstorm. (Or maybe a brain burp.... equal odds with me.) Why is ACE getting involved at all? Has to be that it is trying to validate the recordset underlying the report. What can you tell us about the recordset on which the report is based?
So does this mean the crashing is not due to report corruption? If so, I'm suspecting this is all happening because of bad report design... So the record source is a query of the following design:

RecordSource.JPG
quni_combined_assessments is a Union Query.

I would say I have less than 50 controls in each section of the report (the version that consistently crashes), has 10 subreports, 3 of which have their own subreports. I created a couple of "Group On" (based on expressions "=1", "=2", "=3", etc...) sections in the report in an effort to organize the subreports in separate sections. At the risk of my report getting blasted, here's a screenshot of it in the midst of being rebuilt (about 40% complete).
ReportCapture.JPG

I'm not exactly what conditional compile means, but the visibility of some sections is based on values, which is done through VBA. Now there is a function that some of the fields are based on called "QueryFieldAsSeparatedString". The code for that function was copied/pasted into "Module1". Reason I mention this is that particular function does tend to slow down report generation.
 

arnelgp

error reading drive A:
Local time
Today, 08:54
Joined
May 7, 2009
Messages
10,253
if it uses Database and Recordset object, it will be slow.
plz post the code of QueryFieldAsSeparatedString .
 

demax182

New member
Local time
Yesterday, 19:54
Joined
Jul 31, 2020
Messages
18
if it uses Database and Recordset object, it will be slow.
plz post the code of QueryFieldAsSeparatedString .
Here is the code:

EDITED BY THE_DOC_MAN: ADDED CODE TAGS (ONLY), NO OTHER CHANGES.

Code:
Public Function QueryFieldAsSeparatedString(ByVal fieldName As String, _
                                            ByVal tableOrQueryName As String, _
                                            Optional ByVal criteria As String = "", _
                                            Optional ByVal criteria2 As String = "", _
                                            Optional ByVal sortBy As String = "", _
                                            Optional ByVal delimiter As String = ", " _
                                        ) As String

' Paramter description
'   fieldName           =   Is the name of the field containing the values
'                           we want in our comma separated string
'   tableOrQueryName    =   Is the name of table or query containing the column
'   criteria            =   The criteria to filter the data
'   criteria2           =   Additional criteria join with "AND" if needed (Modification by Oey)
'   sortBy              =   An optional sort expression to sort the data
'   delimiter           =   The delimiter used to separate the values. It defaults
'                           to a comma and a blank, but you can use anything you
'                           like there


    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim sql             As String
    Dim whereCondition  As String
    Dim sortExpression  As String
    Dim retVal          As String
   
    Set db = CurrentDb
   
    ' If there where any criteria passed to the function, we build a WHERE-condition for SQL
    If Len(criteria) > 0 Then
        whereCondition = " WHERE " & criteria
    End If
   
    ' If there's anymore criteria, we add this to the WHERE-condition for SQL (modification by Michael Oey)
    If Len(criteria2) > 0 Then
        whereCondition = " WHERE (" & criteria & ") AND (" & criteria2 & ")"
    End If
   
    ' If there was a sort expression passed to the function, we build a ORDER BY for SQL
    If Len(sortBy) > 0 Then
        sortExpression = " ORDER BY " & sortBy
    End If
       
    ' building the complete SQL string
    sql = "SELECT " & fieldName & " FROM " & tableOrQueryName & whereCondition & sortExpression

    ' opening a recordset
    Set rs = db.OpenRecordset(sql, dbOpenForwardOnly, dbReadOnly)
    Do Until rs.EOF
        ' here we are looping through the records and, if the value is not NULL,
        ' concatenate the field value of each record with the delimiter
        If Not IsNull(rs.Fields(0).Value) Then
            retVal = retVal & Nz(rs.Fields(0).Value, "") & delimiter
        End If
        rs.MoveNext
    Loop
   
    ' we cut away the last delimiter
    retVal = Left(retVal, Len(retVal) - Len(delimiter))
   
    ' setting the return value of the function
    QueryFieldAsSeparatedString = retVal
   
    ' cleaning up our objects
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   
End Function
 
Last edited by a moderator:

arnelgp

error reading drive A:
Local time
Today, 08:54
Joined
May 7, 2009
Messages
10,253
the function Always has At least a Criteria.
if there are Many matching records, the return String
from the function can be quite large.
if there are only few records, you can use a Collection object
to save the retVal.
else, use Temp table to store the result of retVal and lookup
this table on your report.

is there any PK field in the table/query, then pass to
the function the PK fieldname.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 19:54
Joined
Feb 28, 2001
Messages
17,717
I'm interested in knowing whether you can open that recordset directly as a SELECT query. I'm still probing how ACE gets involved here during design mode and keep on coming back to it having to somehow look at the underlying recordset for validation purposes.

I'm trying to avoid biasing myself here because it would be very rare to have Access die on you like this unless you have some really bizarre table or query issues. So far as I recall (admittedly it has been a while since I read up on this), the Access design interface doesn't necessarily use ACE very much but it DOES have to validate recordsets and recordsources and rowsources and controlsources. That's got me really scratching my head - which is dangerous for me 'cause of the hair I've already scratched off from my Navy projects.
 

arnelgp

error reading drive A:
Local time
Today, 08:54
Joined
May 7, 2009
Messages
10,253
you may try technique in this report.
it generate all the related string once, when
the report loads.
then just assign the value to an unbound textbox.
 

Attachments

demax182

New member
Local time
Yesterday, 19:54
Joined
Jul 31, 2020
Messages
18
you may try technique in this report.
it generate all the related string once, when
the report loads.
then just assign the value to an unbound textbox.
Thank you. I'll give it a try. I have a query that also uses "QueryFieldAsSeparatedString". Could I implement this in a query as well?
 

demax182

New member
Local time
Yesterday, 19:54
Joined
Jul 31, 2020
Messages
18
I'm interested in knowing whether you can open that recordset directly as a SELECT query. I'm still probing how ACE gets involved here during design mode and keep on coming back to it having to somehow look at the underlying recordset for validation purposes.

I'm trying to avoid biasing myself here because it would be very rare to have Access die on you like this unless you have some really bizarre table or query issues. So far as I recall (admittedly it has been a while since I read up on this), the Access design interface doesn't necessarily use ACE very much but it DOES have to validate recordsets and recordsources and rowsources and controlsources. That's got me really scratching my head - which is dangerous for me 'cause of the hair I've already scratched off from my Navy projects.
So I took the SQL from the query I showed you and used it to create a separate select query. I was able to open the query without crashing Access...
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 19:54
Joined
Feb 28, 2001
Messages
17,717
WOW. This is getting more and more bizarre with every minute. The query works but the report based on it does not. In the fine art of troubleshooting, we are at least on page 4 by now.

You can't open the saved report for design, but maybe you can do a bit of "divide and conquer."

Put a breakpoint in the ...SeparatedString function. Then try to open the report. See if it just crashes again or maybe it breakpoints inside the function without breaking.
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:54
Joined
Mar 14, 2017
Messages
1,972
@demax182
I don't pretend to have anything particularly brilliant to add to this thread, but I was curious if you'd tried MajP's suggestion of saving the report to text and then loading it from text .
I know you've gotten a barrage of information and troubleshooting questions, but his idea would take only a minute or two, I think, to try, and may solve your problem.
 

Micron

AWF VIP
Local time
Yesterday, 20:54
Joined
Oct 20, 2018
Messages
3,467
Has no one suggested posting a zipped copy of the db? (sorry if I seem too lazy to search over 30 posts to find out, but the microwave is beeping at me).
 

demax182

New member
Local time
Yesterday, 19:54
Joined
Jul 31, 2020
Messages
18
@demax182
I don't pretend to have anything particularly brilliant to add to this thread, but I was curious if you'd tried MajP's suggestion of saving the report to text and then loading it from text .
I know you've gotten a barrage of information and troubleshooting questions, but his idea would take only a minute or two, I think, to try, and may solve your problem.
I did, but the report still caused Access to crash.
 

demax182

New member
Local time
Yesterday, 19:54
Joined
Jul 31, 2020
Messages
18
WOW. This is getting more and more bizarre with every minute. The query works but the report based on it does not. In the fine art of troubleshooting, we are at least on page 4 by now.

You can't open the saved report for design, but maybe you can do a bit of "divide and conquer."

Put a breakpoint in the ...SeparatedString function. Then try to open the report. See if it just crashes again or maybe it breakpoints inside the function without breaking.
That was a really good suggestion that I should have tried earlier. In any case, the report crashes before it reaches any of the breakpoints...
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:54
Joined
Mar 14, 2017
Messages
1,972
I did, but the report still caused Access to crash.
Huh. :(
Frustrating. Well, thanks for reporting back. I've always felt that Reports are the worst performing object in Access. (Some people feel the opposite). To me in theory they are powerful and convenient, but in practice it always seems like the report is "struggling" to just barely render before it collapses on the ground from mechanical contortions.
The first thing I do when presented with a reporting need coming out of an Access FE is to ask the user whether or not their report requirement might be satisfied in Excel (even with additional automation required after the export)...if I get lucky, it still ends up being a fraction the frustration of Access reports.
I wonder if you can remove elements from the report one by one and isolate anything. Tedious as heck, I know...
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 19:54
Joined
Feb 28, 2001
Messages
17,717
Yes, Isaac, good suggestion, which would have been mine as well. This has to be done incrementally to find which element does it. But just to grasp at straws (and we are talking desperate grasps),...

Can you at some point go to your explorer page, right-click on the drive where this app is kept, and tell it to run a CHKDSK? You will have to reboot the machine and wait for it to run, so maybe this is someone you can do overnight and just get the results in the morning. OR do some paperwork.

NOTE: You didn't say whether this was a traditional HDD or an SSD. If you are on a solid-state disk, this suggestion would be pointless, but on a traditional rotating platter style of disk, CHKDSK might find a cluster of bad blocks. Also, if you have not done one lately, do a disk optimization and don't let it tell you that it doesn't need one. Sorry to throw time-consuming issues at you but we are eliminating options and what is left is heading towards the bottom of the barrel.
 

Micron

AWF VIP
Local time
Yesterday, 20:54
Joined
Oct 20, 2018
Messages
3,467
How do you remove elements of a report that you can't open in design view?
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:54
Joined
Mar 14, 2017
Messages
1,972
How do you remove elements of a report that you can't open in design view?
Oops. You got me there. I missed or forgot that part of the picture in this saga
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 19:54
Joined
Feb 28, 2001
Messages
17,717
So does this mean the crashing is not due to report corruption?
My apologies, I didn't answer this question when you asked it.

This kind of error - causing something to crash in design mode even after moving it to a new copy of the DB file - is unlikely to be due to corruption of the app file. But at the moment I can't see what else could cause it other than disk failures. But with disk failures, you should have gotten a different error than the one that showed the 0xC0000005 as the exception code. Normally, you can't GET to the innards of the system that would lead to a Memory Management Violation from design mode.

I'll give you this, demax182 - if you are going to have a bug, make it a really GOOD bug.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom