Summary

How do I setup a database connection for MS Access (.MDB) or MS SQL database?

Resolution

There are two ways to do this: The first way does not require a use of a DSN (Data Source Name). This is the fastest and easiest method.

Please choose either Microsoft Access | Microsoft SQL Server database.

Microsoft Access (.mdb)

1. DSNless Method for MS Access - (easiest)
   Create a database connection in your .ASP code as follows:
   
   <%
   Set Conn = Server.CreateObject("ADODB.Connection")
   Conn.Open "Microsoft.Jet.OLEDB.4.0;Data Source=c:\domains\myDomain.com\db\myDB.mdb;User Id=myLogin;Password=myPass;"
   Set rsmyTable = Conn.Execute("SELECT * From myTable")
   %>
   
   
   Substitute myDomain.com, myDB, myLogin, myPass, myTable with your own specific details. This assumes your db is located in the \db directory although if you want you can place it in the wwwroot as well, however take care to update the path in the connectionstring to be c:\domains\myDomain.com\wwwroot\myDB.mdb. NOTE: Doing this will allow your database to be downloaded unless you use password protection (.htaccess).
   
   
2. DSN Method for MS Access
   
   
   
   1. For MS Access (.mdb) files, copy the file to /db directory (one above wwwroot). NOTE: It must reside here for this to work.
      
   2. Open your control panel and click on ODBC Dsn icon.
      
      
   3. Click "Add New"
      
   4. Select "Microsoft Access", click Next
      
   5. Type in a DSN name, for example myDSN. The dropdown box should now contain the .MDB file.
      
   6. Click next and you are finished.
      
   
   

Create a database connection in your .ASP code as follows:

Substitute myDomain.com, myDB, myLogin, myPass, myTable with your own specific details.

Microsoft SQL

1. DSNless Method for Microsoft SQL - (easiest)
   Create a database connection in your .ASP code as follows:
   
   <%
   Set Conn = Server.CreateObject("ADODB.Connection")
   Conn.Open "Driver={SQL Server};Server=sql.myDomain.com,1444;DATABASE=myDB;UID=myLogin;PWD=myPass;"
   Set rsmyTable = Conn.Execute("SELECT * From myTable")
   %>
   
   
   Substitute myDomain.com, myDB, myLogin, myPass, myTable with your own specific details.
   
   
2. DSN Method for Microsoft SQL
   
   
   
   1. Open your control panel and add a new SQL database (under Databases icon)
      
   2. Next ODBC Dsn icon.
      
      
   3. Click "Add New"
      
   4. Select "Microsoft SQL Server 2000", click Next
      
   5. Type in a DSN name, for example myDSN. The dropdown box should now contain the SQL database.
      
   6. Click next and you are finished.
      
   
   Create a database connection in your .ASP code as follows:
   
   <%
   Set Conn = Server.CreateObject("ADODB.Connection")
   Conn.Open"DSN=myDsn;Uid=myUsername;Pwd=myPass;"
   Set rsmyTable = Conn.Execute("SELECT * From myTable")
   %>
   
   
   Substitute myDomain.com, myDB, myLogin, myPass, myTable with your own specific details.
   

Additional Information
ConnectionStrings.com