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