Executing statements using the Command object

This section describes a simple routine that sends a simple SQL statement to the database.

Sample code

You can try this routine by placing a command button named Command2 on a form, and pasting the routine into its Click event. Run the program and click Command2 to connect, display a message on the database server window, and then disconnect.

Private Sub cmdUpdate_Click()
   ' Declare variables
   Dim myConn As New ADODB.Connection
   Dim myCommand As New ADODB.Command
   Dim cAffected As Long

   ' Establish the connection
   myConn.Provider = "ASEOLEDB"
   myConn.ConnectionString = "Data Source = MANGO:5000; User ID=sa;PWD=;"+_
      "Initial Catalog=pubs2;"
   myConn.Open

   'Execute a command
   myCommand.CommandText = "INSERT INTO publishers values" +_
      "('7777', 'American Books', 'Boston', 'MA')"
   Set myCommand.ActiveConnection = myConn
   myCommand.Execute cAffected
   MsgBox CStr(cAffected) + " rows affected.", vbInformation
   myConn.Close 
End Sub

Notes

After establishing a connection, the example code creates a Command object, sets its CommandText property to an insert statement, and sets its ActiveConnection property to the current connection. Then, it executes the insert statement and displays the number of rows affected by the update in a message box.

In this example, the insert statement is sent to the database and committed as soon as it is executed.