Ado|connection| object What is a connection object?
A Connection object describes the physical connection to the data source. You can also use OLE DB to connect to a data source using ODBC. When you open a Connectiont object, you will attempt to connect to the database. The State property of the Connection object tells us if the connection was successful. Sends an SQL statement to a data source or runs a save stored procedure by using the Connection object's Execute method. If the command you send to the data source requires the data source to return the recordset, the Recordset object is automatically created. When you connect to the database, you can close the connection object.
What are the methods and attributes of the Connection object?
The following table lists some common methods for connection.
Method
Describe
Open
Open a Data source connection
Close
Close the connection to the data source and related objects
Execute
Executes a related query (SQL name or stored procedure, or data provider specific text)
BeginTrans
Start a new transaction
CommitTrans
.
Save some changes or the current transaction purpose is to start a new transaction
RollbackTrans
Cancel some changes the purpose is to start a new transaction when the current transaction and the end transaction
The following table lists the properties of some commonly used connection objects.
Property
Describe
ConnectionString
Contains information about establishing a connection to a data source
ConnectionTimeout
Displays the time it took to try to establish a connection to the data source and create an error
CommandTimeout
Shows the time taken to execute the instruction before interrupting an attempt and returning an error
State
Indicates whether it is connected to the data source or is closed or connected
Provider
Displays the name of the connection provider
Version
Show ADO Version number
CursorLocation
Sets or returns the fixed value of a provider cursor function
How do I connect a data source with a connection object?
With a Connnection object, just specify a connection string to specify the data source you want to join, and then call the Open method to establish the link.
The information provided by connection string makes it easy to establish a connection to the data with the open method. If you decide to work with a connection object, you can use its state attribute. If the connection object is opened then its return value is adStateOpen, if not its return value is adstateclosed. The following example is the use of ODBC to establish a connection with SQL.
Sub ConnectionExample1 ()
Dim CNN as ADODB. Connection
Set cnn = New ADODB. Connection
' Open the connection with ODBC.
Cnn. Open "Pubs", "sa", ""
' Check whether the connection is complete
If CNN. State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today. "
End If
Close Connection Object
Cnn. Close
End Sub
If you only need to connect to a data source. The following code is simpler than the above. As a choice, you can create a connection object, and you can set the ConnectionString property before calling the Open method. This method allows you to connect to a data source and then reuse the object to connect to another data source.
Sub ConnectionExample2 ()
Dim CNN as ADODB. Connection
Set cnn = New ADODB. Connection
' Establish a connection with an ODBC DSN
Cnn. ConnectionString = "dsn=pubs; Uid=sa; pwd=; "
Cnn. Open
' Check to see if a connection has been established with the data source.
If CNN. State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today. "
End If
' Close the Connection object
Cnn. Close
End Sub
Before you make a connection to a connection object, you can set its other properties. For example, you can set the connection timeout.
Sub ConnectionExample3 ()
Dim CNN as ADODB. Connection
Set cnn = New ADODB. Connection
' Set the Connection property
Cnn. ConnectionString = "dsn=pubs; Uid=sa; pwd=; "
Cnn. ConnectionTimeout = 30
' Open the Connection object
Cnn. Open
' Check to see if the data source is connected
If CNN. State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today. "
End If
' Close the Connection object
Cnn. Close
End Sub
The syntax structure of the ConnectionString property assumes that the data source has been established or uses ODBC as a system administrator. The ODBC data source that does not depend on the existence becomes popular. This reduces the burden of installation. The following example is an optional way to connect to SQL Server, relying only on its own ODBC driver
. Sub ConnectionExample4 ()
Dim CNN as ADODB. Connection
Set cnn = New ADODB. Connection
' Open Connection object with reference ODBC driver
Cnn. ConnectionString = "Driver={sql Server};" & _
"Server=rgreennt;uid=sa;pwd=;d atabase=pubs"
Cnn. Open
' Find out if ' attempt to connect worked.
' Check if the connection has been established
If CNN. State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today. "
End If
' Close the Connection object
Cnn. Close
End Sub
Now that the ODBC DRIVER has a wider range of changes, you can use ADO and the Data Source dialog. A more OLE DB provider will soon be connected to the data source. The Microsoft®ole DB Provider for ODBC is the current default provider for ADO. You can set the provider properties of the connection object with different providers.
Sub ConnectionExample5 ()
Dim CNN as ADODB. Connection
Set cnn = New ADODB. Connection
' Set the Provider property to take advantage of OLE DB Provider for ODBC
Cnn. Provider = "Msdasql"
' Open Connection object with ODBC DSN
Cnn. ConnectionString = "Driver={sql Server};" & _
"Server=rgreennt;uid=sa;pwd=;d atabase=pubs"
Cnn. Open
' Check to see if the data source is connected
If CNN. State = adStateOpen Then
MsgBox "Welcome to Pubs!"
Else
MsgBox "Sorry. No Pubs today. "
End If
' Close the Connection object
Cnn. Close
End Sub
The code above sets the PROVIDER property not necessarily because the default provider for ADO is OLE DB PROVIDER for ODBC. This is just to recognize that you know how to set up when you use other OLE DB providers.
How do I execute command with a connection object?
The Connnetion Execute method is used to send a command (an SQL instruction or other textual information) to the data source. If a few rows of recordsets are required to be returned in the SQL directive, a Recordset object will be automatically established.
Sub ConnectionExample6 ()
Dim CNN as ADODB. Connection
Dim rs as ADODB. Recordset
Set cnn = New ADODB. Connection
' References ODBC driver to establish a connection.
Cnn. ConnectionString = "Driver={sql Server};" & _
"Server=rgreennt;uid=sa;pwd=;d atabase=pubs"
Cnn. Open
' Execute the SQL statement to create a Recordset object.
Set rs = cnn. Execute ("Select * from authors")
' Show the first author.
MsgBox rs ("au_fname") & "" & RS ("au_lname")
' Disconnect
Rs. Close
End Sub
Remember that the recordset returned with execute is read-only and is a forward-only cursor. If you need more functions with a Recordset object, you first create the Recordset object and set the property you want to set and then open it with the open method to execute the query and return the desired cursor type.
In the following example, the command object performs a delete instruction, and you do not need to use a Recordset object for any additional data set to return. How many rows of data are deleted? You can know it by recordsaffected parameters.
Sub ConnectionExample7 ()
Dim CNN as ADODB. Connection
Dim rs as ADODB. Recordset
Set cnn = New ADODB. Connection
' Reference ODBC driver establish a connection
Cnn. ConnectionString = "Driver={sql Server};" & _
"Server=rgreennt;uid=sa;pwd=;d atabase=pubs"
Cnn. Open
' Send a delete directive to the data source
Cnn. Execute ("Delete from authors Where au_i
