Report Table/view or SP in SQL to handle various reports

Question

Report Table/view or SP in SQL to handle various reports

JP789

SSC Veteran

Points: 222
More actions
July 8, 2025 at 5:20 pm

#4619513

I have an Access database with a SQL backend with tables, views, SP's.
I have reports to run based on rptArea and I am currently using MSAccess vba
but wondering if it's better to use a report table, view or SP
to hold the report table criteria. For instance, sample vba code:
if rptArea is "Mktg" then
Select Case ReportID
Case M1
rptName = "Mktg1_rpt"
strdisplayName = "MKtg1"
Docmd.OpenReport "Mktg1_rpt", acViewPreview
Reports(strReportName).Caption = strdisplayName
Case M2
.... same thing but different report
Case M3
.... same thing but different report
End Select
if rptArea is "Finance" then
Case F1
rptName = "Finance1_rpt"
strdisplayName = "Finance1"
Docmd.OpenReport "Finance1_rpt", acViewPreview
Reports(strReportName).Caption = strdisplayName
Case F2
.... same thing but different report
Case F3
.... same thing but different report
End Select
There are 12-15 rptArea .
I can do this in VBA, but was wondering if a table, view or SP would be better.
Then if they add a new report or delete a report, it can be done in the backend.
But I don't know how to setup the backend to populate and run the reports from
a table with the criteria from above.
Maybe TABLE or view to create a table list of reports:
the table would have fields:
rptArea, rptName , strdisplayName , Reports(strReportName).Caption, Docmd.OpenReport "Finance1_rpt", acViewPreview
Then the VBA would only be:
if rptArea is "Mktg" then
DLookup the table that has the criteria from above,
but it would need to lookup each report, and there can be 1 or many reports per rptArea.
How to loop through the table and run each report for that rptArea?
Not sure. Looking for a way to streamline the VBA used to run the reports by letting
SQL do the heavy lifting.
Any ideas, suggestions is appreciated?
Thanks,
JP

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24934 More actions · Answer 1

My opinion - Access is the wrong tool for the job. If you have a SQL Server standard (or higher) license, use SSRS for this instead. Report creator uploads the report to the report server and provides the necessary access restrictions and you are good to go. No overhead of Access needing to be installed anywhere or licensed, all hosted in a web interface.

Now, if VBA and Access is your only solution, I think it really depends. I think the easiest way would be to use a stored procedure and you'd pass in the report area and all criteria as to which report to show to the stored procedure and the stored procedure would hand back whatever data was needed.

BUT I do think that Access is not the right tool for the job.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

pietlinden SSC Guru Points: 63594 More actions · Answer 2

(You could absolutely cheat and create a hyperlink or a button in your Access front end that opens the SSRS portal, and then you wouldn't really need Access reports at all, would you? Are you doing anything in your reports that can't be done in SSRS?