Solved

Display an Access report in ASP

Posted on 2001-07-19
14
592 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
  • 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now