Programmatically test 1,700+ reports

[PRMiller2]

I have been tasked with upgrading an Access application from 2003 to 2010. The application in question has over 1,700 reports. Having converted a development copy from 2003 to 2010, we have stumbled upon a few reports that don't work. We need to find a way to determine if all reports in the database work in the 2003 version before making the determination that a faulty report is due solely to the conversion to 2010.

I am writing a module that will programmatically open each report and write a line to a table indicating whether or not the report loaded successfully. My problem is that many reports rely on either a) queries that have parameters populated from open forms or b) rely on function calls to populate data.

My thought is to loop through each report, checking each report first for function calls, then populating the required variable. Second, I'd obtain the name of the query feeding the report, then figure out how to check the SQL statement for a "forms" or function reference. From there, I'd need to figure out how to temporarily modify the record source, or "spoof" the required parameter.

Is this the right approach to take? It seems a bit clunky, and I'm honestly not certain if it will work.

About all I've got so far is the following, which is simply writing a line to the immediate window (append query will be added later). Clearly I haven't started down the road of writing the above-mentioned two step process. I'd like to get some feedback from the group before I embark on this little expedition!

Function TestAllReports2()
On Error GoTo Err_Handler


    Dim rpts As AllReports
    Dim x As Integer
    
    Set rpts = Application.CurrentProject.AllReports
    gboolPrintAll = True

    For x = 0 To rpts.Count - 1
        DoCmd.OpenReport rpts(x).Name, acViewPreview
    Next
    
Exit_Function:
    Set rpts = Nothing
    Exit Function
    
Err_Handler:
    Debug.Print rpts(x).Name & ": " & Err.Number & " - " & Err.Description
    Resume Next
    
End Function

 

[MichaelRed]

you NEED to close the report(s) as you process them. Manually closing even close to that number is tiresome.

the approach could work, but seems complicated. why not just let the error handler at least identify the ones needing the investigation? also, the parameters need to be at least reasonable / realistic for the process to work well (more or less completly).

Finding the totallity of the source datas for a report (or most any object) can become an exercise in frustration. Some reports are opened only from some module / procedure code which sets the 'record source' - which then replaces the listed record source and may actually rely on other sources form control values, other traditional record sources ...



MichaelRed

 

[PRMiller2]

Thanks Michael. We actually decided to abandon this approach and instead open the reports manually as situations warrant while exploring the program. This is probably the best way to test it from a user's perspective.