Hi all,

I am relatively new to ASP and have been tearing my hair out for the last 2 days as nobody in the office has been able to help me.

I am creating a database in access in which every table has a CompanyID, What I am trying to do, is to use ASP to generate snapshots from an ms Access report, where the report only selects the appropriate entries according to the CompanyID. Generating a report is easily enough done, however, because my report is based on a query that needs a parameter, a prompt appears asking for the value. Therefore this relies on the integrity of the user to ensure that they enter their own CompanyID and not anyone elses.

So, my question is.

Is there any way to automatically fill in the prompt for the parameter when it appears, without giving the user the chance to enter in any number, or better still, to place the parameter into the ASP code that generates the snapshot??

Usually, you would use a SQL query to select the data you want from your database tables. The query allows you add in filter criteria. You wouldn't actually use the access "query" you are familiar with, that's an internal object within the access database that you see and use when you are viewing the database from within the access desktop application. Instead, you are connect directly to the data and use SQL queries to select which bits you want. How familiar are you with SQL? (when you are in access, you can select the SQL view in a query and you will see the text syntax behind the query that you build in the access interface.)

Peter
------------------------------------------------------
Work smarter, not harder.

use ado instead for the recordset and instantiate the access database programatically

http://p2p.wrox.com/topic.asp?TOPIC_ID=5345



Sal

Hi,
I am relativley new to sql but I can understand it.

The reason i want to use snapshots is because the formatting is a lot nicer than if i had to generate my own in HTML from an SQL query in the ASP page. They are also very easy to print and save from.

I already know how to generate a snapshot from an access report with the appAccess.docmd.outputTo method but, there are going to be multiple companies accessing my database, all with their own individual ID numbers. All these companies use the same report and the same query, except in the WHERE clause where there is a
CompanyID=[x].

Since my report is based on a query that requires a parameter, when the outputTo method is executed, it prompts the user for a value to fill the parameter. I'd rather not generate a seperate version of each report for each company as there are many reports for each company and it would get out of hand.

So, is there any way in which the prompt can be automatically answered with the CompanyID of the current client?

I had the system woring fine where each company had a seperate database, but my boss informed me nicely that a single database for all companies is what they wanted.

I hope that was clearer.

Thank you for all your help

What is this "snapshot" you speak of? This is something in Access right?

If you want to have these reports served up in ASP, then you'll have to do it thru HTML and the browser. I don't think you can use a snapshot the way you think you can.

In order to have a company specific report you would need to build a page in ASP that outputs the report data, and filter the data based on your database fields. This part you seem to understand. You could use a URL like this...

http://myserver/report.asp?companyID=<companyID>

A potential problem with this is that if <companyID> is strictly a numeric value, then it's easy for someone to change that and see another company's data. So you could do one of several things: A) Secure access with some kind of password that is also passed in the URL querystring; B) Use some kind of unique identifier for the company that is more difficult to spoof, like a GUID (I don't know if access has those built in). Depending on the availability of a GUID you might just want to go with the password route. You would need to add a password field to your company data somewhere, then use that as part of the SQL query so that a company's data is more secure. The URL would then look like this:

http://myserver/report.asp?companyID=<companyID>&password=<passwor d>

Either way you do this, I think you are going to have to recreate the report in HTML. I don't think it's that easy (if possible at all) to have an Access report output to HTML. But I'm no Access expert so don't take my word for it.

Peter
------------------------------------------------------
Work smarter, not harder.

A snapshot is a high fidelity representation of an access report. Access has the ability to export a report in this format which can then be easily viewed using microsoft snapshot viewer in an activex control.

They can be used the way i think they can because I am using them :D

ASP can be used like this to export an access report in snapshot format
<%
    Dim objAccess
    Set objAccess = Server.CreateObject("Access.Application")
        objAccess.OpenCurrentDatabase request("APPL_PHYSICAL_PATH") & "databasename.mdb"
    objAccess.DoCmd.OutputTo 3, "reportName", "Snapshot Format (*.snp)", "snapshotname.snp", 0
    objAccess.DoCmd.Quit 2
%>
My report is based on a parameter query, therfore when this is executed it prompts the user for a parameter

All i want to know is if it is possible to automatically respond to the prompt when it appears, through the use of a macro or other means.

Thanks again for your time

I understand the Snapshot issue. I agree with you. You can open Access programatically, pass a parameter to an ado recordset, or even pass the whole recordset to access and then export the report as a snapshot.

Or you can automate access to create the reports beforehand for each of your customers to make them available without going through too much trouble.





Sal

Thanks for the reply sal,

From your reply I take it that there is no way of turning off the prompt asking for the parameter and putting in a value automatically. Its a shame because I think it would be quite a useful feature but anyway, can you please expand on how would i go about automating access to create the reports for me? Would this be done like on a timer, say they are updated every 5 mins or so?

cheers

You can schedule a module to run with a server OS. Get with your Network Admin. Just make sure that the file is completly locked from the world and does not get shared to ensure that it will open and close properly.



Sal