ACC97: Problems with ASP File Based on Linked Tables (184667)

SYMPTOMS

When you export a linked table or an object based on a linked table to Active Server Pages (ASP), the resulting file may not work correctly. For example, when you open the ASP file in a browser, you may receive the error:

Microsoft OLE DB Provider for ODBC Drivers error <error number>

CAUSE

Exporting a linked table or an object based on a linked table to ASP may result in problems with permissions. When you open the ASP file in a browser, access to the data is granted to the World Wide Web service instead of to you as a user. The account used by the service is IUSR_<servername>, which is created by Microsoft Internet Information Server. For more information, see the following article in the Microsoft Knowledge Base:

161333 ACC97: Check NT Permissions When Using IDC/ASP Files with Access

In addition, when you export a database object to ASP, Microsoft Access automatically generates the script in the ASP file. This script contains not only the connection string that points to the data source, but also the SQL statement used to create the recordset. Microsoft Access uses Microsoft Jet SQL to create this SQL statement. If the object that you are exporting to ASP is linked to an ODBC data source, that data source may not be able to process the Microsoft Jet SQL in the script.

RESOLUTION

Use Microsoft Access to export only local objects to ASP. ASP files that work with data in a linked table should be written to communicate directly with that data. Consider using a tool such as Microsoft Visual Interdev to help you to create these ASP files.

MORE INFORMATION

The following diagram depicts how components are used when you export a Microsoft Access object based on a SQL Server data source to ASP:

                                 ASP
                                  :
                                 ADO
                                  :
                         OLE Database Provider
                                  :
                                 ODBC
                                  :
                                 Jet
                                  :
                                 ODBC
                                  :
                              SQL Server

However, if you write an ASP file that accesses the data source directly, the components are used as follows:

                                 ASP
                                  :
                                 ADO
                                  :
                         OLE Database Provider
                                  :
                                 ODBC
                                  :
                              SQL Server

REFERENCES

For more information about Microsoft Visual Interdev, visit the Microsoft World Wide Web site at

http://www.microsoft.com/catalog/default.asp?subid=22

and select Visual Interdev.