[FAQ's: OD] How do I transfer data between Excel and Access?

[RobDog888]

You can perform the transfer many ways. Using Access VBA, ActiveX Data Objects and Excel's Object Model, etc. I'm not going to cover using other databases in this thread, just Access. For MS SQL Server, see this FAQ Thread or this FAQ Thread.

You can do the transfer from the Excel point of view or also from the Access point of view. See this FAQ Thread for the Access examples.


Excel 2003 VBA Code Example:

Option Explicit
'Add a reference to MS ActiveX Data Objects 2.x Library
Private Sub Command1_Click()
    Dim oRs As ADODB.Recordset
    Dim oCnn As ADODB.Connection
    Dim i As Integer
    'Connect to your Access db
    Set oCnn = New ADODB.Connection
    oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RobDog888.mdb;User Id=admin;Password=;"
    oCnn.Open
    'Create your recordset
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText
    'Add to your current workbook and add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        Workbooks("Book1").Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    Workbooks("Book1").Sheets(1).Range("1:1").Font.Bold = True
    Workbooks("Book1").Sheets(1).Cells(2, 1).CopyFromRecordset oRs
    'Clean up ADO Objects
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
End Sub