Can you explain the different ways to connect to a database?

A quick rundown of three ways to connect to a database

1. ODBC Manager
Enter all configuration information through the ODBC manager and the ODBC driver. Connect to your database like this:

[tt]"DSN=Northwind;"[/tt]

2. DSN-less ODBC connection
Script the same information, connect with the ODBC driver but don't need to set up a system level DSN.

SQL Server connection string looks like this:
[tt]"Driver={SQL Server}; Server=(local); Database=Northwind; UID=sa; PWD=;"[/tt]

Access connection string looks like this:
[tt]"Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\Temp\Northwind.mdb"[/tt]

Oracle:
[tt]DRIVER={Microsoft ODBC for Oracle};SERVER=path to server;[/tt] (same as SQL Server)

Microsoft Excel:
[tt]Driver={Microsoft Excel Driver (*.xls)};DBQ=physical path to .xls file; DriverID=278;[/tt]

Microsoft Excel 97:
[tt]Driver={Microsoft Excel Driver (*.xls)};DBQ=physical path to .xls file;DriverID=790;[/tt]

Paradox:
[tt]Driver={Microsoft Paradox Driver (*.db)};DBQ=physical path to .db file;DriverID=26;[/tt]

Text file:
[tt]Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=physical path to .txt file;[/tt]


3. DSN-less OLEDB connection
Script similar information but use the OLEDB native driver.

SQL Server Connection string looks like this:
[tt]"Provider=SQLOLEDB; Data_Source=(local); Initial Catalog=Northwind; User Id=sa; Password=;"[/tt]

Access connection string looks like this;
[tt]"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp\Northwind.mdb"[/tt]

Oracle:
[tt]Provider=MSDAORA.1;Data Source=path to database on server;[/tt] (same as SQL Server)

Indexing Service:
[tt]Provider=MSIDXS.1;Data Source=path to file;[/tt]
---------------------

So what are the differences? Well, it helps if you understand how ODBC is used. ODBC is a vendor independent layer between your applications and the databases. OLEDB is a Microsoft specific layer between your application and the database. When ADO connects to your database through ODBC, it actually goes ADO -> ODBC -> OLEDB -> Database.

So what does all this mean?
1. Using a DSN-less OLEDB connection is going to give you faster access because you don't have to go through the ODBC driver. (ADO -> OLEDB -> Database)

2. If your ISP charges you to set up a DSN, using a DSN-less connection will save you a few bucks.

3. Moving applications between machines is a breeze with a DSN-less connection because you don't have to remember to set up ODBC DSNs on multiple systems.

4. A DSN-less connection is potentially less secure because your DB password is scripted in your page. If someone has access to your ASP source code, they have access to your database password.

That't the gist of it. Hope this helps.