User-designed reports in an mde?

[kiln]

There is a request for user designed reports with an app (access 2000)
that is distributed as an mde, and full-bore Access group security
implemented. I wondered if anyone has tried allowing users in this sort
of scenario to create reports in a separate mdb file, with more
permissive perms, that has links (somehow) to either the data file or
the mde interface file for custom report generation. I've not thought
this through yet, but on the surface it'd seem to be a viable option, as
long as the data was exposed to the mdb "safely".

I've not found any really versatile ad-hoc report engines for use with
Access, but would be interested in hearing of any. Crystal or
ActiveReports might be an option?

 

[Douglas J. Steele]

What you're describing (letting users build their own reports in an MDB
file) is a fairly common approach.

 

[Marshall Barton]

There is a request for user designed reports with an app (access 2000)
that is distributed as an mde, and full-bore Access group security
implemented. I wondered if anyone has tried allowing users in this sort
of scenario to create reports in a separate mdb file, with more
permissive perms, that has links (somehow) to either the data file or
the mde interface file for custom report generation. I've not thought
this through yet, but on the surface it'd seem to be a viable option, as
long as the data was exposed to the mdb "safely".

I've not found any really versatile ad-hoc report engines for use with
Access, but would be interested in hearing of any. Crystal or
ActiveReports might be an option?

I can't help if there are any security issues, but creating
a playground mdb file is not very difficult.

First create a public provedure in a standard module in the
report mdb to open reports.

Public Sub OpenUserReport(rn As String, _
v As Long, _
fn As String, _
wc As String, _
wm As Long, _
oa As String)
DoCmd.OpenReport rn,v,fn,wc,wm,oa
End Sub

Next you need to create queries in the report mdb to
retrieve the data that the users may want to use in their
reports. (It is often useful to have the queries flatten
the data so the users don't have to mees with Joins, etc)
The trick with the queries is to get them to reference the
tables in the back end data mdb file. This can be done
using the usual linked tables or by using the IN phrase in
the FROM clause.

If you have to create a form for the users to select and
open their reports, create that in your front end and set a
reference to the reports mdb so you can open the report
using the above procedure. Then recreate the mde.

Set up a dummy playground template mdb with these minimum
capabilities and play with it to see if that's enough for
the users to create their needed reports.

 

[kiln]

OK, thanks, both are helpful responses.

I can't help if there are any security issues, but creating
a playground mdb file is not very difficult.

First create a public provedure in a standard module in the
report mdb to open reports.

Public Sub OpenUserReport(rn As String, _
v As Long, _
fn As String, _
wc As String, _
wm As Long, _
oa As String)
DoCmd.OpenReport rn,v,fn,wc,wm,oa
End Sub

Next you need to create queries in the report mdb to
retrieve the data that the users may want to use in their
reports. (It is often useful to have the queries flatten
the data so the users don't have to mees with Joins, etc)
The trick with the queries is to get them to reference the
tables in the back end data mdb file. This can be done
using the usual linked tables or by using the IN phrase in
the FROM clause.

If you have to create a form for the users to select and
open their reports, create that in your front end and set a
reference to the reports mdb so you can open the report
using the above procedure. Then recreate the mde.

Set up a dummy playground template mdb with these minimum
capabilities and play with it to see if that's enough for
the users to create their needed reports.