The Connection object allows you to:
ADO can use any OLE DB provider to establish a connection. The provider is specified through the Connection object’s Provider property. Generally, you should use SQLOLEDB to connect to Microsoft® SQL Server™, although you can also use MSDASQL. If no provider is specified, ADO uses MSDASQL by default.
Using the Execute method of the Connection object is one way to execute an SQL statement against a SQL Server data source.
There are some differences in connection properties between SQLOLEDB and MSDASQL. The online Microsoft MSDN™ Library, Microsoft Visual Studio® version 6.0 edition details the connection properties for MSDASQL.
Like MSDASQL, SQLOLEDB lets you use a connection string to specify the individual connection parameters. If you want to use individual connection parameters:
To specify SQL Server Authentication, use the User ID and Password connection properties.
If you are writing a connection string for use with SQLOLEDB:
To specify SQL Server Authentication, use the keyword UID or User ID for the user (such as sa) and the keyword Pwd or Password for the password.
For more information about a complete list of keywords available for use with a SQLOLEDB connection string, see SQLOLEDB Provider String in Initialization and Authorization Properties.
Note that SQLOLEDB does not allow multiple connections. Unlike MSDASQL, SQLOLEDB does not attempt to reconnect when the connection is blocked.
The following code fragments from the ADO Introductory Visual Basic Sample show how to use Microsoft Visual Basic® to connect to a SQL Server database with SQLOLEDB.
' Initialize variables
Dim cn As New ADODB Connection
. . .
Dim ServerName As String, DatabaseName As String, _
UserName As String, Password As String
' Put text box values into connection variables.
ServerName = txtServerName.txt
DatabaseName = txtDatabaseName.txt
UserName = txtUserName.txt
Password = txtPassword.txt
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Set SQLOLEDB connection properties.
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName
' Decision code for login authorization type: WinNT or SQL Server.
If optWinNTAuth.Value = True Then
cn.Properties("Integrated Security").Value = "SSPI"
Else
cn.Properties("User ID").Value = UserName
cn.Properties("Password").Value = Password
End If
' Open the database.
cn.Open
The following code fragment shows how to use a connection string in Visual Basic to connect to a SQL Server database SQLOLEDB.
' Initialize variables
Dim cn As New ADODB Connection
Dim provStr As String
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Specify connection string on Open method.
ProvStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr
If you need to use MSDASQL to connect to SQL Server, there are two main ways of making a connection.
The first is based on the ODBC API SQLConnect function. This type of connection is useful in situations where you do not want to code specific information about the data source. This may be the case if the data source could change or if you do not know its particulars.
In the code fragment shown, the ConnectionTimeout method sets the connection time-out value to 100 seconds. Next, the data source name, user ID, and password are passed as parameters to the Open method of the Connection object, using an ODBC data source named “MyDataSource” that points to the pubs database on a SQL Server installation . The sa login ID is provided as the second parameter and the password is the third parameter.
Note the alternative syntactical form in the following:
Dim cn As New ADODB.Connection
cn.ConnectionTimeout = 100
' DSN connection. You can use variables for the parameters.
cn.Open "MyDataSource", "sa", "MyPassword"
' Alternative syntax follows:
' cn.Open "DSN=DataSourceName;UID=sa;PWD=Password;"
cn.Close
The second method of opening a connection through MSDASQL is based on the ODBC API SQLDriverConnect function. This type of connection is useful in situations where you want a driver-specific connection string. To make a connection, use the Open method of the Connection object and specify the driver, server name, user ID, password, and database. You can also specify any other valid keywords to include in the connection string. For more information about the keyword list, see SQLDriverConnect.
Dim cn As New ADODB.Connection
' Connection to SQL Server without using ODBC data source
cn.Open "Driver={SQL Server};Server=Server1;Uid=SA;Pwd=;Database=pubs"
cn.Close
This example shows how to use the Execute method of the Connection object to execute commands.
Dim cn As New ADODB.Connection
. . .
Dim rs As New ADODB.Recordset
cmd1 = txtQuery.txt
Set rs = cn.Execute(cmd1)
After the Connection and Recordset objects are created, the variable cmd1 is assigned the value of a user-supplied query string (txtQuery.txt) from a Visual Basic form. The recordset is then assigned the results of a query, by carrying out the Execute method of the Connection object, with the variable cmd1 used as the query string parameter.
This example shows how to use the ADO transaction methods BeginTrans, CommitTrans, and RollbackTrans to manage a transaction.
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
. . .
' Open connection.
cn.Open
' Open titles table.
rs.Open "SELECT * FROM titles", Cn, adOpenDynamic, adLockPessimistic
. . .
' User makes changes.
. . .
' Ask if the user wants to commit to all the changes made.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
cn.CommitTrans
Else
cn.RollbackTrans
End If
After the connection is established, a recordset is opened on the result set of a select query, with a dynamic cursor and pessimistic locking (properties of a Recordset object). After you edit or update the data, you select whether to commit the changes or cancel them. The data changed in the transaction can then be committed or rolled back.