The OleDbConnection Class

An OleDbConnection object supports a connection to an OLE DB data provider. In practice, you usually use OLE DB connections with all data providers except Microsoft's SQL Server. Note that, depending on the OLE DB data provider, not all properties of an OleDbConnection object may be supported.

A central property of connection objects is the ConnectionString property, which holds a string full of attribute/value pairs that contain data needed to log on to a data provider and choose a specific database. These attribute/value pairs are specific to the data provider you're using, and make up a list of items separated by semicolons. You can either assign a connection string to the connection's ConnectionString property, or you can pass the connection string to the connection object's constructor, like this:

Dim ConnectionString As String = "Provider=SQLOLEDB.1;Integrated " & _
     Security=SSPI;Persist Security Info=False;Initial " & _
     "Catalog=pubs;Packet Size=4096;Workstation ID=STEVE;" & _
     "Use Encryption for Data=False"

Dim Connection1 As OleDbConnection = New OleDbConnection(ConnectionString)

If you have no idea what a connection string should look like for a specific data provider and database, use the visual tools built into Visual Basic to construct a few sample strings to that data provider, which you can either use directly in code or modify as you need. To do that, create a connection to the source you want to use (see "Creating a New Data Connection" in Chapter 20), then drag a data adapter to a project's main form (see "Data Access Using Data Adapter Controls" in Chapter 20), which creates both data connection and data adapter objects. Then take a look at the connection object's ConnectionString property in the Properties window.

After you've created a connection object, you can open it with the Open method, and assign it to the Connection property of a command object. (To specify the SQL you want to use, you can pass that SQL to the command object's constructor.) Then you can use the command object with a data adapter. For example, you might assign the command object to the SelectCommand property of a data adapter, and you can use the data adapter's Fill method to execute that command and fill a dataset. When done with the connection, use its Close method to close it. (The connection won't be closed otherwise, even if the connection object goes out of scope.)