Type Mismatch in Macro that sends excel data to access.

CBRRider

New Member
Joined
Jan 27, 2012
Messages
3
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.


Code:
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

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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

New Member
Joined
Jan 27, 2012
Messages
3
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

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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
 

Forum statistics

Threads
1,086,234
Messages
5,388,613
Members
402,127
Latest member
BiscuitButter

Some videos you may like

This Week's Hot Topics

Top