Solved

Display an Access report in ASP

Posted on 2001-07-19
14
607 Views
Last Modified: 2013-11-28
Hi all.

I've got a client that wants to use Access as the database for his site.  I've talked with him about scaling to SQL Server, but he wants to stay with Access.

The vast majority of asp work I do is with SQL Server or Oracle on the backend.  I've done a little work with Access but almost nothing with it and asp, so I'm a little out of my element.

In Access, he's got some reports that are based on queries that are in turn based on his main table.  He wants to see these reports on his web site.

My question is twofold:
1) Is there a way in asp to display the reports he has already designed in Access?  In other words, do I need to recreate the reports myself or is there some type of Access object I can call in asp and use to display his report on a web page?

2) Is there a way to return the results of an Access query into an asp recordset like I can with a stored procedure in SQL Server?

A wrinkle for both questions: the queries (and therefore the reports) in Access have an input parameter (e.g. county number).  Can you address this too in your answers?

I will increase the points for affirmative answers with code samples.

Thanks.
0
Comment
Question by:mayhew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 4

Expert Comment

by:vindevogel
ID: 6298773
1) There's an Snapshot Viewer for Access.  This is mainly an OCX that allows you to view reports without having the full product.  Sits somewhere on Bill's servers ...

If you do not want to use OCX's on the site, you face another problem.  How to show them.

I'd add Adobe PDF Writer on it.  With that you can write a component (VB) that calls the Snapshot Viewer, prints the report to Adobe PDF Writer, and returns the filename it uses.  Redirect to that PDF and you have a web report...

2) ADO can access the tables, queries, ... in Access.
So there's no real difference in that.  The connect string is a little different.  The rest stays the same.

This is just a hint, leave the question open, code samples are a little to hard for 1), do you need a sample on 2) ?
0
 
LVL 10

Expert Comment

by:makerp
ID: 6298778
its easiest to simply select from the database the same data that is in the reports and then output HTML tables so they look like the results from the access report

stored procedures, just create Queries in access, save them and then use the following to call them

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DSN IN CONTROL PANEL"

Set cmd1 = Server.CreateObject("ADODB.Command")
cmd1.ActiveConnection      = Connection    ' the above connection
cmd1.commandText        = "prime_telno" 'query name
cmd1.commandType      = adCmdStoredProc 'the type
cmd1.Parameters.Append cmd1.CreateParameter ("entry",adInteger,adParamInput)

cmd1.Parameters("entry") = CINT(res("entry_id"))
Set ptel = cmd1.Execute

then simply loop through the results

DO UNTIL ptel.EOF
   Response.Write(ptel("col_name_you_want_to_print")&"<BR>)
LOOP

It probably easy for you to know ask a stream, of q's in this thread
0
 
LVL 5

Author Comment

by:mayhew
ID: 6298923
Hi vindevogel and makerp.  Thanks for the quick responses.

I'll probably end up recreating the reports myself before I add components to the web site.  It's rented space and I don't have the freedom to add components on their server.

He's got about seven reports and he does all sorts of groupings in the Access report designer to get it to look the way he wants.  I figure it's going to take me between 20 - 40 hours to go through these reports, figure out what he's doing, and then recreate them in asp.

I've done some programming with Access COM objects (years ago) and I know that there are ways to expose the queries and reports.  What I'm wondering is if that's possible through asp.  Or is there some other asp/Access trick that I've missed because I haven't been paying much attention to Access?

For the second question, yes I'd like a code sample.  It looks like accessing a query in Access is almost identical to accessing a stored proc in SQL Server according to makerp's code.  Can you validate that code or provide the code that you would have used vindevogel?

Please don't worry about points.  Everyone who helps me will get some.  I just really need to get this worked out.

Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:makerp
ID: 6298931
heres a sweet little example

<%
' simple search engine example that takes a string of comma seperated keywords and then
' searches a simple single tables db with a table called table1 with a name column

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "test_db","",""
' if there no search string then display the form
IF(Request("search") = "")THEN
     %>
          <B>Please enter keywords separted by a space and select the and or radio buttpns</B>
          <FORM ACTION=<%=Request.ServerVariables("SCRIPT_NAME")%> METHOD=POST>
               <INPUT TYPE=TEXT SIZE=80 NAME=search><BR>
               OR<INPUT TYPE=RADIO NAME=s_opt VALUE=OR CHECKED>AND<INPUT TYPE=RADIO NAME=s_opt VALUE=AND><BR><BR>
               <INPUT TYPE=SUBMIT VALUE=Search>
          </FORM>
     <%
ELSE
' else the form has been submitted so lets query

     ' first split the string on spaces to get out keywords
     keywords = Split(Request("search")," ")
     ' get the array size of keywords
     no = UBOUND(keywords,1)
     ' now loop through building out string
     FOR i = 0 to no
          s = s + " name LIKE '%" & keywords(i) & "%' " & Request("s_opt")
     NEXT
     ' chop of the remaing logic operator
     s = LEFT(s, LEN(s) - LEN(Request("s_opt")))
     ' now execute the stmt. response.write for testing
     Response.write("SELECT * FROM table1 WHERE " & s)
     ' exe it and then display our results
     Set rs = Con.Execute("SELECT * FROM table1 WHERE " & s)
     DO UNTIL rs.EOF
          Response.Write(rs("name") & "<BR>")
          rs.MoveNext
     LOOP
END IF
%>
0
 
LVL 10

Expert Comment

by:makerp
ID: 6298934
here is how to connect without a dsn, often usefull if your putting something in rented space

<%
Set Connection = Server.CreateObject("ADODB.Connection")

'Connection.Open "DBQ=D:\examples\testdb.mdb;Driver={Microsoft Access Driver (*.mdb)};"
Connection.Open "test"

Set rs = Connection.Execute("SELECT * FROM sites")
DO UNTIL rs.EOF
     response.write(rs("url"))
     rs.MoveNext
LOOP
%>
0
 
LVL 10

Expert Comment

by:makerp
ID: 6298939
heres how to get column names etc from the ADO recordset, often use full when you dont know the column names in advance

<%
      ' put your username and password in here
      uname = ""
      pswd = ""

      ' create the connection object
      Set Con = Server.CreateObject("ADODB.Connection")
      ' open it using our dsn, uname and pswd
      Con.Open "testsql",uname,pswd
      ' select some data
      Set rs = Con.Execute("SELECT * FROM suppliers")

      %><TABLE WIDTH=100><TR><%
            ' first up lets spit out the column headings
            For each head IN rs.Fields
                  %><TH><%=head.Name%></TH><%
            Next
            %></TR><%
            ' now lets print out our data
            DO UNTIL rs.EOF
                  %><TR><%
                  ' for each item in each row
                  FOR each item In rs.Fields
                        %><TD><%=rs(item.name)%></TD><%
                  NEXT      
                  %></TR><%
                  rs.MoveNext
            LOOP
      %></TABLE><%
%>
0
 
LVL 19

Expert Comment

by:webwoman
ID: 6298958
If he's got groupings in his reports, you'll have to make sure you sort the data by the headings he uses so the records stay together. You may need to just modify the existing queries, or write an SQL statement to handle that. You can use SQL pretty much the same to connect to ASP -- and even if he doesn't have the right queries, you can use SQL to generate the recordsets you need.
0
 
LVL 5

Author Comment

by:mayhew
ID: 6298961
Thanks makerp.  I understand ADO in general.  I need to know specifically how to get into Access queries and reports.
0
 
LVL 10

Expert Comment

by:makerp
ID: 6298996
i do not now if you can get into reports, you may need to create the sql for the report, save it as a query them call that query as shown in the asp. you may have to have several queries for each report depending on how the report is formed
0
 
LVL 4

Accepted Solution

by:
vindevogel earned 100 total points
ID: 6299047
You can't get into the reports of Access ....

You can only use the Database functionality of Access, that is the tables, indexes and queries.

Using those, does not differ in any way of using the with Sql ...

Stored procedures don't really exist on Access, at least, if you don't consider parameter queries to be stored procedures.

I think, based on the ADO, that makerp almost showed everything, so I'm not gonna write code on that ;-)

Accessing the queries is simple ...
Recordset.Source = "NameOfTheQuery"

If you need parameter queries ...
Use the ADO.Command, refresh the parameters, fill them and execute the command, that returns a recordset too.

There a good example on:
http://support.microsoft.com/support/kb/articles/Q200/1/90.ASP
0
 
LVL 19

Expert Comment

by:daveamour
ID: 6299151
Hi guys

Am I right in thing there is a server version of crystal reports that you can use with ASP to create these kind of reports?  This might be a possible solution.

http://www.vbxtras.com/Product.asp?ProductID=84

Dave
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6784453
mayhew---->  Please update and finalize this question.

Also, you have points waiting for you from Yog for help you gave.  Please add your comments to get them here:
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20129976

Your responsiveness to finalizing your open questions in not only needed, but appreciated.

Moondancer
Community Support Moderator @ Experts Exchange
0
 
LVL 5

Author Comment

by:mayhew
ID: 6785908
Thanks for the help guys.  Sorry I let this question get away from me.

I'm also awarding points to makerp.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6785947
Your responsiveness is appreciated.
Moondancer
Community Support Moderator @ Experts Exchange
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question