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