MS Access Stored Procedure

[Roland Hall]

I've read several articles, this being one of them:
[url]http://www.aspemporium.com/aspEmporium/tutorials/StoredProcedures.asp[/url]
regarding SP in MS Access. What appears to be straight-forward in SQL is
not so straight forward in Access.

I have a SAM's book, Mastering Microsoft Access 2000 Development where it
discusses ADP files and the simplicity of creating SPs, but states they're
only used with data stored in a SQL database so that appears to be out, for
now.

Thus, I have two questions:
1. I've searched google and not found really good references other than
discussions that either suddenly end without resolution or vague references
other than the above which is not a lot of information.

2. Is it also possible to use reports, stored in Access, that use stored
queries and if so, can you point me to some information or provide an
example?

Ex. I have a report that calls a query which requests user input and
returns the results, formats the page for printing, etc. It would be nice
to harness this. I can export a report but the query has already been
executed by this time.

TIA...

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[Bob Barrows]

Roland Hall wrote:

> I've read several articles, this being one of them:
> [url]http://www.aspemporium.com/aspEmporium/tutorials/StoredProcedures.asp[/url]
> regarding SP in MS Access. What appears to be straight-forward in
> SQL is not so straight forward in Access.
>
> I have a SAM's book, Mastering Microsoft Access 2000 Development
> where it discusses ADP files and the simplicity of creating SPs, but
> states they're only used with data stored in a SQL database so that
> appears to be out, for now.
>
> Thus, I have two questions:
> 1. I've searched google and not found really good references other
> than discussions that either suddenly end without resolution or vague
> references other than the above which is not a lot of information.
>
> 2. Is it also possible to use reports, stored in Access, that use
> stored queries and if so, can you point me to some information or
> provide an example?

Do you mean from ASP?

It is certainly possible to create a report based on a saved parameter query
and run it in Access. I used to do it all the time.

However, if you are talking about displaying that report via ASP, then you
are barking up the wrong tree. Access needs to be involved to run Access
reports. It is possible to automate Access from an external program such as
VB, but MS discourages automating any Office application in server-side ASP
code ([url]http://support.microsoft.com/?kbid=257757[/url])

Remember, Access is a desktop app, and therefore does not behave as a server
application would behave.

I once saw an article in VBPJ that talked about utilizing the Snapshot
Viewer to create a COM dll that queued report requests, but I never tried
it.

Crystal's report designer is just as good as Access's, and it has a Viewer
component that can be called via ASP.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Roland Hall]

"Bob Barrows" wrote:
: Roland Hall wrote:
: > I've read several articles, this being one of them:
: > [url]http://www.aspemporium.com/aspEmporium/tutorials/StoredProcedures.asp[/url]
: > regarding SP in MS Access. What appears to be straight-forward in
: > SQL is not so straight forward in Access.
: >
: > I have a SAM's book, Mastering Microsoft Access 2000 Development
: > where it discusses ADP files and the simplicity of creating SPs, but
: > states they're only used with data stored in a SQL database so that
: > appears to be out, for now.
: >
: > Thus, I have two questions:
: > 1. I've searched google and not found really good references other
: > than discussions that either suddenly end without resolution or vague
: > references other than the above which is not a lot of information.
: >
: > 2. Is it also possible to use reports, stored in Access, that use
: > stored queries and if so, can you point me to some information or
: > provide an example?
:
: Do you mean from ASP?
:
: It is certainly possible to create a report based on a saved parameter
query
: and run it in Access. I used to do it all the time.
:
: However, if you are talking about displaying that report via ASP, then you
: are barking up the wrong tree. Access needs to be involved to run Access
: reports. It is possible to automate Access from an external program such
as
: VB, but MS discourages automating any Office application in server-side
ASP
: code ([url]http://support.microsoft.com/?kbid=257757[/url])
:
: Remember, Access is a desktop app, and therefore does not behave as a
server
: application would behave.
:
: I once saw an article in VBPJ that talked about utilizing the Snapshot
: Viewer to create a COM dll that queued report requests, but I never tried
: it.
:
: Crystal's report designer is just as good as Access's, and it has a Viewer
: component that can be called via ASP.

Yes, I meant from ASP. I thought that was assumed since I was in this NG
but I know how OT questions get posted in the wrong NG. Too bad Microsoft
doesn't put the Access reporting engine in a COM.

Very helpful. Thanks Bob.

I might have an additional question regarding printing and FSO but that
should probably be asked in asp.general. I'll post one there if I get stuck
or need some advice.

Roland

[Bob Barrows]

Roland Hall wrote:

>> It is certainly possible to create a report based on a saved
>> parameter query and run it in Access. I used to do it all the time.
>>
>> However, if you are talking about displaying that report via ASP,
>> then you are barking up the wrong tree. Access needs to be involved
>> to run Access reports. It is possible to automate Access from an
>> external program such as VB, but MS discourages automating any
>> Office application in server-side ASP code
>> ([url]http://support.microsoft.com/?kbid=257757[/url])
>>
>> Remember, Access is a desktop app, and therefore does not behave as
>> a server application would behave.
>>
>> I once saw an article in VBPJ that talked about utilizing the
>> Snapshot Viewer to create a COM dll that queued report requests, but
>> I never tried it.
>>
>> Crystal's report designer is just as good as Access's, and it has a
>> Viewer component that can be called via ASP.

>
> Yes, I meant from ASP. I thought that was assumed since I was in
> this NG but I know how OT questions get posted in the wrong NG. Too
> bad Microsoft doesn't put the Access reporting engine in a COM.
>
> Very helpful. Thanks Bob.

It just occurred to me that I've been out of Access development for several
years now and that there may be features of which I am unaware in the
current version of Access that allow the deployment of reports on the web.
You might want to try asking on one of the Access newsgroups.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Roland Hall]

"Bob Barrows" wrote:
: Roland Hall wrote:
: >> It is certainly possible to create a report based on a saved
: >> parameter query and run it in Access. I used to do it all the time.
: >>
: >> However, if you are talking about displaying that report via ASP,
: >> then you are barking up the wrong tree. Access needs to be involved
: >> to run Access reports. It is possible to automate Access from an
: >> external program such as VB, but MS discourages automating any
: >> Office application in server-side ASP code
: >> ([url]http://support.microsoft.com/?kbid=257757[/url])
: >>
: >> Remember, Access is a desktop app, and therefore does not behave as
: >> a server application would behave.
: >>
: >> I once saw an article in VBPJ that talked about utilizing the
: >> Snapshot Viewer to create a COM dll that queued report requests, but
: >> I never tried it.
: >>
: >> Crystal's report designer is just as good as Access's, and it has a
: >> Viewer component that can be called via ASP.
: >
: > Yes, I meant from ASP. I thought that was assumed since I was in
: > this NG but I know how OT questions get posted in the wrong NG. Too
: > bad Microsoft doesn't put the Access reporting engine in a COM.
: >
: > Very helpful. Thanks Bob.
:
:
: It just occurred to me that I've been out of Access development for
several
: years now and that there may be features of which I am unaware in the
: current version of Access that allow the deployment of reports on the web.
: You might want to try asking on one of the Access newsgroups.

I'll look into it but I wrote the reporting part last night using DHTML.
I'm using two different stylesheets, one for media=screen and one for
media=print. I added a clickable image that pops up a window, passes the
report to it with a close button which does not get rendered during the
print process and call for the print dialog box immediately. The user is
required to pick the printer they want to use and then print. They can
click the close button afterwards.

What I did have an issue with was using this:

<script type="text/javascript">
function window.onafterprint() {
self.close();
}
</script>

It never fires. I know it's not relevant for here so perhaps I will pursue
that in the jscript NG after a little more testing.
Thanks again for your help and I appreciate the follow up. (O:=

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]