This section describes a simple routine that sends a simple SQL statement to the database.
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
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.