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

[smartpat19]

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]

Perhaps you should be looking at late-binding rather than early-binding?

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

 

[smartpat19]

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]

You wouldn't need to.

Can you post your code?

 

[smartpat19]

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.

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]

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]

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:

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:

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]

 

[smartpat19]

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]

Dim acApp as Object
Set acApp = CreateObject("Access.Application")

You can then do whatever you need to do with access.

'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]

I'd stick with DAO rather than automating Access personally:

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)