ACC2000: "Can't Open the Table" in Data Source of ASP File Error (197113)

SYMPTOMS

When you try to open a Microsoft Access table in Design view, you may receive the following message:

You can't open the table '<tablename>' for modification.

A query or form bound to the table is open, you may not have permission to open this table in Design view, or another user has the table open. Do you want to open this table as read-only?

If you have opened a query or form bound to this table, close it, and then try again to open the table in Design view.

You may also receive errors if you try to move or copy a database in Windows Explorer; for example you may receive the message:

Cannot move <database>: There has been a sharing violation.

The source or destination file may be in use.

-or-

Cannot rename <database name>: Access is denied.

Make sure the disk is not full or write-protected and that the file is not currently in use.

CAUSE

The database is an ODBC data source that is used by an ASP file. For example, this behavior would occur if the table itself, or a query or form based on that table, had been exported to ASP format. Although the ASP file, itself, may not currently be open in a user's browser, the Session object for a particular user session persists for the period of time specified by its Timeout property. The ASP file contains code that creates a Connection object and stores that connection in the session variable. Therefore, the connection to the Microsoft Access database that serves as the ODBC data source persists until the session times out. The default value of the Timeout property is 20 minutes.

RESOLUTION

Use one of the following methods to resolve this issue.

Method 1

Instead of editing the database that is the ODBC data source, edit a copy of that database. When all sessions have expired, export the redesigned table to the ODBC data source, or copy the database itself to the file that serves as the data source. This is the recommended method to resolve this issue.

Method 2

Export the database object to ASP format again, and assign a value to the Timeout property. To do so, follow these steps:

Start Microsoft Access and open the sample database Northwind.mdb.
With focus on the name of the Products form, click Export on the File menu.
In the Export Form 'Products' As dialog box, select your Web server's Scripts folder from the Save In box, and from the Save As Type box, select Microsoft Active Server Pages (*.asp). Then, click Save.
In the Microsoft Active Server Pages Output Options dialog box, enter the name of your DSN in the Data Source Name box, enter http://<ServerName>/Scripts (where <ServerName> is the actual name of your Web server) in the Server URL box, and enter some value less than 20 in the Session Timeout (Min) box. Then, click OK.

Method 3

NOTE: This section contains information about editing ASP files and assumes that you are familiar with editing ASP files. Microsoft Access Product Support professionals do not support customization of any HTML, HTX, IDC, or ASP files.

Edit your ASP file and specify the value of Session.Timeout. To do so, follow these steps:

Open the ASP file in a text editor, such as Notepad.

Locate the following text after the TITLE tags near the beginning of the file:

      </HEAD>
      <BODY>
      <%
      If IsObject(Session("<Your ODBC Data Source Name>_conn")) Then

Edit the text so that it reads as follows

      </HEAD>
      <BODY>
      <%
      Session.timeout = n
      If IsObject(Session("<ODBC Data Source Name>_conn")) Then

where n is the number of minutes for which you want the session to last before it times out.

Save and close the file.

MORE INFORMATION

Steps to Reproduce Behavior

The following example assumes that you have created an ODBC data source based on the sample database Northwind.mdb.

Start Microsoft Access and open the sample database Northwind.mdb.
With focus on the name of the Products form, click Export on the File menu.
In the Export Form 'Products' As dialog box, select your Web server's Scripts folder from the Save In box, and from the Save As Type box, select Microsoft Active Server Pages (*.asp). Then, click Save.
In the Microsoft Active Server Pages Output Options dialog box, enter the name of your DSN in the Data Source Name box and in the Server URL box, type http://<ServerName>/Scripts (where <ServerName> is the actual name of your Web server). Then, click OK.
Close the database.
Open a browser on a client computer, and type the URL for Products.asp in the Address box. For example:
http://<servername>/scripts/Products.asp
Close the browser.
On the server, open the sample database Northwind.mdb in Microsoft Access.
Try to open the Products table in Design view. Note that you receive the following error:
You can't open the table 'Products' for modification.