Excel VBA create new record in Access Table - 2007 Office

[CookieCoder]

Hi, Good Morning Experts!
I'm trying to create a new record in Access from Excel VBA. I've got a button in the Excel file, that when pushed will put data from the excel sheet into a NEW record in Access. Here's what I've got so far, but it keeps failing on "Set rstTable = oAcc.Recordset.Open("C:\Documents and Settings\ecook\My Documents\SharePoint.mdb")"

Sub cmdPassDownItem_Click()
Dim oAcc As Object
Dim rstTable As Object
 
Set oAcc = CreateObject("Access.Application")
Set rstTable = oAcc.Recordset.Open("C:\Documents and Settings\ecook\My Documents\SharePoint.mdb")
    r = 20 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("Title") = Range("A" & r).Value
            .Fields("Country") = Range("B" & r).Value
 
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub  

[ADezii]

The general idea would be something similar to:

Dim oAcc As Object
Dim rstTable As Object
 
Set oAcc = CreateObject("Access.Application")
 
'Open Database in Microsoft Access window
oAcc.OpenCurrentDatabase "C:\Documents and Settings\ecook\My Documents\SharePoint.mdb", True
 
oAcc.Visible = False
 
'Create a Recordset based on <Table name>
Set rstTable = oAcc.CurrentDb.OpenRecordset("<Table Name>")
 
With rstTable
  .AddNew
    ![Title] = "<Value>"
    ![Country] = "<Value>"
  .Update
End With
 
oAcc.Quit
Set oAcc = Nothing

[CookieCoder]

Thank you very much, this works like a charm!!

[ADezii]

You are quite welcome.