DataWright Information Services

Consulting and Resources for Excel and Access

Load the contents of a worksheet to a database table

This is Part 2 of 7 in a tutorial on using code to integrate Excel and Access.

In the sample file, Population projections has actual populations by country for 1950 and 2000, with forecasts going out to 2050. By clicking the Load to Access button, you will load all of the records on this sheet to tblPopulation in the Access database that was created in Part 1 of this tutorial.

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.

The process can be broken down to this sequence of steps:

  • Create a connection to the database.
  • Define a recordset based on the table.
  • Loop through the columns and rows, loading data one field at a time into each record. So, for each row, you need to do this:
    • rst.AddNew (Create a new record)
    • Loop through the columns, assigning values to each field in the recordset. In this example the column headings on the worksheet are exactly the same as the field names in the table, so we can loop through those headings to get the list of fields for loading the data.
    • rst.Update (Save the record)
  • Finally, clean up by closing the Recordset and Connection objects, and then setting them to Nothing. If you don't do this, you will end create a memory leak because VBA will keep references to all of the objects unless you do these housekeeping steps.

Here is the code:

Sub PushTableToAccess()
    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst As ADODB.Recordset
    Dim i As Long, j As Long
    Dim Rw As Long

    'go to the correct sheet and determine the # of used rows
    Sheets("Population Projections").Activate
    Rw = Range("A65536").End(xlUp).Row

    'create the connection to the database
    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

    With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Open MyConn
    End With

    'create the recordset
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:="tblPopulation", _
             ActiveConnection:=cnn, _
             CursorType:=adOpenDynamic, _
             LockType:=adLockOptimistic, _
             Options:=adCmdTable

    'Load all records from Excel to Access, 
    'by looping through the rows and columns
    For i = 2 To Rw
       rst.AddNew
       'in this case we are loading 7 fields. Adjust to suit.
       'field headings are in row 1 of the worksheet.
       For j = 1 To 7
          rst(Cells(1, j).Value) = Cells(i, j).Value
       Next j
       rst.Update
    Next i

    ' Clean up
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

<<Previous: Part 1 Next: Part 3>>