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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,508
Messages
5,414,972
Members
403,558
Latest member
Hardeni

This Week's Hot Topics

Top