Open a second instance of MS Access 2000 .adp file from within my .mdb file

[WB]

I have an .mdb file that resolves 99% of the users reporting needs.
However, the limitations within the .mdb prevent a couple of reports from
running because of the complexity built into the queries. Through various
posts within the newsgroups the workaround was to use .adp file for the few
reports needed.

What I want to do is open an instance of the .adp from a function call
within the .mdb file. Then I could have the code call the report and print
it without the user ever having to open the .adp manually.

any suggestions?

WB

 

[Dirk Goldgar]

I have an .mdb file that resolves 99% of the users reporting needs.
However, the limitations within the .mdb prevent a couple of reports
from running because of the complexity built into the queries.
Through various posts within the newsgroups the workaround was to use
.adp file for the few reports needed.

I'm curious as to what circumstances required the use of an external
ADP.

What I want to do is open an instance of the .adp from a function call
within the .mdb file. Then I could have the code call the report and
print it without the user ever having to open the .adp manually.

any suggestions?

Assuming you need to do this, I'm thinking it would be something like
this:

Dim appADP As Object

Set appADP = CreateObject("Access.Application")
appADP.OpenAccessProject "C:\YourPath\YourADP.adp"

appADP.Run "YourFunction"

appADP.Quit

Set appADP = Nothing

But I haven't tested that to see if there are any wrinkles to iron out.

 

[WB]

I'm curious as to what circumstances required the use of an external
ADP.

If you search for the following thread (System not Responding when query is
executed) within the access.queries group that should give an example.


Meanwhile, I will give your suggestion a try.

thanks

WB

 

[Dirk Goldgar]

If you search for the following thread (System not Responding when
query is executed) within the access.queries group that should give
an example.

If you're connecting to SQL Server via linked tables, did you consider
running the query as a pass-through query (after revising it to conform
to T-SQL syntax, of course)?

 

[wb]

Yes, I am connecting via linked tables. I have never worked with
pass-through queries....I will look into that, thanks!
It would be great if that would work because it seems strange that I have to
use an .adp to get some data, and the .mdb for the rest.

thanks for following the tread.

wb

 

[wb]

One more thing about pass-through.....Can they have parameters?

 

[Dirk Goldgar]

One more thing about pass-through.....Can they have parameters?

Pass-through queries can't have parameters as such. What you can do is
either (a) rewrite the query's SQL before executing it, or (b) if you're
running the query in code to return a recordset, you can create a
temproary querydef in memory and open your recordset on that -- or use
an ADO recordset instead.