28 Aug 10 What is a DSN and how can I use a DSN or a DSN-less connection to connect to a database?

DSN is an abbreviation for Data Source Name. Data Source Name is a parameter that stores Data Source information (which includes the source of data such as relational database, and the connectivity information for accessing the data). The primary purpose of the DSN is to provide connectivity to a database through an ODBC driver.

What is ODBC?

ODBC stands for Open Data-Base Connectivity. This is a method developed for accessing databases, particularly with the intention of making it possible to access the data in them from any application. Thus, it works independent of the Database Management System (DBMS) that is used to maintain the database.

The Data Source information stored in the DSN typically consists of

the database name,
directory,
the database driver,
a UserID,
its password, and
any other essential information

DSNs are particularly useful for retrieving data from your databases to display on your website. Once you create a DSN for your database, you can use the DSN in your website to retrieve and display that information on your database.

You may connect to a database even without creating a DSN, by using a DSN-less connection. The only change is use of a Connection String in place of a rather easy to remember DSN.

Advantages of using a DSN Connection string over a DSN-less connection string

Provides easy to remember data source names.
When there are lots of data sources to use 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.

Sample of a DSN Connection String

<%
Dim con
Set con = Server.CreateObject(“ADODB.Connection”)
con.Open “DSN=mydsn”
‘ Now database is open and we are connected
con.Close
‘We are done so we’ve closed the connection above
Set con = Nothing
%>

There is only a singular advantage of using a DSN-less Connection string instead of a DSN connection string – database access becomes much faster since DSN-less connections use native OLE DB providers, while DSN connections make use of ODBC drivers.

Sample of a DSN-less Connection String

Access database

<%
set cnn = server.createobject(“ADODB.Connection”)
cnn.open “PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:anydatabase.mdb”
%>

Note: In the above example, C:anydatabase needs to be replaced with the actual physical path to the database on the website.

How to construct a DSN-less connection string for Access and SQL Server Databases?

Access Database

With native OLE DB Provider (preferred)

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:pathtodatabase.mdb

Using ODBC connection

Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:pathtodatabase.mdb

Always use the connection string that uses native OLE DB provider because it is faster than the ODBC method of connecting. Data Source or DBQ are the absolute path to the database. If you have a relative path, then you can obtain the absolute path by using Server.MapPath(“/relative/path/to/database.mdb”).

Example

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

SQL Server Database

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

Tags: Abbreviation, actual site, Central Repository, Connection String, Data Source Name, Data Sources, Database Driver, Database Management System, Database Management System Dbms, Database Name, Databases, DBMS, Dsn Connection, Dsn Less Connection, Intention, Microsoft, Name Directory, Odbc Driver, Open Data Base, Relational Database, Retrieving Data, Singular Advantage, Source Names

Filed in DSN Manager with 0 Comments

28 Aug 10 What is a DSN and how can I use a DSN or a DSN-less connection to connect to a database?

Recent Posts

Some good Links

Favorite Gossimer Links

Categories

Tags

Gossimer in the News

Gossimer Promotions and Announcements