How do I connect to the Microsoft Access database using a DSN-less connection?

A DSN-less connection is a method of connecting to a database without specifying a named data source. DSN-less connections don’t require the creation of an ODBC DSN. Instead of relying on information stored in a file or in the system registry (as DSN connections do), DSN-less connections specify the driver name and all driver-specific information in the connection string.

Because a DSN-less connection is essentially “hard-coded” to use a certain driver, user identity and database location, problems may arise when database parameters change. Because the variety of connection parameters can differ greatly from one ODBC data source drive to another, it is recommended that you use a DSN whenever possible.

Microsoft Access DSN-less connection strings

Without a password:

<% Set Cnn = Server.CreateObject(“ADODB.Connection”) strConAccess = “Driver={Microsoft Access Driver (*.mdb)}; DBQ=D:wwwrootdomaincomdatabasedatabase.mdb” Cnn.open StrConAccess %>

With a password:

<%
Set Cnn = Server.CreateObject("ADODB.Connection")
strConAccess = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=D:wwwrootdomaincomdatabasedatabase.mdb; Password=YourDBPassword"
Cnn.open StrConAccess
%>

OLE DB connection:

<% Set Cnn = Server.CreateObject("ADODB.Connection")
strConAccess = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA
SOURCE=D:wwwrootdomaincomdatabasedatabase.mdb"
Cnn.open strConAccess
%>

Microsoft SQL Server DSN-less connection strings

With a password:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL
Server};SERVER=mssql.domain.com;UID=USER;PWD=password;DATABASE=mydatabase"
Conn.open DSNtest
%>

OLE DB connection:

Code:

<% Set cnn = Server.CreateObject(“ADODB.Connection”) cnn.open “PROVIDER=SQLOLEDB;DATA SOURCE=mssql.domain.com;UID=username;PWD=password;DATABASE=mydatabase ” %>