The ADO Object Model - Connection Strings
(Page 7 of 9 )
One of the major differences among the various OLE DB providers is found in the connection string: Different providers require different information to be able to make the connection to the data store.
ADO recognizes only four of the arguments in the connection string; the remaining arguments are passed on to the provider. The main argument you are interested in is Provider, which identifies the OLE DB provider to be used. The second argument is File Name, which can be used to point to an existing Data Link file. If you use a Data Link file, you can omit the Provider argument, because the Data Link file contains this information. The other two arguments (Remote Provider and Remote Server) relate to Remote Data Services; I describe them in detail in Chapter 9.
OLE DB Provider for the ODBC Drivers The OLE DB Provider for ODBC is the default provider, so if you don’t specify which one to use, this is what you’ll get. If you do specify this provider explicitly in your connection string, you must give the rather obscure name MSDASQL. When using the OLE DB Provider for ODBC, you have three choices: use an existing ODBC System DSN, a DSN-less connection string, or an ODBC File DSN.
For a DSN-based connection, simply specify the data source name:
Provider=MSDASQL;DSN=data_source_name;
UID=user_id; PWD=user_password
For a DSN-less connection, the connection string varies with the database you are connecting to. It follows the same conventions as an ODBC connection string; in the Control Panel, you can see the parameters in the ODBC applet. One important option is the same for all ODBC connections, because it specifies the ODBC driver to use:
Provider=MSDASQL;Driver=
The name of the driver specified in the Driver attribute of the connection string will be one of those shown on the Drivers tab of the ODBC Control Panel applet. Your list of drivers may differ from those listed in this screen:
On Windows 2000 and Windows XP the ODBC Data Source Administrator, known as simply Data Sources (ODBC) on this platform, can be found under the Administrative Tools group. Regardless of platform, the list of ODBC Drivers installed on a given system can also be found in the system registry at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers .
You should enclose the driver name in curly braces. Let’s look at some examples.
Microsoft Access
To connect to Microsoft Access, your connection string would start like this:
Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)}
You then must specify the full path and filename to the Access database by using the DBQ attribute:
Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\mdb_name.mdb
Note: An interesting fact has recently come to light that causes the following error with the OLE DB Provider for ODBC:
Microsoft OLEDB Provider for ODBC Drivers error ‘80004005’
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
This seems to occur if you leave a space after the first semicolon in the connection string shown in the preceding code example, just before the Driver. So, this works:
Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\mdb_name.mdb
But this doesn’t:
Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\mdb_name.mdb
Notice the space; it’s hard to spot.
Of course, because this is the default OLE DB Provider, you can use the following string (without specifying the Provider) perfectly well:
Driver={Microsoft Access Driver (*.mdb)}
; DBQ=C:\mdb_name.mdb
Microsoft SQL Server
To connect to SQL Server by using the OLE DB Provider for ODBC (MSDASQL), you must supply a little more information:
- Server is the name of the SQL Server
- Database is the database name
- UID is the SQL Server user ID
- PWD is the password for the SQL Server user ID
For example:
Provider=MSDASQL;Driver={SQL Server}; Server=Tigger;
Database=pubs; UID=sa; PWD=
Microsoft Excel
You can connect to a Microsoft Excel (any version) spreadsheet three ways. The first is by specifying the sheet name as the source of the Recordset:
objRs.Open "[Sheet1$]", objConn, adOpenDynamic, _
adOpenStatic, adCmdTable
The second is by specifying the sheet name with a row and column area:
objRs.Open "Select * from `Sheet1$A2:C4`", oConn, _
adOpenStatic, adLockBatchOptimistic, adCmdText
The third is by specifying a range name:
objRs.Open "Select * from myRange1", oConn, adOpenStatic, _
adLockBatchOptimistic, adCmdText
To use the range method, you must first make sure that a range has been specified in the spreadsheet. This range should enclose all the data that you wish to select, and it equates to the recordset. You can have any number of ranges in a spreadsheet. To create a range, you select the cells in the spreadsheet and enter the name in the range box.
You then specify the range as the Source parameter of the Recordset’s Open method:
objRec.Open "Authors", objConn
In all three cases, the connection string must specify Microsoft Excel as the ODBC driver and the spreadsheet name as the data store name:
Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};
DBQ=C:\xls_name.xls
Text Files
Text files are slightly different from Excel files in the following respect: In the connection string, you specify the directory where the text file resides, rather than the text file itself:
Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt;
*.csv)}; DBQ=C:\directory_name
You then specify the text file as the name of the recordset to open:
objRec.Open "TextFile.txt", objConn
OLE DB Provider for Jet When using the provider for Jet, you must specify only the database name in the Data Source attribute of the connection string:
Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\mdb_name.mdb
If you have a system database, you can use the Properties collection to set this before opening the connection, but you must specify the provider first:
conDB.Provider = "Microsoft.Jet.OLEDB.4.0"
conDB.Properties("Jet OLEDB:System database") = _
"C:\system_db_name"
conDB.Open "Data Source=C:\pubs\pubs.mdb"
A database password is also set in this way:
objConn.Properties("Jet OLEDB:Database Password") =
"LetMeIn"
OLE DB Provider for SQL Server When establishing connections to SQL Server databases using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), the Data Source attribute is used to specify the name or address of the SQL Server. Consequently, an additional attribute named Initial Catalog is used to specify the name of the database for which the connection will be made:
Provider=SQLOLEDB; Data Source= server_name;
Initial Catalog= database_name; User Id= user_id; Password=
user_password
For example:
Provider=SQLOLEDB; Data Source=Tigger; Initial
Catalog=pubs;
User Id=sa; Password=
OLE DB Provider for Index Server For Index Server you need to specify only the provider name, unless you have multiple catalogs in use under Index Server. In this case you use the Data Source to specify the required catalog:
Provider=MSIDXS; Data Source=catalog_name
OLE DB Provider for Internet Publishing The Internet Publishing provider allows you to connect to servers that support either the Microsoft FrontPage Server Extensions or DAV (also known as WebDAV or HTTP-DAV) protocol. This allows you to use ADO to query the servers for directory contents, resources, and so on, as well as to update these resources. You use the Data Source attribute to specify the name of the Web server:
Provider=MSDAIPP.DSO.1; Data Source=http://web.server.name
Alternatively, you can add URL= to the front of the Data Source you are opening, which tells ADO that the Internet Publishing provider is being used. For example:
recRoot.Open "", "URL=http://web.server.name"
Data Link Files A Data Link file can contain connection details for any OLE DB Provider. Data Link files have a .udl suffix and allow the connection details to be stored in a file rather than being embedded in an application. Data Link files can be created by using Windows Explorer. Under Windows 2000 and Windows XP, you should create a Text File, and then rename the suffix to .udl. Double-clicking this file opens the Data Link Properties dialog. Under previous versions of Windows (and ADO), you can select New Data Link File from the Explorer context menu.
To use a Data Link file as a connection string, simply set the File Name option to point to the .udl file:
objConn.Open "File Name=C:\temp\pubs.UDL"
 | This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today. Buy this book now! |
Next: Asynchronous Processing >>
More ASP.NET Articles
More By Apress Publishing
/ 14 
