Display an Access report in ASP

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?

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) ?

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.

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

' 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
%>

%><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><%
%>

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.

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

Featured Post

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.