Late Binding - exporting data from excel to Access

[smartpat19]

HI everyone,

My code is below. I want to have a button that will allow someone to add a record to access without having to open up the VBA editor to select the reference library.

[COLOR=#333333]Private Sub CommandButton2_Click()MsgBox ("MAKE SURE THE ENTIRE INPUT TAB IS CORRECT")[/COLOR]

Application.ScreenUpdating = False


Dim db As database
Dim rs As DAO.Recordset


Set db = OpenDatabase("J:\samart\Projects\model output 2.0\discussion doc\Prelim data tape.accdb")
Set rs = db.OpenRecordset("Prelim List", dbOpenTable)


rs.AddNew
rs.Fields("Deal Name") = Sheets("import").Range("A2").Value
rs.Fields("Scenario") = Sheets("import").Range("B2").Value
rs.Fields("Deal Type") = Sheets("import").Range("C2").Value
rs.Fields("Shelf") = Sheets("import").Range("D2").Value




rs.Update


rs.Close
db.Close

Also if you know an easy way to create my own error message instead of the scary debug one (just incase someone freaks out in the office), that would be helpful as well.

ThanKS! Excel and access 2013

Pat

 

[smartpat19]

Is there a way to kill the VBA error box to something that says "there seems to be a problem, contact Pat" and it would just stop the macro?

 

[MUKESHY12390]

add this line at beginning

On Error goto ExitDoor

and second line at end of the procedure..

ExitDoor:


MsgBox Err.Number & " " & Err.Description & " -----Call Batman"

 

[RoryA]

This appears to be the same question as this one: http://www.mrexcel.com/forum/excel-...ing-sending-record-excel-access-database.html

As that one was posted first, I'm locking this one.