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

Download the
.mdb sample file (68,765 bytes)
Download the .accdb sample file (71,432 bytes)
If you can’t create the database,
download this one (8558
bytes)

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:

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.
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:

Determine the ID of the record to update, by finding the
value in column A of the active row.
Build the SQL statement that filters the table to just the
record of interest
Create an ADO connection, and open a recordset based on the
SQL statement
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.
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>>

DataWright Information Services

Consulting and Resources for Excel and Access

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