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,084,733
Messages
5,379,498
Members
401,607
Latest member
Zemexi

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top