accessguy52
Programmer
Hello folks, in appreciation to all who helped me, I'd like to post a small but simple solution to putting up reports in your bowser object by way of Excel. This example was used by me courtesy of Steven Smith's example ( I just used a simple table in Access with two 2 fields: Country and TotalSalesByCountry (or something like that - you could name it GDP if you want). Here's the code including Steve's suggestion for a msgbox in case of no records found:
<%
'Open the connection and retrieve data from the database
Set cn = Server.CreateObject("ADODB.Connection"
cn.Open "DSN=mgraph2;UID="";PWD="";DATABASE=mgraph2"
Set rs = cn.Execute("SELECT * FROM Countries"
%>
if rs.BOF and rs.EOF then%>
<HTML>
<head>
<SCRIPT LANGUAGE="JavaScript">
<!-- Hide Javascript
{
alert("No records found -- click OK, then use the Back button to return to form"
}
// - End hide Javascript - -->
</Script>
else
Response.ContentType = "application/vnd.ms-excel"
<TABLE BORDER=1>
<TR>
<%
'Loop thru field names and print out field names
j = 2 'row counter
For i = 0 to rs.Fields.Count - 1
%>
<TD BGCOLOR=YELLOW><B><% = rs(i).Name %></B></TD>
<% Next %>
</TR>
<%
'Loop thru rows displaying each field.
Do While not rs.EOF
%>
<TR>
<% For i = 0 to rs.Fields.Count - 1
%>
<TD VALIGN=TOP><% = rs(i) %></TD>
<% Next %>
</TR>
<%
rs.MoveNext
j = j + 1
Loop
'Clean up
rs.Close
%>
</TABLE>
Just substitute your own tablename and so on. Hope this helps someone else.
Accessguy52
<%
'Open the connection and retrieve data from the database
Set cn = Server.CreateObject("ADODB.Connection"
cn.Open "DSN=mgraph2;UID="";PWD="";DATABASE=mgraph2"
Set rs = cn.Execute("SELECT * FROM Countries"
%>
if rs.BOF and rs.EOF then%>
<HTML>
<head>
<SCRIPT LANGUAGE="JavaScript">
<!-- Hide Javascript
{
alert("No records found -- click OK, then use the Back button to return to form"
}
// - End hide Javascript - -->
</Script>
else
Response.ContentType = "application/vnd.ms-excel"
<TABLE BORDER=1>
<TR>
<%
'Loop thru field names and print out field names
j = 2 'row counter
For i = 0 to rs.Fields.Count - 1
%>
<TD BGCOLOR=YELLOW><B><% = rs(i).Name %></B></TD>
<% Next %>
</TR>
<%
'Loop thru rows displaying each field.
Do While not rs.EOF
%>
<TR>
<% For i = 0 to rs.Fields.Count - 1
%>
<TD VALIGN=TOP><% = rs(i) %></TD>
<% Next %>
</TR>
<%
rs.MoveNext
j = j + 1
Loop
'Clean up
rs.Close
%>
</TABLE>
Just substitute your own tablename and so on. Hope this helps someone else.
Accessguy52