How to load Report from ADP Project from another ACCDB or MDB

[Eric D]

From the article at
http://technet2.microsoft.com/Offic...ba1c-446a-8ff2-221769a58ba51033.mspx?mfr=true
it mentions near the following near the bottom:
"Because of the layers required to get from Access to SQL Server in the ADP
architecture, it is often easier to optimize MDB/ACCDB file solutions.
However, there are some scenarios where a report might be generated
significantly faster in an ADP file. To add these performance improvements
and retain the flexibility of SQL Server, you can build the majority of the
application in an MDB or ACCDB file and have the file load reports from a
referenced ADP file."

How can a report be loaded from a referenced ADP file? Can Access launch
reports or forms from another file? If so how?

 

[Sylvain Lafontaine]

Like any MDB files, ADP projects are Access documents (ie, they are opened
by Access) and as such, can easily be used as external modules in the same
way as any other MDB, MDE or MDA files. There is no difference between
using an ADP project or a MDB file. Here how to do this:

1- Create an ADP project with a form taking no external parameter (for the
moment); call this form MyForm.

2- In a public module of this ADP project, create the following function:

Public Function AutomationMyForm() As Form_MyForm

Set AutomationMyForm = New Form_MyForm

End Function


3- In the Project Properties in the VBA window, give the project a name such
as MyADP.

4- Open a MDB file and then open the References dialog window that is part
of the VBA window. Use the Browse button to locate and open the ADP file.

5- From a form (or a module), add a Public Object variable and use it to
store the value returned by a call to the now external function
AutomationMyForm:

Option Compare Database
Option Explicit

Public F As Object

Private Sub Command0_Click()

Set F = AutomationMyForm()
F.Visible = True

End Sub


And voilà! The ADP form should open in the MDB application. From now on,
you can manipulate this form in exactly the same way that you would
manipulate any other anonymous form. (Note: the above exemple use a
referenced ADP file but I suppose that the other methods of using Access as
an automation server should work as well.).

My advice: this is probably a total loss of time. Either you don't know how
to create an ADP project and then you can't use this method or you can
create an ADP project and then, I don't see why you should lose your time
doing a part of the application with a MDB file and ODBC linked tables.
This is particularly true if you are capable of creating reports with ADP
because in a database project, it's often more difficult to create the
reports than to create the forms. If you can create the reports, I don't
see how you couldn't do the same with the (easier to do) forms.

By mixing a MDB file with an ADP project, you are probably joining together
the worst of both worlds: you will have the problems of an MDB file with
ODBC linked tables, you will have the problems of an ADP project and
finally, you will have the problems of working with two different files and
mixing these two together.