Configuring Connections
Let’s now take a look at the different ways in which we can configure the connection to a data source through the use of a Connection object. In this section, I will explain how to work with connection strings and Data Source Names (DSNs). Connection strings are detailed explanations of how to open a data source, while Data Source Names are just a name of a definition that is stored on the current machine, by the operating system rather than the application. In addition, I will also talk about how to obtain the version number for the ADO library that you are using and how to set connection options such as the cursor location (whether to run the cursor on the server or the client), the default database setting, and the permission settings.
Working with Connection Strings
All connections revolve around connection strings, which contain all the pertinent information to ADO concerning the establishment of a connection to our data source. The connection string comprises a number of arguments. There are five standard ADO arguments that can be used in a connection string:
- Provider
Identifies the name of the data provider that you wish to use to establish a connection to a data source. The data provider indicates the type of data source. The Provider argument can be set to such things as Microsoft’s OLE DB provider for SQL Server (SQLOLEDB.1) or Microsoft’s OLE DB provider for Jet (Microsoft.Jet.OLEDB.4.0). (I will talk about the various types of data providers later in this chapter.)
- File Name
Specifies an exact filename (including a path) with which the connection should be established. Because this argument forces ADO to load the data provider that is associated with the data-source type of the file, the Provider argument cannot be used with the File Name argument; when the File Name argument is used, the data provider is implicit rather than explicit.
- Remote Provider
Used only when implementing RDS from a client-side Connection object to specify the name of a data provider. As a matter of fact, when using a client-side Connection object, you can use only the Remote Provider and Remote Server arguments.
- Remote Server
Used only when implementing RDS from a client-side Connection object to specify the path to a remote server. As a matter of fact, when using a client-side Connection object, you can use only the Remote Provider and Remote Server arguments.
The ConnectionString is a public property of the Connection object that is set before the connection is opened. Here is an example of a ConnectionString that could be used to connect to an Oracle database, using the username of BigBear and the password, 1810:
"Provider=MSDAORA.1; Data Source=WidgetOracle; User ID=BigBear; Password=1810;"
The default data provider is the Microsoft OLE DB provider for ODBC drivers (or MSDASQL.1). Because it is the default provider, you don’t necessarily have to name it in the ConnectionString. The following two connection strings are identical:
"Provider=MSDASQL.1; Data Source=WidgetsDSN; User ID=BigBear; Password=1810;"
and:
"Data Source=WidgetsDSN; User ID=BigBear; Password=1810;"
Example 4-3 shows the ways in which we can use the ConnectionString when we open the Connection object.
' declare and instantiate a Connection
Dim con As ADODB.Connection
Set con = New ADODB.Connection
' set the Connection String property
con.ConnectionString = "Provider=MSDASQL.1; " _
& "Data Source=BiblioDSN"
' open the Connection object using the connection string that was just set
con.Open
' display the version of ADO
MsgBox "Connection opened with ADO Version " & con.Version
' close the Connection
con.Close
' set the Connection String property with the Open method
con.Open "Provider=MSDASQL.1; " _
& "Data Source=BiblioDSN"
' print the version of ADO and close the Connection object
MsgBox "Connection opened with ADO Version " & con.Version
' close the Connection and clean up
con.Close
Set con = NothingIn the first part of the code, you can see that we set the ConnectionString as a property of the Connection object, and in the second part of the code, we passed the ConnectionString as an argument to the Open method of the Connection object. Either way works, and there is no real benefit to using one method over another.
The ConnectionString property of the Connection object has another useful function, however. Because it has read ability as well as write ability, we can use the ConnectionString property to view the ConnectionString used by a Connection object that was implicitly created with another object, such as a Recordset object, as shown in Example 4-4.
' declare and instantiate a Connection and a Recordset
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset
' open the recordset, creating a Connection object implicitly
rst.Open "Titles", _
"Provider=MSDASQL.1; Data Source=BiblioDSN", _
adOpenForwardOnly, , _
adCmdTable
' set con to the Connection object that was just created by
' the Recordset object in the above Method call
Set con = rst.ActiveConnection
' print the ConnectionString of the implicitly created Connection object
Debug.Print con.ConnectionString
' close and clean up the Recordset object
rst.Close
Set rst = Nothing
' close and clean up the Connection object
con.Close
Set con = NothingAfter running the previous code, the ConnectionString property of the implicitly created Connection object is printed to the Immediate window of the VB IDE. You should get an output message similar to the following:
Provider=MSDASQL.1;Data Source=BiblioDSN; Extended Properties="DSN=BiblioDSN;DBQ=C:\Inetpub\wwwroot\BIBLIO.MDB; DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
By reading the ConnectionString property, you can determine what settings are being used when connecting to a data source. For instance, in the previous example, you could parse the String for MaxBufferSize and see that it was set to 2048.
Working with Data Source Names
Another way in which to use the Open method of the Connection object is for the ConnectionString to be set to a valid Data Source Name (DSN). Valid DSNs are maintained in the ODBC applet in the Windows Control Panel. This is where all of the information regarding connections is stored. Using a DSN allows you to not worry about the actual definition of the connection string in your application. The details (such as the data-source type and location) are maintained by the ODBC applet on the system you are running your application. When using a DSN, ADO looks up the connection information through the ODBC applet.
Installing the DSNs
Throughout the rest of this book, I will be using two DSNs. The first of these two DSNs is BiblioDSN, which uses an ODBC driver for Microsoft Access to connect to the Biblio.mdb database usually located in the C:\Program Files\Microsoft Visual Studio\VB98 directory when you install Visual Basic. The second is SQLNorthwindDSN, which uses an ODBC driver for SQL Server to connect to the Northwind database, installed with SQL Server.
To set up the BiblioDSN:
Open the Data Sources ODBC setup utility within the Control Panel (for Windows 9x and NT) or under Administrative Tools within the Control Panel for Windows 2000.
From this dialog box, select the System DSN tab, and click on the Add button so that you get the Wizard shown in Figure 4-2.
From here, select the Microsoft Access driver, and click Finish. This should bring up the ODBC Microsoft Access Setup dialog box shown in Figure 4-3. Fill out the Data Source Name (BiblioDSN) and the database (by clicking the Select button and navigating to the Biblio.mdb file).
Once you have entered this information, hit the OK button to finish.
To set up the SQLNorthwindDSN:
Open the Data Sources ODBC setup utility within the Control Panel (for Windows 9x and NT) or under Administrative Tools within the Control Panel for Windows 2000.
From this dialog box, select the System DSN tab, and click the Add button so that you get the Wizard shown in Figure 4-1. Next, select the SQL Server driver and click the Finish button. You should see the “Create a New Data Source to SQL Server” dialog box, as shown in Figure 4-4. Fill in the Data Source Name (SQLNorthwindDSN), and choose your SQL Server of choice (I use my local machine).
Click the Next button, which brings up the panel in Figure 4-5. Here you must fill in a username and password to log on to the server. If you can click the Next button again, you don’t have a problem with these settings.
The next screen, shown in Figure 4-6, allows you to choose your default database. Choose the Northwind database now, and click the Next button and then the Finish button so that you can see the summary screen shown in Figure 4-7. Here you can test your data source.
As soon as you choose the OK button, your new DSN is created for you to use.
Opening a connection with a DSN
Once a DSN has been created, all we have to supply ADO is the name of this DSN and logon information, as shown in Example 4-5.
' instantiate a new instance of the Connection
Set con = ADODB.Connection
Set con = New ADODB.Connection
' establish a connection using only a DSN name and logon information
con.Open "BiblioDSN", _
"Tammi", _
"Rocks"
' print the ConnectionString used to establish this connection
Debug.Print con.ConnectionString
' close and clean up
con.Close
Set con = NothingThis code calls the Open method of the Connection object with a
ConnectionString containing only a DSN name. In addition, the
arguments Tammi and Rocks were
used as the username and password. After running the previous code,
you should see output similar to the following in the Immediate
window of the VB IDE:
Provider=MSDASQL.1;Password=Rocks;User ID=Tammi;Data Source=BiblioDSN; Extended Properties="DSN=BiblioDSN;DBQ=C:\Inetpub\wwwroot\BIBLIO.MDB; DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=Rocks; UID=admin;"
Obviously we did not send all of this information to ADO through the ConnectionString, but regardless, it is there. The Provider argument was obtained through the ODBC applet in the Windows Control Panel along with some of the other information. These are the default values of the Connection object.
ADO determines that the ConnectionString argument being passed through the Open method is a DSN if it does not contain an equal sign (=).
It should be noted that you can use any valid connection string when opening a Connection object, not just a DSN. For simplicity in our examples for this chapter, I have chosen to use DSNs until you learn more about the details of a connection string.
Setting Connection Options
There are four connection properties that we can specify via the Connection object:
- Default database
The DefaultDatabase property allows you to specify which database, on a multiple-database connection, is to be the default.
- Data access permissions
By using the Mode property, you can either grant or read the permissions for accessing data for the given connection. In other words, you can determine whether the current user has read, write, or both read and write access to data over the given connection. In addition, you can deny others any of these rights.
These properties are described in the following sections.
Default database
When the particular data provider that you are using allows the use of multiple databases per connection, I recommend that you set the DefaultDatabase property (shown in Example 4-6), because if you are using a DSN, it is possible that it will be pointing to a database that you do not care to use. This property indicates the database that is used for default access, via SQL statements, throughout any use of the Connection object. You should also set the DefaultDatabase for Recordset and Command objects that have been created with their ActiveConnection property set to such a Connection object.
If your data provider allows the use of multiple databases per connection, you must specify the name of alternate databases in SQL statements when you access them. All other statements (those that do not specify a database name) will revert to the default database specified by the DefaultDatabase property.
' instantiate a new instance of the Connection Set con = ADODB.Connection Set con = New ADODB.Connection ' open a connection on a given DSN (Data Source Name) con.Open "SQLNorthwindDSN" ' display the name of the current default database MsgBox "The default database is: " & con.DefaultDatabase ' set the new default database of the currently open Connection object con.DefaultDatabase = "Master" ' display the name of the current default database MsgBox "The default database is: " & con.DefaultDatabase ' close and clean up con.Close Set con = Nothing
In Example 4-6, the output of the DefaultDatabase
property is Master, exactly as it is shown being
set. The location (full qualified path) and filename (minus the
database file extension) is reported.
If your data provider does not allow the use of multiple databases
per connection, the DefaultDatabase property is read-only to your
application. If your data provider does not support the
DefaultDatabase property, either an empty string will be returned, or
you will get an adErrFeatureNotAvailable error.
Data-access permissions
With the Mode property, you can specify the types of permissions that are allowed on a connection. The Mode property can be set only while the Connection object is closed and is read-only once it is opened.
The Mode property can be set to one of the values specified by the
ConnectModeEnum enumeration, which establishes the
permissions for reading and writing data to the connection. The
values of ConnectModeEnum are shown in Table 4-1.
Example 4-7 demonstrates the setting of the Mode property on a Connection object. In addition, this example displays a message based on the current setting of the Mode property.
Dim con As ADODB.Connection
Dim sPermissions As String
Set con = New ADODB.Connection
' do not allow other users to write to the data source
con.Mode = adModeShareDenyWrite
' open the Connection object with a connection string
con.Open "DSN=SQLNorthwindDSN"
' print the current Mode property setting once the connection object
' is opened to verify the permissions
Select Case (con.Mode)
Case adModeUnknown:
sPermissions = "Unkown or unset permissions."
Case adModeRead:
sPermissions = "User cannot read data."
Case adModeWrite:
sPermissions = "User cannot write data."
Case adModeReadWrite:
sPermissions = "User cannot read nor write data."
Case adModeShareDenyRead:
sPermissions = "Other users cannot read data."
Case adModeShareDenyWrite:
sPermissions = "Other users cannot write data."
Case adModeShareExclusive:
sPermissions = "Other users cannot read or write data."
Case adModeShareDenyNone:
sPermissions = "Other users cannot do anything with data."
End Select
' close the connection to the data source
con.Close
' display permissions
MsgBox sPermissions
' clean up
Set con = NothingThe Mode property is very useful when you need to restrict other users from accessing your data source. This usually occurs when yours needs to be the only application that allows changes to a particular database or when it is important that other applications do not change information that your application has changed.
Timeout setting
The Connection object uses the value of
the ConnectionTimeout property to define the maximum number of
seconds that ADO has to attempt to open a connection to a data
source. The default value for this property is 15,
or 15 seconds.
If the value of the ConnectionTimeout property is set to zero, then ADO will wait forever for a connection to complete. By setting the value of this property, you can abandon a connection when the network is too busy for this type of operation to take place. If the connection does not complete within the specified time interval, then an error is generated and ADO cancels the attempt.
The code in Example 4-8 illustrates how you can use the ConnectionTimeout property to abandon the opening of a connection to a data source. Notice how this subroutine handles errors. ADO can produce multiple errors; therefore, we must loop through a collection of errors and handle each one.
Public Sub OpenDataSource( )
On Error GoTo ERR_OpenDataSource:
Dim con As ADODB.Connection
Set con = New ADODB.Connection
' set the timeout period to 2 seconds
con.ConnectionTimeout = 2
' attempt to open the Connection object with a connection string
con.Open "DSN=SQLNorthwindDSN"
'
' do something here
'
' close the connection
con.Close
' clean up
GoTo CleanUp:
' an error has occurred
ERR_OpenDataSource:
Dim oErr As ADODB.Error
' there can be multiple errors in ADO;
' therefore, we must look at all of them
For Each oErr In con.Errors
Select Case (Err.Number)
Case adErrStillConnecting:
' timeout error
MsgBox "The connection timed out on attempting to open."
Case Else:
' other type of error
MsgBox "Other Error: " & oErr.Description
End Select
Next oErr
' this code will be run whether or not there was an error
CleanUp:
' clean up
Set con = Nothing
End SubThe ConnectionTimeout property is read-only once the Connection object is opened, and it can be used only if the specified data provider supports it. Writing to the ConnectionTimeout property while the Connection object is open will generate an error.
Cursor location
When opening a Connection object on a given data source, you can indicate whether you would like either a client-side or a server-side cursor for that object. Client-side cursors indicate that local cursor libraries will process the data from your connection locally. Server-side cursors indicate that the data provider will process the data from your connection on the server.
Your decision to use either client-side or server-side cursors should be based on the relative abilities of your local cursor libraries and those of the data provider. Usually, you would change the location of your cursor to take advantage of special features not available in another location.
Changing the location of your cursor is done with the CursorLocation
property. By setting the CursorLocation property to
adUseClient (or
adUseClientBatch for earlier versions of ADO), you
indicate to ADO that you want your data to be client-side, supplied
by a local cursor library. By setting this property to
adUseServer, which is the default, you indicate
that you wish ADO to use the data provider or driver-supplied cursors
for the given data source, residing wherever the data source resides.
CursorLocation is a read-write property. However, if you change its value, you will not see its effects until the Connection object’s Open method is called. In other words, if the Connection object is already open when you change the value of the CursorLocation property, the location of the cursor will not change until that Connection object is closed and then reopened with the Open method, as Example 4-9 illustrates.
Dim con As ADODB.Connection Set con = New ADODB.Connection ' set the ConnectionString property to use our DSN con.ConnectionString = "SQLNorthwindDSN" ' set the cursor location to client-side con.CursorLocation = adUseClient ' open the Connection object con.Open ' we are using a client-side client ' do something here ' change the cursor location con.CursorLocation = adUseServer ' this has no effect yet until we reopen the Connection object con.Close con.Open ' now we are using a server-side client ' ' do something here ' close the current connection con.Close ' clean up Set con = Nothing
Determining ADO Version Number and Connection State
You can determine the version of ADO that you are using with the Version property of the Connection object. The value returned by the Version property is a read-only string. The following code fragment prints the version of ADO:
Dim con as ADODB.Connection Set con = New ADODB.Connection ' print the current version of ActiveX Data Objects Debug.Print con.Version
You can also determine whether
the current connection is open or closed by reading the value of the
State property of the Connection object. This property returns a long
value that can be used to check the state of the Connection object.
The connection can either be opened or closed, as represented by the
constants adStateOpen and
adStateClosed, respectively.
The following piece of code displays the state of the current Connection object:
If (con.State & adStateClosed) Then
Debug.Print "The con object is currently closed."
End If
If (con.State & adStateConnecting) Then
Debug.Print "The con object is currently connecting."
End If
If (con.State & adStateExecuting) Then
Debug.Print "The con object is currently executing."
End If
If (con.State & adStateFetching) Then
Debug.Print "The con object is currently fetching."
End If
If (con.State & adStateOpen) Then
Debug.Print "The con object is currently open."
End IfGet ADO: ActiveX Data Objects now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
