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.

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]

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?

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]

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.

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.

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.

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]

...