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

Copyright © 1997-2010 All Rights Reserved.