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.
The process can be broken down to this sequence of 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