Type Mismatch in Macro that sends excel data to access.

[CBRRider]

I have an type mismatch error that occurs on the fifth line of the code. Set rs = db.OpenRecordset("Header_New", dbOpenTable). I don't know enough about OpenRecordset command but from what I gathered from Microsoft the syntax is correct, but I may be wrong. Is there a specific reference that I need to use to make this work or is the code off? Just FYI I am moving more than one cell and the excel data will be populating a preexisting db.

Sub AccessHeader()
Dim db As Database, rs As Recordset, r As Long
Sheets("Header Access Trnsfr").Select
    Set db = OpenDatabase("F:\TestQUOTE.mdb")
    Set rs = db.OpenRecordset("Header_New", dbOpenTable)
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
        With rs
            .AddNew 
            .Fields("Name") = Range("A" & r).Value
            .Update 
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Thanks Rob.

 

[boblarson]

Is F:\TestQUOTE.mdb a completely separate database from the one you have the code you are running? Or is this code in Excel and you want to send to Access?

 

[CBRRider]

This code is in the excel workbook. The ultimate goal would be to have this macro run automatically before the excel sheet is closed.

Thanks

 

[boblarson]

Well, your main problem most likely can be solved by changing this line:

Dim db As Database, rs As Recordset, r As Long

to this

Dim db As Database, rs As DAO.Recordset, r As Long

 

[CBRRider]

That was it! Thank you very much Bob!

Robert