INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ASP 101

Can you explain the different ways to connect to a database? by jfriestman
Posted: 9 Aug 00

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:

"DSN=Northwind;"

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:
"Driver={SQL Server}; Server=(local); Database=Northwind; UID=sa; PWD=;"

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

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

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

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

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

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


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

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

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

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

Indexing Service:
Provider=MSIDXS.1;Data Source=path to file;
---------------------

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.

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close