The ADO Connection Object | Ado Examples and Best Practices

Team-Fly

The ADO Connection object is your pipeline (almost literally) to client/server data sources. Although you might not realize it, ADO often creates one of these for you behind the scenes if you don't explicitly code one yourself. Of course, there are exceptions (aren't there always?), and I discuss them in this chapter, but suffice it to say that you usually need to build a Connection object at one point or another.

Your Connection object is used to gather up properties that are passed to the underlying data and service providers—such as ODBC, or OLE DB, or cursor providers. That's why the documentation for the providers (ODBC or OLE DB) provides vital low-level information about what should go into these parameters. Hopefully, you won't have to go there to get this information.

When working with Web pages, we often bypass the creation of the Connection object and sprint right down to the Recordset.Open method. That's cool and quick, but it makes handling the errors tough. Consider that VBScript's error handling is about as sophisticated as it was in MBASIC-80 for CP/M.^[1] Because of this, you'll want to add a little more granularity to your Web-based code to help capture errors as they occur. See Chapter 9 for more details.

Never assume your connection will open. There are lots of reasons for a connection not opening (or not staying open). Let's take a quick look at the top 10 reasons:

The data source isn't there. The server name is misspelled or it's simply down. The filename or its path is wrong, or the system where it lives is down. The server was stolen in the night by animal rights activists.
The network is down. The hub, router, switch, or domain controller is down. One of those little boxes with the cool flashing lights was borrowed to enhance someone's holiday tree display.
The net is up and the server is up, but it's full—you and everyone else have consumed all of the available connections. Your system administrator has been playing with the connections count again—his bonus did not come through.
You don't have enough licenses to upgrade the server to more users. Your boss said you paid too much for the software in the first place and you should have used one of those freeware systems anyway.
The connection was established, but you don't have permission to access the database specified as the initial catalog or default database. You made the SA mad yesterday when you accused him of playing with the connections count.
The server you're trying to connect to does not speak named pipes—only sockets. You forgot that the target server is MSDE.
The Data Source Name you specified in the ConnectionString does not exist. You forgot to install it on every single system in the universe where your application runs.
You chose the wrong provider for the database. You forgot that the right provider for an Access 2000 database is the Jolt 4.0 provider. You did not know that the SA converted all of your Jet 3.5 databases to 4.0 in the night—he's still mad about the connections crack.
You have been using the SA user-id for all of your applications—just like all of the examples in all of the books—and the real SA just changed the password. He won't even speak to you at this point—there are signs he has moved to Argentina.
The user pressed ctrl-alt-delete before your connection could time out because you set the ConnectionTimeout property to 0. They would have waited longer, but they had to leave for the airport—something about a trip south to join the SA.

Frankly, this list is far from complete. Each organization has its own situations that you have to deal with. While many of these errors are trappable, some simply require better organization, teamwork, and discipline on the part of your team, on the part of management, and, if at all possible, on the part of the SA. I know that's asking a lot, but if you expect to have a robust application, it's going to take a lot of work to keep your application connecting and running when things go "snap" somewhere.

A Connection object represents a unique session with a data source. In the case of a client/server database system, it may be equivalent to an actual network connection to the server. Depending on the functionality supported by the provider, some collections, methods, or properties of a Connection object might not be available. How can you tell which are available and which aren't? Again, check out the Properties collection after opening the Connection object.

Using the collections, methods, and properties of a Connection object, you can do the following:

Configure the connection before opening it with the ConnectionString, ConnectionTimeout, and Mode properties. After the Connection object is open, examine the ConnectionString property to see what options were actually used to establish the connection.
Set the CursorLocation property to invoke either the Client Cursor Provider, which supports batch updates, or server-side cursors, which support read-write cursors.
Set the default database (initial catalog) for the connection with the DefaultDatabase property.
Specify an OLE DB provider with the Provider property. Remember that the default provider is the OLE DB provider for ODBC. If you touch the Properties collection before setting the Provider property, it's set to MSDASQL (the provider name for the OLE DB Provider for ODBC).
Manage transactions on the connection, including nested transactions if the provider supports them, with the IsolationLevel, BeginTrans, CommitTrans, and RollbackTrans methods and with the Attributes property.
Establish—and later break—the physical connection to the data source with the Open and Close methods. Note that the Recordset object can open the Connection object on its own.
Execute a command on the Connection object with the Execute method.
Execute a named Command object against the Connection object—including managing parameters and returning a Recordset.
Obtain schema information about your database with the OpenSchema method.
Examine errors returned from the data source with the Errors collection.
Read the version of the current ADO implementation with the Version property.

^[1]CP/M is the 8-bit (8080/Z80) operating system I supported while working at Digital Research in the 80s. MBASIC-80 is one of the early versions of Microsoft Basic.

Team-Fly