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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,771
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top