Adding records to an access table via excel

[Jomalley2004]

I am looking to send information to an access database using some vba and population 5 fields in a table. I am aware the I can always upload an excel spreadsheet into access but am trying to push trhe new records into access from excel rather than pulling. I would like for this code to rest in the excel module and have the ability to be executed with the push of a control button. Any help would be appreciated.

 

[bat17]

Here is some code from Access help. You will need to set a reference in Excel to the Microsoft DAO Object Libary.

Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strFirstName As String
    Dim strLastName As String

    Set dbsNorthwind = OpenDatabase("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset)

    ' Get data from the user.
    strFirstName = Trim(InputBox( _
        "Enter first name:"))
    strLastName = Trim(InputBox( _
        "Enter last name:"))

    ' Proceed only if the user actually entered something

' for both the first and last names.
    If strFirstName <> "" And strLastName <> "" Then

        ' Call the function that adds the record.
        AddName rstEmployees, strFirstName, strLastName

        ' Show the newly added data.
        With rstEmployees
            Debug.Print "New record: " & !FirstName & _
                " " & !LastName
            ' Delete new record because this is a demonstration.
            .Delete
        End With

    Else
        Debug.Print _
            "You must input a string for first and last name!"

End If

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Function AddName(rstTemp As Recordset, _
    strFirst As String, strLast As String)

    ' Adds a new record to a Recordset using the data passed
    ' by the calling procedure. The new record is then made
    ' the current record.
    With rstTemp
        .AddNew
        !FirstName = strFirst
        !LastName = strLast
        .Update
        .Bookmark = .LastModified
    End With

End Function

HTH

Peter