Dynamic Excel Reports with ASP

Sometimes it's useful to present data to users in Excel format, so they can easily manipulate the data themselves. In this article, a very simple technique for accomplishing this is demonstrated.

Technique

There are many situations in which you may wish to convert table data into an Excel spreadsheet format for the user. There are several methods available for doing this; I will describe in this article one of the simplest ones. It basically tricks the user's browser into thinking the HTML it is downloading is actually an Excel document, and then Excel does the rest of the work by parsing the HTML into a worksheet. Because of the way this works, although this technique is free and easy, it is also very limited in how it can be used. Also, this method only works if the client has Excel 97 or later installed.

If you need to generate Excel documents on the web server in any kind of scalable, robust, or customized fashion, the best tool available is OfficeWriter (formerly ExcelWriter) from SoftArtisans. AspAlliance author Andrew Mooney has written a fairly detailed review of an older version (v4) of ExcelWriter.

In order to create an Excel report dynamically, you must simply create a .asp file with the header of:

1 <% 2 Response.ContentType = "application/vnd.ms-excel"
3 %>

This informs the browser that the code to follow is Excel formatted, and Netscape or IE will prompt the user to Save or Open the file. When they Open the file, Excel is launched and the report is viewed by Excel. In order for Excel to understand your data, you need only to create an HTML table, which Excel 97 will then convert into its own format. NOTE: This must be the first line of code on the page! (Actually, it just has to be before any other header or HTML info is output to the browser, but put it at the top and it won't cause you problems)

Code

They also want to be able to manipulate this data using Excel, and perform some calculations on it. They created an Excel sheet using this code:

<%
Response.ContentType = "application/vnd.ms-excel"

set conntemp=server.createobject("adodb.connection")
cnpath="DBQ=" & server.mappath("/stevesmith/data/timesheet.mdb")
conntemp.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath
set RS=conntemp.execute("select * from donut")
%>
<TABLE BORDER=1>
<TR>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through Fields Names and print out the Field Names
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2 'row counter
For i = 0 to RS.Fields.Count - 1
%>
<TD><B><% = RS(i).Name %></B></TD>
<% Next %>
<TD><B>On Hand (calculated)</B></TD>
<TD><B>Gross (calculated)</B></TD>
</TR>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through 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 %>
<TD>=b<%=j%>-c<%=j%>-d<%=j%></TD>
<TD>=d<%=j%>*e<%=j</TD>
</TR>
<%
RS.MoveNext
j = j + 1
Loop
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Make sure to close the Result Set and the Connection object
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
RS.Close
%>
<TR BGCOLOR=RED>
<TD>Totals</TD>
<TD>=SUM(B2:B6)</TD>
<TD>=SUM(C2:C6)</TD>
<TD>=SUM(D2:D6)</TD>
<TD>n/a</TD>
<TD>=SUM(F2:F6)</TD>
<TD>=SUM(G2:G6)</TD>
</TR>
</TABLE>

Add a date or time stamp to new records in access db

In some Microsoft Office Access tables, it is important to keep track of the date or the date and time when a new record is added. This is often referred to as a date or time stamp. You can use the Now or Date functions to have Access automatically fill in the date or time when a new record is added. Use the Now function to fill in the date and time, or the Date function to fill in just the date.

Add a date or time stamp field

1. In the Navigation Pane, double-click the table to which you want to add the time stamp field.

   Access opens the table in Datasheet view.

2. In the first blank column, double-click the column header labeled Add New Field, type a name for the field, such as Date Added, and then press ENTER.
3. Select the column that you just added and then, under Table Tools, on the Datasheet tab, in the Data Type & Formatting group, select Date/Time in the Data Type list.
4. Click the Microsoft Office Button and then click Save, or press CTRL+S.
5. On the Home tab, in the Views group, click View, and then click Design View.
6. In the Field Name column, click your new field.
7. Under Field Properties, on the General tab, click in the Default Value property box, and then type Now() or Date().
8. Click the Show Date Picker property box, and then select Never from the list.
9. Save your changes, and then close the table.

Each time you add a new record to the table, Access automatically inserts the date or the date and time in the Date Added field.

List of reserved words in Access 2002 and in later versions of Access

This article lists words and symbols that you should not use in field, object, and variable names in Microsoft Access 2002 and later versions of Access because they are "reserved words." Reserved words have a specific meaning to Access or to the Microsoft Jet database engine. If you use a reserved word or symbol, you may receive an error such as the following:

The wizard was unable to preview your report, possibly because a table needed by your report is exclusively locked.

If you use a reserved word, such as date, value, name, text, and year, in Access 2007, you may receive the following message:

The Name you supplied is a reserved word. Reserved words have a specific meaning to Microsoft Office Access or to the Microsoft Office Access database engine

For existing objects with names that contain reserved words, you can avoid errors by surrounding the object name with brackets ([ ]).

MORE INFORMATION

Because it is not practical to provide a list of all reserved words, such as built-in function names or Microsoft Access user-defined names, please check your product documentation for additional reserved words. Note that if you set a reference to a type library, an object library, or an ActiveX control, that library's reserved words are also reserved words in your database. For example, if you add an ActiveX control to a form, a reference is set, and the names of the objects, methods, and properties of that control become reserved words in your database.

-A

ADD

ALL

Alphanumeric

ALTER

AND

ANY

Application

AS

ASC

Assistant

AUTOINCREMENT

Avg

-B

BETWEEN

BINARY

BIT

BOOLEAN

BY

BYTE

-C

CHAR, CHARACTER

COLUMN

CompactDatabase

CONSTRAINT

Container

Count

COUNTER

CREATE

CreateDatabase

CreateField

CreateGroup

CreateIndex

CreateObject

CreateProperty

CreateRelation

CreateTableDef

CreateUser

CreateWorkspace

CURRENCY

CurrentUser

-D

DATABASE

DATE

DATETIME

DELETE

DESC

Description

DISALLOW

DISTINCT

DISTINCTROW

Document

DOUBLE

DROP

-E

Echo

Else

End

Eqv

Error

EXISTS

Exit

-F

FALSE

Field, Fields

FillCache

FLOAT, FLOAT4, FLOAT8

FOREIGN

Form, Forms

FROM

Full

FUNCTION

-G

GENERAL

GetObject

GetOption

GotoPage

GROUP

GROUP BY

GUID

-H

HAVING

-I

Idle

IEEEDOUBLE, IEEESINGLE

If

IGNORE

Imp

IN

INDEX

Index, Indexes

INNER

INSERT

InsertText

INT, INTEGER, INTEGER1, INTEGER2, INTEGER4

INTO

IS

-J

JOIN

-K

KEY

-L

LastModified

LEFT

Level

Like

LOGICAL, LOGICAL1

LONG, LONGBINARY, LONGTEXT

 

-M

Macro

Match

Max, Min, Mod

MEMO

Module

MONEY

Move

-N

NAME

NewPassword

NO

Not

Note

NULL

NUMBER, NUMERIC

-O

Object

OLEOBJECT

OFF

ON

OpenRecordset

OPTION

OR

ORDER

Orientation

Outer

OWNERACCESS

-P

Parameter

PARAMETERS

Partial

PERCENT

PIVOT

PRIMARY

PROCEDURE

Property

-Q

Queries

Query

Quit

-R

REAL

Recalc

Recordset

REFERENCES

Refresh

RefreshLink

RegisterDatabase

Relation

Repaint

RepairDatabase

Report

Reports

Requery

RIGHT

-S

SCREEN

SECTION

SELECT

SET

SetFocus

SetOption

SHORT

SINGLE

SMALLINT

SOME

SQL

StDev, StDevP

STRING

Sum

-T

TABLE

TableDef, TableDefs

TableID

TEXT

TIME, TIMESTAMP

TOP

TRANSFORM

TRUE

Type

-U

UNION

UNIQUE

UPDATE

USER

-V

VALUE

VALUES

Var, VarP

VARBINARY, VARCHAR

VERSION

-W

WHERE

WITH

Workspace

-X

Xor

-Y

Year

YES

YESNO

For more information about special characters to avoid using when you work with the database object names or the field names in all versions of Access, click the following article number to view the article in the Microsoft Knowledge Base:

826763 (http://support.microsoft.com/kb/826763/ ) Special characters that you must avoid when you work with Access databases

Source:http://support.microsoft.com/kb/286335

Active Server Pages (ASP) Access Database Errors

Some of the most common questions asked on the Web Wiz Guide Forums are from people getting errors when using Microsoft Access with ASP. So in this article I'm going to try and explain what a few of the more common errors mean and how to solve them.

Common Access Database Errors

1. Cannot update. Database or object is read-only
2. Operation must use an updateable query
3. General error Unable to open registry key
4. Could not find file
5. Could not use '(unknown)'; file already in use
6. Table 'tblTable' is exclusively locked by user 'Admin' on machine 'MyMachine'
7. Too few parameters. Expected 1
8. Either BOF or EOF is True, or the current record has been deleted
9. Item cannot be found in the collection corresponding to the requested name or ordinal
10. The search key was not found in any record

Cannot update. Database or object is read-only.
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.

This is the most common error that I'm asked about. This error usually happens when you try to insert data into or update data in an Access database. It means that you don't have sufficient permissions to write to the database.

If you are running the web server yourself then read the FAQ, Checking and Setting up the Correct Permissions on the Server.

If you are not running the server yourself you will need to contact your web space provider and ask them how to sort the problem out. You may need to use a special folder or they may have to setup a system DSN for you or change the permissions on the directory containing the database.

Operation must use an updateable query
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

This error usually happens when you try to insert data into or update data in an Access database. It means that you don't have sufficient permissions to write to the database.

If you are running the web server yourself then read the FAQ, Checking and Setting up the Correct Permissions on the Server.

If you are not running the server yourself you will need to contact your web space provider and ask them how to sort the problem out. You may need to use a special folder or they may have to setup a system DSN for you or change the permissions on the directory containing the database.

General error Unable to open registry key
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x6cc Thread 0x78c DBC 0x144cfc4 Jet'.

This error can happen for a number of reasons the main reason being if the path to the database is incorrect.

You need to check that the path to the database is correct (You must use the physical path on the server to the database and not a virtual path).

The error is also quite common if the permissions on the server are incorrect. Check that IIS has sufficient permissions to access the registry and that the correct permissions, read and write, are set on the directory containing the database and the database itself, for the IUSR account.

Could not find file
Microsoft JET Database Engine (0x80004005)
Could not find file 'C:\Inetpub\wwwroot\databaseName.mdb'.

This error is more or less what it says, the database file can not be found. This usually occurs if the path to the database is incorrect.

You need to check that the path to the database is correct (You must use the physical path on the server to the database and not a virtual path).

Could not use '(unknown)'; file already in use
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use.

This is a bit of an odd error that I have never received myself but have been asked about on number of occasions. It usually means that either incorrect permissions are set on the server or the incorrect version of MDAC (Microsoft Data Access Components installed on the server or the version is not correctly installed). You need to ensure the ODBC version you have is 4 or greater.

If you are running the web server yourself then read the FAQ, Checking and Setting up the Correct Permissions on the Server and the FAQ on, Checking you have the correct ODBC Access Database Drivers.

If you are not running the server yourself you will need to contact your web space provider and ask them how to sort the problem out.

Another reason for this error is that you have the database already open in MS Access or another program, if this is the case shut down the other program you have the Access database open in.

Table 'tblTable' is exclusively locked by user 'Admin' on machine 'MyMachine'
Microsoft JET Database Engine error '80004005'
Table 'tblTable' is exclusively locked by user 'Admin' on machine 'MyMachine'.

This error means that you are either unable to open the table or that you already have the table open in 'Design View' in Microsoft Access. Close Access and try again.

Too few parameters. Expected 1
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

This error occurs only with Microsoft Access when one of the field names used in a select statement does not exist in the table being queried.

Check that your SQL query is correct and that you have not misspelled any of the field names in your select statement and that the field name exists in the table being queried.

Either BOF or EOF is True, or the current record has been deleted
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

This is a recordset error. It means that you have tried to read into a variable or display in a web page a record from the recordset that has either been deleted or does not exist.

The most common occurrence of this error is if you have run a database query that has not returned any records and you have then tried to read in a record from the recordset that contains no data.

You need to first check that there is a record in the recordset before reading it in (eg. 'If NOT rsRecordSet.EOF Then').

Item cannot be found in the collection corresponding to the requested name or ordinal
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

This like the error above is a recordset error. You have tried requesting a field from the recordset that does not exist.

Check when you are reading in the field from the recordset into a variable or to display in a web page that you have spelt the field name correctly and that the field exists in the database.

The search key was not found in any record.
Microsoft JET Database Engine (0x80004005)

The search key was not found in any record.
This error often means that the database has become corrupted.

The solution to this is to repair the database. If the database is on a remote server download the database and follow the instructions below to repair the database.

Access 2000, XP, and 2003 Compact and Repair Database

Open the database in Microsoft Access, click on the 'Tools' menu and select 'Database Utilities -> Compact and Repair Database'.

Access 2007 Compact and Repair Database
Open the database in Microsoft Access, click on the 'Office Button' in the top left corner then select 'Manage -> Compact and Repair Database'.

Click for more (Err:80040E14 Syntax errors)