DataWright Information Services

Consulting and Resources for Excel and Access

Create a new field in a table, and update existing records with new data

This is Part 3 of 7 in a tutorial on using code to integrate Excel and Access. In Part 1, you created the database; in Part 2 you populated the table with data from Excel. Here, you will create a new field in the existing table and load data into that field without affecting the other fields.

In the sample file, New Field has the same population data as the first worksheet, with an additional column for Region. By clicking the Insert Field button, you will add the Region field to tblPopulation in the Access database that was created in Part 1 of this tutorial. The field is blank at this stage, but clicking Update Field Data will update the data in the Access table.

Note:

  1. The database name is defined as a constant at the top of the code module. You will need to add this to the top of your code module as well, above the first Sub.
  2. You will need to set a reference to the Microsoft ActiveX Data Objects 2.x Library to run this code. If you use the ADOX method for creating the field, you will also need to reference the ADO Extension Library, as noted in the link above.

Add a new field

Add a Field Using ADOX

To create a new field using ADOX you need to do 4 things:

  1. Create a new ADO connection
  2. Create an ADOX Catalog (database) and make it use the ADO connection that you just created
  3. Set a reference to the table that you want to change
  4. Add column(s) to the table, by appending them to the table's Columns collection. You need to define the data types when you create each field. This page shows which ADOX data types correspond to the Access JET and DDL data types.
  5. Clean up the references
Sub AddNewField_ADOX()

    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim cat As ADOX.Catalog
    Dim col As ADOX.Column
    Dim tbl As ADOX.Table
    Dim sDB_Path As String

    MyConn = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB

    'connect to the database and create a new ADOX Catalog
    'that uses the connection
    Set cnn = New ADODB.Connection
    With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open MyConn
    End With

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = cnn

    'add the new field
    Set tbl = cat.Tables("tblPopulation")
    tbl.Columns.Append "Region", adVarWChar, 60

    'clean up references
    Set cat = Nothing
    Set col = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

Add a Field Using SQL

An alternative to ADOX is to use SQL to modify database and table structures. Part of SQL is the Data Definition Language or DDL, which is used for making structural changes. The sequence of steps is:

  1. Create a new ADO connection
  2. Create a Command object, and make it use the new ADO connection
  3. Execute an SQL statement that adds the new field (in this case). You define the field types and sizes in the SQL statement.
  4. Clean up references.

By clicking Insert Field_SQL you will run the following code and create Region_2, a 30-character text field:

Sub AddNewField_SQL()

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim MyConn

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

    'open the connection
    Set cnn = New ADODB.Connection
    With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open MyConn
    End With

    'create a new Command object, and set its Connection property
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn

    'create the field
    cmd.CommandText = "ALTER TABLE tblPopulation ADD Column Region_2 Char(30)"
    cmd.Execute

    'clean up the references
    Set cmd = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

Update the field data

To update the contents of a field for multiple records, you need to loop through the rows in the worksheet, changing records one at a time. You must use a unique ID as the key field. By clicking Update Field Data you will populate the newly created Region field in the database with the matching data in column C of the spreadsheet. The code is shown below.

The steps involved are as follows:

  1. Go to the correct worksheet and determine how many rows to load
  2. Create an ADO connection
  3. Loop through each record in turn doing the following --
    • Define a recordset filtered on the current record
    • Open the recordset
    • Change the record
    • Update and close the recordset
  4. Close the connection and clean up references
Sub PopulateOneField()
   Dim cnn As ADODB.Connection
   Dim MyConn
   Dim rst As ADODB.Recordset
   Dim i As Long, j As Long
   Dim Rw As Long
   Dim sSQL As String

   Sheets("New Field").Activate
   Rw = Range("A65536").End(xlUp).Row

   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
   'Update one field in each record of the table. First record is in Row 2.
   For i = 2 To Rw
     sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & Cells(i, 1).Value
     rst.Open Source:=sSQL, _
              ActiveConnection:=cnn, _
              CursorType:=adOpenKeyset, _
              LockType:=adLockOptimistic
     rst(Cells(1, 3).Value) = Cells(i, 3).Value
     rst.Update
     rst.Close
   Next i

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

End Sub

<<Previous: Part 2 Next: Part 4>>