DSN vs DSN less Database Connections

Introduction
In this article we will learn the two ways of connecting to database :

• via DSN ( Data Source Name )
• without DSN

DSN Connections
In my earlier article on Accessing the database from ASP I explained connecting to database via DSN in a step by step mannner. So there is no need to repeat that again, you can see it from there.

DSN stands for 'Data Source Name'. It is an easy way to assign useful and easily rememberable names to data sources which may not be limited to databases alone e.g Excel spread sheet etc.

I will now skip the steps of creating and assigning DSN to a database, you can see them by clicking here. Once you are done creating a DSN for your data source ( database lets say ), you can connect to it using following code :

<%
	Dim con
		Set con = Server.CreateObject("ADODB.Connection")

	con.Open "DSN=mydsn"

	' Now database is open and we are connected
	' Do some thing here
	'We are done so lets close the connection

	con.Close
	Set con = Nothing
%>

Explanation
If you have been following my tutorials then above code will be nothing but a piece of cake for you. The only significant point to see is that we have used "DSN=mydsn" to connect to our database using our DSN which in this case is mydsn.

DSN less Connection
DSN less connections don't require creation of system level DSNs for connecting to databases and provide an alternative to DSNs. We will now see how to connect to a database via ASP using Connection String in place of DSN name.

<%
	Dim con
		Set con = Server.CreateObject("ADODB.Connection")

	con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data" & _
		"Source=c:\path\to\database.mdb"

	' Now database is open and we are connected
	' Do some thing here
	'We are done so lets close the connection

	con.Close
	Set con = Nothing
%>

Explanation
The only change is use of a Connection String in place of a rather easy to remember DSN. Above code connects to an imaginary Access database. Connection Strings for other databases are different.

How to construct a Connection String for Access and SQL Server Databases ?

• For Access database :-
  With native OLE DB Provider ( preferred ):
  Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\path\to\database.mdb
  
  Using ODBC connection without specifying a DSN :
  Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\path\to\database.mdb
  
  

  Note, always use the first Connection String that uses native OLE DB provider because it is faster than the second one. 'Data Source' or 'DBQ' are absolute path to the database. If you have relative path then you can obtain absolute path by using Server.MapPath("/relative/path/to/database.mdb") e.g.

  Dim conStr
  	Set conStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  		Server.MapPath("/dbo/database.mdb")

• For SQL Server :
  With native OLE DB Provider ( preferred ):-
  
  Provider=SQLOLEDB; Data Source=server_name; Initial Catalog=database_name; User Id=user_name; Password=user_password
  
  Using ODBC Provider :
  Driver={SQL Server}; Server=server_name; Database=database_name; UID=user_name; PWD=user_password

Why to use DSN Connections ?

• Provides easy to remember data source names.
• When there are lots of data sources to think of and you want a central repository to hold the collection of data sources without having to worry about the actual site and configuration of the data sources.

Why to use DSN less Connections ?

• When you can't register DSNs yourself e.g. when you are running a virtual hosting account on other's server. Stop emailing system administerator, connect to your databases directly.
• Provides faster database access because it uses native OLE DB providers, while DSN connections make use of ODBC drivers.

My Experience
I always use DSN less connections on my site and examples :).

<%
	Dim con
		Set con = Server.CreateObject("ADODB.Connection")

	con.Open "DSN=mydsn"

	' Now database is open and we are connected
	' Do some thing here
	'We are done so lets close the connection

	con.Close
	Set con = Nothing
%>

<%
	Dim con
		Set con = Server.CreateObject("ADODB.Connection")

	con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data" & _
		"Source=c:\path\to\database.mdb"

	' Now database is open and we are connected
	' Do some thing here
	'We are done so lets close the connection

	con.Close
	Set con = Nothing
%>

1. How to display records from top 4 database systems using plain ASP?
2. Uploading Files into an Access Database using plain ASP
3. Displaying Images from an Access Database using plain ASP
4. Fastest way of Database Access : Caching Records in Memory
5. Adding records to the database with ASP
6. Searching and sorting records in a recordset from the database
7. Speedup Database Access using GetRows
8. Inserting Form content into Database with ASP
9. Using ASP pages to page through Recordsets
10. Generating Random Records from the Database