Consulting

Results 1 to 8 of 8

Thread: Solved: Early Binding ADODB from Excel

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location

    Solved: Early Binding ADODB from Excel

    I build Excel apps that I distribute across a very large company.
    Becuase I can't be sure of the end-user's OS or version of Office, I almost always use Late Binding, so that I don't have to set a reference in the project for a specific version of a library.

    This means that I've gotten very good at Late Binding, but I have little experience with Early Binding.

    My latest code uses ADODB to query an Access DB. ADODB Late Binds quite nicely, but when I remove the reference, I lose the constants, such as adOpenStatic. I see that I have two options: switch to Early Binding and add a reference to the project, or keep Late Binding, and use literal integers instead of constants.

    What happens if a project contains a reference to a NEWER version of ADODB? Does it run the older version or just error out?
    This might be a silly question, but for obvious reasons I am unable to test this on my own machine.

    Advice please.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    or just add

    [vba]

    #Const EarlyBound = False 'if using early binding

    #If Not EarlyBound Then
    Const adOpenForwardOnly As Long = 0
    Const adLockReadOnly As Long = 1
    Const adCmdText As Long = 1
    Const adInteger As Long = 3
    Const adVarChar As Long = 200
    Const adLongVarChar As Long = 203
    Const adVarWChar As Long = 202
    Const adLongVarWChar As Long = 203
    'etc.
    #End If
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location
    XLD- thanks for the response.

    Forgive my ignorance, but I'm not sure I understand that code.
    Where would I put it? Currently, all of the ADO routines are in a private module... put it in the Declarations section of that module? If so, why not just set private constants to contain that stuff, why make it dependent on "#Const EarlyBound = False"?

    Where does that 'EarlyBound' constant come from? With the hundreds of references you could set, how does it know that you are referring to the binding of a specific library?

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    The idea Dr.K is to have the option to run it early bound or late bound. You could do what you say, or you could use conditional compliation on the constants, together with something like this

    [vba]

    #If EarlyBound Then
    Dim oConn As ADODB.Connection
    Dim oRS As ADODB.Recordset

    Set oConn = New ADODB.Connection
    oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & DBPath
    Set oRS = New ADODB.Recordset
    #Else
    Dim oConn As Object
    Dim oRS As Object

    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & DBPath
    Set oRS = CreateObject("ADODB.Recordset")
    #End If
    [/vba]
    Last edited by Bob Phillips; 11-20-2007 at 07:48 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    To answer your other question, if you have a reference to ADO2.1 and the machine in use has ADO2.7 for example, then you should have no problems. If your project has a reference to 2.7 and the machine only had 2.1 then all sorts of errors would crop up.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    I had this problem with Excel 10 vs 11. Until I got everyone upgraded I created the file path that Excel 10 resides in, then copied the exe to my HD. Then I always bound to 10 even though I was working from 11. Solved the problem.

    The problem with the conditional compilation solution provided by El Xid is that you cannot programattically change the compiler constant. So someone has to know that the reference is broken, remove it, and change the compiler constant. (Although it works great for development to keep intellisense available until you switch to late bound.)

    You could use a multifile solution, a launcher that detects if the dll is there, and launches the appropriate version of the file (late or early bound). But to be honest it seems like the most reasonable course of action is to define the constants yourself and go latebound.
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Oorang,

    What do you mean that you cannot programmatically change the directive?

    If you mean during a run, I would have to ask why would you?

    If you mean as a batch process, then you can. I always develope early bound, and I have a procedure that goes through all of my modules and changes the Early_Bound flag to False, so production code is late bound.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location
    Thanks for all the input guys, its been illuminating.

    I like the idea of developing with early-binding, and then switching to Late and removing references before release. Sounds like the best of both worlds.

    For the current issue, I've decided to go with pure late bound, with my own private consts to replace the ADODB library consts. Here is an example:


    [vba]Option Explicit
    Option Private Module

    Private Const AccessDBPath As String = "\\REMOVED
    Private Const AccessDBConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & AccessDBPath

    'ADODB Constants (needed due to Late Binding)
    Private Const adUseClient = 3
    Private Const adOpenStatic = 3
    Private Const adLockReadOnly = 1
    '

    Sub PRForm_RefreshDept()

    Dim strDept As String
    Dim conDB As Object
    Dim rstDB As Object

    'If AccessDB is NOT available, then exit sub
    If Dir(AccessDBPath) = Empty Then Exit Sub

    'If DB is avail, repopulate options from DB

    'Set up Objects, open DB connection

    Set c Set rstDB = CreateObject("ADODB.Recordset")
    rstDB.CursorLocation = adUseClient

    conDB.Open AccessDBConStr
    With ActiveSheet.ComboBox1
    'store current value and clear out ComboBox
    strDept = .Value
    .Value = Empty
    .Clear

    'Re-populate ComboBox with entries from DB list
    rstDB.Open "SELECT Department FROM Departments ORDER BY Department;" _
    , conDB, adOpenStatic, adLockReadOnly[/vba]

    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •