Early Binding - Sending a record from Excel to an access database

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
79
My VBA is working great my record is recorded correctly in access when I run the macro, my only issue is that every time I open a new workbook I have to reactivate the VBA Reference library : "Microsoft Office 15.0 Access database engine Object"

I will be having other co-workers (not-VBA users) running the same macro and I need it to work smoothly with them. I been reading about early binding and I'm thinking its what i need. Can i have the macro activate the reference library before I send the new record to the access database?

How can i do this?

Excel 2013
Access 2013

Thank you!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Perhaps you should be looking at late-binding rather than early-binding?

With late-binding you don't need the reference.
 

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
79
I'm not sure how to refer to the "Microsoft Office 15.0 Access database engine Object" reference library, how would I do that with late binding?

Thanks.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
You wouldn't need to.:)

Can you post your code?
 

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
79
Thank you! i've never worked with binding and I know that people would freak out if they had to open their vba editor to run this macro.

Code:
Private Sub CommandButton2_Click()MsgBox ("MAKE SURE THE ENTIRE INPUT TAB IS CORRECT")


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


Application.ScreenUpdating = True


MsgBox ("Record added to access")
 Workbooks.Open "J:\samart\Projects\model output 2.0\discussion doc\TEMPLATE - Prelim Discussion Presentation.xlsx"
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(Filefilter:="excel files (*.xlsx), *.xlsx")
End Sub
 

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
79
Have you gotten a chance to look at my code?

I know I posted it Friday afternoon, not the most ideal time. haha.

Thanks!
 

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
I'm not sure how to refer to the "Microsoft Office 15.0 Access database engine Object" reference library, how would I do that with late binding?

Thanks.
The idea behind late-binding your variables is to avoid a need to reference a library at all. In the simplest of terms, if you declare a variable as an object, you are late-binding it.

The benefit to late-binding is that it allows you to not set a reference to a library at all. If there’s no reference, there’s nothing to break and you're problem is solved.

There are three steps involved in producing late-bound code.


1. Declare your object variables as Object, rather than as, say, Word.Document or Outlook.Application

2. Instead of using the New operator to instantiate the objects, you must use CreateObject with the relevant class name.

3. Either declare any constants you use from the target library, or use their literal values instead (the former I believe is better practice).


Example of Early-Bound:
Rich (BB code):
Sub SendOLMail_EarlyBound()
' declare variables for the Application and Mailitem objects
Dim oAPP                       As Outlook.Application
Dim oItem                      As Outlook.MailItem

' instantiate the Application
Set oAPP = New Outlook.Application

' create a new email
Set oItem = oAPP.CreateItem(olMailItem)

' set basic properties and display the email
With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
EndWith

EndSub
Example of Late Bound:
Rich (BB code):
Sub SendOLMail_LateBound()
Dim oAPP                       AsObject
Dim oItem                      AsObject
' need to declare this constant as it has no meaning without
' the reference set to the Outlook library
   Const olMailItem               AsLong = 0

' instantiate the Application - cannot use New without a reference
' so we must use CreateObject
Set oAPP = CreateObject("Outlook.Application")

' #######################################
' NOTE: THE REST OF THE CODE IS IDENTICAL
' #######################################

' create a new email
Set oItem = oAPP.CreateItem(olMailItem)

' set basic properties and display the email
With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
EndWith

EndSub
I cannot provide you specific examples as I've never interacted with Access before. However, I hope this information explains the concept. Hopefully someone with more experience can provide you with assistance specific to your project.

Edit: This explanation taken from [HERE]
 
Last edited:

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
79
Thank you for the example, it really helps to show how to late bind.

Can anyone show me how to connect to access using late binding?
 

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
Code:
Dim acApp as Object
Set acApp = CreateObject("Access.Application")
You can then do whatever you need to do with access.

Code:
'open an access database

acApp.OpenCurrentDatabase("some_database.accdb")


' copy data from excel to access database
' https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

acApp.DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
Hopefully this helps you out as a starting point.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,323
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I'd stick with DAO rather than automating Access personally:

Rich (BB code):
Dim db As Object
Dim rs As Object
Const dbOpenTable As Long = 1

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

Forum statistics

Threads
1,081,691
Messages
5,360,631
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top