ACC97: Using a File DSN or DSN-Less Connection with ASP files

• Microsoft Access 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you export Microsoft Access 97 objects to ASP format, you provide the name of a System DSN on your Web server, which Microsoft Access uses in the ConnectionString property of the ASP script that it generates.

You can modify the ASP script so that it uses either a File DSN or a DSN-less ConnectionString (by providing detailed connection information). This capability has the following advantages:

• If you are not the administrator of the Web server, you do not have to ask the administrator to create a System DSN.
• You can change the arguments in a File DSN or in the ASP ConnectionString without having to ask the Web server administrator to modify a System DSN.
• If you are the administrator, you do not have to create multiple System DSNs for every person who wants to create Web pages that have database access.
• File DSNs can be shared with other users.

MORE INFORMATION

The following example shows you how to create an ASP file with Microsoft Access 97, and then modify it so that a System DSN is not required. This section is divided into these parts:

• Important Notes
• Copying Northwind and Your File DSN to the Web Server
• Creating and Modifying the ASP File
• Testing the ASP file

Important Notes

For the sake of simplicity, the example in this article instructs you to copy all files to the same folder on your Web server. This is not a requirement, as long as the ConnectionString information in the ASP file contains the correct path to the files. For example, the database and the File DSN (if you use one) can be located on a network share, and you can use universal naming convention (UNC) paths in your ConnectionString.

All files that you want to access require appropriate permissions on the Web server computer. This includes IIS permissions, NTFS permissions, and network share permissions. If IIS is configured to use Anonymous Access with an Internet Guest Account, then that account must have Read and Write access to the folder in which the database resides. If you use a File DSN, then the Internet Guest Account must have Read access to the File DSN. And if network shares and UNC paths are used in the File DSN or in the ConnectionString, then the Internet Guest Account must have appropriate network share permissions. For a detailed description of permissions issues when using ASP files, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q161333
   TITLE     : Check NT Permissions When Using IDC/ASP Files with Access

Copying Northwind and Your File DSN to the Web Server

You must have a folder on your Web server where you can copy files. The folder should be an IIS virtual directory with Execute permissions. Copy the Northwind sample database, Northwind.mdb, to this folder. Write down the complete path to this file; you will need that information when you create the ConnectionString later. The path can use either a drive letter or a UNC name.

If you plan to use a File DSN in the ConnectionString instead of detailed connection information, make sure the appropriate argument in your File DSN contains the complete path to your database on the Web server (drive letter or UNC path). Copy your File DSN to the same folder on your Web server where you place Northwind.mdb. Then, when you modify the ConnectionString in the ASP file, you only need to provide a path to the File DSN.

For more information about creating File DSNs and DSN-less connections, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q165866
   TITLE     : How to Use File DSNs and DSN-less Connections

Creating and Modifying the ASP File

If you are using a File DSN, please skip to the next section.

1. Start Microsoft Access 97 and open the sample database Northwind.mdb.

2. Export the Customers table to ASP format. When you are presented with the "Microsoft Active Server Pages Output Options" dialog box, leave the Data Source Name box blank.

3. Use Notepad or another text editor to open the Customers.asp file. Look for the following line of code in the first If...End If block in the file:

         conn.open "","",""
   

4. Modify the ASP file to use either a DSN-less or File DSN connection.

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

       a. To use a DSN-less connection, modify the line in step 3 to provide
          the correct ConnectionString information. For example, if you copied
          Northwind.mdb to the C:\Inetpub\wwwroot\Test folder on your Web
          server, type the following as your ConnectionString:
   

            conn.open "driver={Microsoft Access Driver _
            (*.mdb)};dbq=c:\inetpub\wwwroot\test\northwind.mdb;uid=Admin"
   
       b. To use a File DSN, modify the line in step 3 to provide the File DSN
          information. For example, if the File DSN is called Northwind.dsn
          and it resides in the C:\Inetpub\wwwroot\Test folder on your Web
          server, type the following as your ConnectionString:
   
            conn.open "FILEDSN=c:\inetpub\wwwroot\test\northwind.dsn", _
            "Admin", ""
   
   

5. Save and close the Customers.asp file. Copy the file to the same folder on your Web server where Northwind.mdb and your File DSN (if you are using one) reside.

Testing the ASP File

1. Start Microsoft Internet Explorer 3.0, or another Web browser program.

2. Type the uniform resource locator (URL) in the address box of your Web browser to view Customers.asp. For example, if you copied your files to a folder called Test in the scripts folder of your Web server, type:

         http://<servername>/scripts/test/Customers.asp
   

   Note that the URL depends on where your files are located on the Web Server.

3. The Web browser returns the records from the Customers table in Northwind.mdb.

REFERENCES

For more information about exporting ASP files, search the Help Index for "ASP files," or ask the Microsoft Access 97 Office Assistant.

For more information on how to create and modify ASP files, please refer to your Microsoft ASP online documentation.