Managing Data with Microsoft® Excel

Using ADO to Move Data from Excel to a Database

There are some minor differences between DAO and ADO when it comes to modifying data. This section examines how you might structure your code using ADO to perform the two tasks discussed in the prior section, adding records and editing existing records.

Using ADO to Add Records

As before, the intent is to scan the data on a worksheet, picking up the names and share prices of stocks and copying them to a database table.

The declarations in the subroutine are of course somewhat different in ADO. A new connection is established (the cnn object) and used to open the recordset.

 Sub AddStocks() Dim cnn As New ADODB.Connection Dim rs401k As ADODB.Recordset Dim wksStocks As Worksheet Dim i As Integer Dim ...