How do I use the Connection object in ADO?
What is a Connection object?
A Connection object describes the physical connection to the data source. You can use ODBC to connect to the data source using OLE DB. When you open a ConnectionT object, you will try to connect to the database. The State property of the Connection object tells us whether the connection is successful. Send a SQL statement or run a storage process to the data source via the EXECUTE method of the Connection object. If you send a data source to the data source to return a recordset, the recordset object is automatically created. When you connect the database, you can close the Connection object.
What are the methods and attributes of the Connection object?
The following table lists some commonly used methods for Connection.
method
description
Open
Open a data source connection
Close
Close connection with data sources and related objects
EXECUTE
Perform a related query (SQL name or stored procedure, or data provider specific text)
Begintrans
Start a new transaction
CommitTrans
.
Some changes or current dedication is to start a new transaction
RollbackTrans
Cancel some changes in current transactions and end transactions, the purpose is to start a new transaction
The following table lists some of the properties of the common Connection objects.
Attributes
description
Connectionstring
Contains information about establishing connection with data sources
ConnectionTIMEOUT
Display attempts to establish a connection to the data source and generate an error
CommandTimeout
Show the time to perform the instruction before interrupting an attempt and returning an error
State
Indicates if it is connected to the data source or is closed or in connection
Provider
Display the name of the connection provider
Version
Display ADO version number
CursorLocation
Set or return a provider cursor function
How do I use the Connection object to connect the data source?
With a Connnection object, as long as a Connection string is specified, the purpose is to specify the data source you want to join, and then call the Open method to create a link.
The information provided by the Connection String can be used to connect to the data connection with the OPEN method. If you decide to work with the Connection object, you can use its State property. If the Connection object is opened, then its return value is AdStateOpen, if it is not its return value is adstateclosed. The following example is to establish a connection with SQL using ODBC.
SUB connectionExample1 ()
DIM CNN As Adodb.Connection
Set cnn = new adodb.connection
'Open the connection with ODBC.
CNN.Open "Pubs", "SA", ""
'Check if you are finished
IF cnn.state = adStateopen Then
Msgbox "Welcome to Pubs!"
Else
"Sorry. No piers."
END IF
Close Connection object
CNN.Close
End Sub
If you only need to connect a data source. The following code is to be simpler than the above. As a choice, you can create a Connection object, you can set the connectionString property before calling the Open method. This method allows you to connect to another data source after connecting a data source.
Sub connectionExample2 ()
DIM CNN As Adodb.Connection
Set cnn = new adodb.connection
'Establish connection with ODBC DSN
CNN.Connectionstring = "DSN = PUBS; UID = SA; PWD =;" cnn.open
'Check whether connectivity to the data source.
IF cnn.state = adStateopen Then
Msgbox "Welcome to Pubs!"
Else
"Sorry. No piers."
END IF
'Close Connection object
CNN.Close
End Sub
You can set other properties before you establish a connection to the Connection object. For example, you can set the connection timeout.
Sub connectExample3 ()
DIM CNN As Adodb.Connection
Set cnn = new adodb.connection
'Set the connection attribute
CNN.Connectionstring = "DSN = PUBS; UID = SA; PWD =;"
cnn.connectionTIMEOUT = 30
'Open Connection object
CNN.Open
'Check that the data source has been connected
IF cnn.state = adStateopen Then
Msgbox "Welcome to Pubs!"
Else
"Sorry. No piers."
END IF
'Close Connection object
CNN.Close
End Sub
The syntax structure of the Connectionstring property assumes that the data source has been established or used using an ODBC using the system administrator. It does not rely on the existing ODBC data source to become popular. This reduces the burden of installation. The following example is a selectable way to connect SQL Server, only relying on the ODBC Driver that itself exists.
Sub connectExample4 ()
DIM CNN As Adodb.Connection
Set cnn = new adodb.connection
'Open Connection objects using the reference ODBC Driver
CNN.Connectionstring = "driver = {SQL Server};" & _
"Server = rgreennt; uid = sa; pwd =; database = pubs"
CNN.Open
'Find Outiff OuttTempt to Connect Worked.
'Check that the connection has been established
IF cnn.state = adStateopen Then
Msgbox "Welcome to Pubs!"
Else
"Sorry. No piers."
END IF
'Close Connection object
CNN.Close
End Sub
Now ODBC Driver has a wider change, you can use ADO and data source dialogue. Soon, there will be a more OLE DB provider to establish a connection with the data source. The Microsoft® OLE DB Provider for ODBC is the current ADO default provider. You can set the Provider property of the Connection object with a different provider.
Sub connectionExample5 ()
DIM CNN As Adodb.Connection
Set cnn = new adodb.connection
'Set the Provider property to use the OLE DB Provider for ODBC
CNN.Provider = "msdasql" 'Open Connection object with ODBC DSN
CNN.Connectionstring = "driver = {SQL Server};" & _
"Server = rgreennt; uid = sa; pwd =; database = pubs"
CNN.Open
'Check if the data source is connected
IF cnn.state = adStateopen Then
Msgbox "Welcome to Pubs!"
Else
"Sorry. No piers."
END IF
'Close Connection object
CNN.Close
End Sub
The above code sets the Provider attribute is not a must because the default provider of ADO is OLE DB Provider for ODBC. Here you know how to set it when you use other OLE DB Providers.
How do I use the Connection object to execute Command?
ConnNetion's EXECUTE method is used to send a Command (a SQL instruction or other text information) to the data source. If you request a few line record sets in the SQL instruction, a RecordSet object will be created automatically.
Sub connectExample6 ()
DIM CNN As Adodb.Connection
DIM RS as adodb.recordset
Set cnn = new adodb.connection
'Reference ODBC Driver establishes connections.
CNN.Connectionstring = "driver = {SQL Server};" & _
"Server = rgreennt; uid = sa; pwd =; database = pubs"
CNN.Open
'Execute the SQL statement to create a Recordset object.
SET RS = cnn.execute ("SELECT * 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 only a forward cursor. If you need more functions of Recordset objects, you first want to create a RecordSet object and set the properties you want to set and then open it with the Open method to perform queries and return the cursor type you want to get.
In the example below, the Command object performs delete instructions. Since there is no data set to return, you don't need to use Recordset objects. How many row data is deleted? You can know it via the Recordsaffected parameter.
Sub connectExample7 ()
DIM CNN As Adodb.Connection
DIM RS as adodb.recordset
Set cnn = new adodb.connection
'Reference ODBC DRIVER Establish connection
CNN.Connectionstring = "driver = {SQL Server};" & _
"Server = rgreennt; uid = sa; pwd =; database = pubs"
CNN.Open
'Send delete instructions to data sources
CNN.Execute ("delete from authors where au_id = '011-01-0111') 'Check how many row of data is deleted
SET RS = cnn.execute ("SELECT @@ rowcount")
'Show the first field
MSGBOX RS (0) & "Rows Deleted"
'Close connection
Rs.close
End Sub
The following example, this Command runs the data source's stored procedure by the name of the specified stored procedure. Since you need to return data, you must establish a RecordSet object.
Sub connectExample8 ()
DIM CNN As Adodb.Connection
DIM RS as adodb.recordset
Set cnn = new adodb.connection
'Reference ODBC Driver to establish a connection
CNN.Connectionstring = "driver = {SQL Server};" & _
"Server = rgreennt; uid = sa; pwd =; database = pubs"
CNN.Open
'Establish a RecordSet object to run the stored procedure
SET RS = cnn.execute ("Exec Byroyalty 50")
'Display the ID of Author by loop
Do While Not Rs.eof
MSGBOX RS ("au_id")
rs.movenext
Loop
' Disconnect
Rs.close
End Sub
The younger brother is the first translation, and the error is inevitable. I hope the master will give correct, and I hope that I can get it from it in the beginner.
Original: http://msdn.microsoft.com/library/default.asp? URL = / library / en-us / DNADO / HTML / MSDN_WORKSHP2.ASP
