Making Database Connections

Opening a connection to a database is a fairly simple process: first, you create a connection object, then you open the object using either a DSN or specified drivers and path. The latter is called a DSNless connection, as it requires no system DSN to be set in the server's ODBC applet in the Control Panel. If you'd like information on setting up a system DSN, be sure to see Setting A System DSN, or if you prefer a more visual approach, check out ASPKicker's ODBC Configuration Page.

By the way, what you call the connection isn't terribly important... I've used some rather weird connection names just to amaze and amuse co-workers and its even more fun when you mess with other object names too. I once spent over two hours writing one database script using themed variables and the like. But then, I'm a seriously sick woman. ;)

Seriously, though, unless you're coding for your own enjoyment, you should really make it real obvious what you are doing so those working with you can follow your logic. Be kind to your fellow programmers if you're in a team development situation - your co-workers will thank you for it. For more information on coding conventions, visit the Coding Conventions page in MSDN's VBScript documentation. And yes, I do break the conventions here to make the scripting easier to read but when I code with others, I do tend to "play nice" and follow the conventions. Unless they're like me and don't give a rip. LOL

MS Access

With DSN and no User ID/Password:
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.open "DSNname"
%>

With DSN and User ID/Password:
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.open "DSNname","username","password"
%>

DSNless

Using physical path as a reference:
<%
Set conn = Server.CreateObject("ADODB.Connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=e:\DataArea\financials.mdb" 
conn.Open DSNtemp
%>

Using Server.MapPath, which is the path from the webserver root (by default, c:\inetpub\wwwroot)
<%
Set conn = Server.CreateObject("ADODB.Connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & Server.MapPath("/database/casino.mdb")
conn.Open DSNtemp
%>

MS SQL Server

With DSN
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=MyDSN;UID=user;PWD=password;DATABASE=databasename"
%>

SQL Server: DSNLess
<%
Set conn = Server.CreateObject("ADODB.Connection")
DSNtemp="DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=databasename"
conn.open DSNtemp
%>

Helpful Hint: To save yourself a whole lot of time and effort, you can fire up a database connection using the global.asa or by whipping up an include file...its a lot quicker *and* if you have to move the database or anything in the connection string changes, you'll only have to edit one file. Ahhh, heaven! :) I'll be posting more on that in the near future (Really!).