Page 511 - Microsoft Office Excel 2003 Programming Inside Out

Introducing ADO

Connecting to Different Database Management Systems
The ConnectionString property is the most important property in the Connection object. The
exact information in the connection string varies depending on the type of database you
want to use.
Connection strings are composed of a series of keywords and values separated by semicolons
that provide the information necessary to locate the data provider and the database, along
with any security information that might be required.

Connecting to Access Databases
Access databases rarely require little more than the name of the provider and the location of
the database file in a connection string, like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel2k3\Customer.mdb
The Provider keyword selects the data provider for Access. For Access 2000 and newer data-
bases, you should use the Microsoft.Jet.OLEDB.4.0 provider. Older Access databases might
require this provider: Microsoft.Jet.OLEDB.3.51.

Note The database engine typically used to power an Access database is known as
Microsoft Jet. In addition to Access, several other products rely on the Jet database engine.
However, because most people access Jet only through Access, it’s common to refer to the
database engine as Access as well.

The Data Source keyword points to the location of the database on disk. Typically, this is a file
path as shown in the earlier example, although it can reference a file using a file share format
(\\athena\d\Excel2k3\Customer.mdb).

Connecting to SQL Server Databases
Connecting to SQL Server databases involves a somewhat more complicated connection
string, although it’s not as bad as you might expect.
Chapter 23
Provider=SQLOLEDB.1;Data Source=athena.justpc.com;
Initial Catalog=Music;User ID=Wayne;Password=TopSecret
The Provider keyword references SQLOLEDB.1, which is the SQL Server data provider.

The Data Source keyword points to the database server that you want to access, and the Initial
Catalog keyword indicates the name of the database on the particular database server that
you want to open.

485
Part 6: Excel and the Outside World: Collaborating Made Easy