Load the contents of a worksheet to a database 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 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:

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

DataWright Information Services

Consulting and Resources for Excel and Access

Load the contents of a worksheet to a database table