DataWright Information Services

Consulting and Resources for Excel and Access




Edit a single record in Excel, and update the Access table

This is Part 6 of 7 in a tutorial on using code to integrate
Excel and Access.

In the sample file, Table Download has a button
with the caption Update Current Record. Do the
following to edit a single record:

  • Change one of the country names, e.g. United States of America
    to USA
  • Press the Tab key to stay on the same row; the code uses the
    active row to determine which record to change
  • Click the button, and the change will be written to the
    Access table.

You can check this by deleting all data on this sheet, then
downloading the table again.

Note:

  1. Because all of the code for this tutorial exists in a single
    module, I have placed a
    constant at the top of the module that
    defines the name of the database. This removes the need to keep
    redefining it for each procedure, and means that you only need
    to change one place in the code to refer to a different
    database.
  2. To use this code in your own file you will need to set the
    appropriate references. In the code environment go to Tools >
    References and select Microsoft ActiveX Data Objects 2.x
    Library
    (the highest version available on your system).

The constant is shown below. It needs to go before the first Sub
procedure in the module:

Const TARGET_DB = "DB_test1.mdb"

The code for this example is shown below. The code does the
following:

  1. Determine the ID of the record to update, by finding the
    value in column A of the active row.
  2. Build the SQL statement that filters the table to just the
    record of interest
  3. Create an ADO connection, and open a recordset based on the
    SQL statement
  4. Loop through each column in the row, writing the values to
    the record. Because the headings in row 1 of the worksheet match
    the field names in the table, we can loop through the headings
    to identify which field to modify.
  5. Close the recordset and clean up references
Sub AlterOneRecord()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim lngRow As Long
   Dim lngID As Long
   Dim j As Long
   Dim sSQL As String

   'determine the ID of the current record and define the SQL statement
   lngRow = ActiveCell.Row
   lngID = Cells(lngRow, 1).Value

   sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID

   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

   With cnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=sSQL, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

   'Load contents of modified record from Excel to Access.
   'do not load the ID again.
   For j = 2 To 7
      rst(Cells(1, j).Value) = Cells(lngRow, j).Value
   Next j
   rst.Update

   ' Close the connection
   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

Note the cursor type in this example. Instead of
adOpenForwardOnly or adDynamic, which we used in the samples where
we were writing whole records to the database, this code uses
adOpenKeyset.

<<Previous:
Part 5

Next: Part 7>>