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

 

[MUKESHY12390]

dude..what is your issue.?

 

[smartpat19]

This current macro requires the reference library for Access 2013 activated. This is defaulted to not-active.

If I use late binding, I know I don't have to select the reference library everytime.

How do I convert my current macro to using late binding?

 

[Norie]

How about the code not opening the database and using ADO instead of DAO?

 

[smartpat19]

How about the code not opening the database and using ADO instead of DAO?

Can you show me how I would do that? Is it as simple as changing ADO.database?

 

[MUKESHY12390]

Here you go... just change DB path , table name , Field name and Worksheet name according to your need.

Sub AddRecord_ExcelToAccess()
Dim ws As Worksheet
Dim cn As Object, rs As Object, r As Long
Set ws = Worksheets("Sheet1")
Set cn = CreateObject("ADODB.Connection")


cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"  ''''''change db path here


Set rs = CreateObject("ADODB.Recordset")


rs.Open "TableName”, cn, adOpenKeyset, adLockOptimistic, adCmdTable"  ''Change Tablename here


r = 3 ' the start row in the worksheet
Do While Len(ws.Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs


   .AddNew ' create a new record
   ' add values to each field in the record
   .Fields("FieldName1") = ws.Range("A" & r).Value
   .Fields("FieldName2") = ws.Range("B" & r).Value
   .Fields("FieldName3") = ws.Range("C" & r).Value
   ' add more fields if necessary…
   .Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

in case if you are using 2010 or later replace with below line .

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\FolderName\DataBaseName.accdb;" ''''''change db path here

 

[smartpat19]

I'm getting a Run-time error '2709' on rs.Open "Prelim List”, cn, adOpenKeyset, adLockOptimistic, adCmdTable" ''Change Tablename here



I tried it with [Prelim List]. Then it says that a "adOpenKeyset" hasn't been defined.

I know for a fact that its opening access, I just can't get the table open.

 

[Norie]

Try adding this at the top of the code.

Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adCmdTable = 2

 

[smartpat19]

I'm getting this run-time error now: Run time Error '-2147217900 (80040e14)'

 

[smartpat19]

WOW, typo on myside. Disregard, this worked perfectly!