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.
To create a new field using ADOX you need to do 4 things:
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
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:
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
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:
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