Simple answer: You don't create reports in an MDE. Not the developer, not
the user, not anybody.
That's a good thing, bacause you should never, ever let users create objects
in any live application object or have direct access to data stores.
I saw a post, perhaps in another chain, that offered the correct answer:
With the MDE, distribute an MDB and let users create reports in that MDB.
You must use an MDB if you want to create a form, a report, or any kind of
module.
There are ways to look into the MDB from the main app and run reports from
there. You'll have an issue of deciding whether you can replace the mdb at
will (almost certainly not) and what to do if you must replace it (e.g.,
import existing reports). If properly planned you can probably avoid having
to have much to do with the external MDB.
The moment you go this route, you have a problem of controling how users get
to data.
Worst idea: Just link to all the underlying tables you need and let users
work from that. Very hard to control anything about data access in this
arrangement.
Slightly better: Don't link to anything from the MDB. Create queries that
present the kind of data from which users can easily make reports, and fully
qualify the tables in the FROM clause with the path/mdbname/tablename. You
can't hide them, and unless you're using Access security you can't keep
users from opening them up to look at the SQL.
Much better: You may not be up for this level of additional effort, but the
best way to allow users to create reports generally, regardless of the tool
really, is to create a data warehouse. Basically you would use queries such
as described above (user-oriented data presentation) without having to
expose them to the users.
You get the users to work with you to define what sort of data presentations
they need to see in order to make reports. You create those queries. You
export the data they retrieve on some periodic basis (daily is a good start)
to a data warehouse. That external MDB is the data warehouse.
You make this contract with your users: I (developer) control the tables
that exist over there. The will be replaced regularly with fresh data. You
(users) can create queries, reports, forms, anything you like. Remember
that the data you're looking at is a data warehouse. Do not perform data
entry there. Use the app for data entry. Use the warehouse for ad hoc
reporting. What you'll find is that with time you will discover reports
that you can and should incorporate into the application.
They get to create reports and anything else they like. You retain control
of data access. You leven get some very knowledgeable users creating
reports that will improve the utility of the application. Win, win, win.
