Move data from Excel to Access

Q: How can I move data from an Excel spreadsheet to an Access table?

A. If the column names in the spreadsheet (Sheet1) correspond to the field names in the table (tblKings), the following VB programme would do the trick Presume that you want to transfer data from C:\Kings.xls to C:\Kings.mdb.

Start by setting a reference to the DAO library in Project|References. Then create a form with two DAO data controls and one command button, cmdTransfer. Set the properties of the two data controls as follows

   DataControl1  DataControl2
 Name  datExcel  datAccess
 Connect  Excel 8.0 x)  Access
 DatabaseName  C:\Kings.xls  C:\Kings.mdb
 RecordSource  Sheet1$  tblKings

x) or whatever version of Excel is available in your machine

Finally, add the following code in the command button's Click event

Private Sub Command1_Click()
  Dim fldAny As Field

  Do Until datExcel.Recordset.EOF 'Traverse the whole Excel "recordset"
    datAccess.Recordset.AddNew    'Open a new empty record in the Access recordset

    'Traverse all the columns in the Excel "recordset"
    For Each fldAny In datExcel.Recordset.Fields

      'Copy the value to the corresponding field in the Access recordset
      datAccess.Recordset(fldAny.Name) = datExcel.Recordset(fldAny.Name)
    Next fldAny


    datAccess.Recordset.Update   'Update the Access recordsett
    datExcel.Recordset.MoveNext  'Go to the next row in the Excel recordset
  Loop

  MsgBox "Transfer completed"    'Inform the user
End Sub