Excel VBA: Late binding reference

I'm trying to write some code for an add-in in excel, which grabs some data from an SQL Server. The code itself is working flawlessly, but somehow something got corrupted.

It seems that the code will work fine a few times and then all of a sudden trigger an excel-crash. After a long time I've determined that it has something to do with the references, seeing as if upon crash I change the reference 'Microsoft ActiveX Data Objects 2.8 Library' to something else, and then back again, the add-in will work again.

Seeing as rebuilding the add-in doesn't work, I'm beginning to explore the option of late-binding. I just can't seem to understand how to do it.

Private Sub RetrieveToWorksheet(SQL As String, WriteTo As Range, Optional WriteColumnNames As Boolean = True)

If GetStatus = "True" Then
MsgBox ("Database is currently being updated. Please try again later.")
Exit Sub
End If

Application.ScreenUpdating = False

Dim Connection As ADODB.Connection
Dim RecordSet As ADODB.RecordSet
Dim Field As ADODB.Field
Dim RowOffset As Long
Dim ColumnOffset As Long

     On Error GoTo Finalize
Err.Clear
Set Connection = New ADODB.Connection
Connection.ConnectionTimeout = 300
Connection.CommandTimeout = 300
Connection.ConnectionString = "Provider=sqloledb;Data Source=vdd1xl0001;Initial Catalog=SRDK;User Id=SRDK_user;Password=password;Connect Timeout=300"
Connection.Mode = adModeShareDenyNone
Connection.Open
Set RecordSet = New ADODB.RecordSet
RecordSet.CursorLocation = adUseServer
RecordSet.Open SQL, Connection, ADODB.CursorTypeEnum.adOpenForwardOnly
RowOffset = 0
ColumnOffset = 0

If WriteColumnNames = True Then
For Each Field In RecordSet.Fields
    WriteTo.Cells(1, 1).Offset(RowOffset, ColumnOffset).Value = Field.Name
    ColumnOffset = ColumnOffset + 1
Next
ColumnOffset = 0
RowOffset = 1
End If

WriteTo.Cells(1, 1).Offset(RowOffset, ColumnOffset).CopyFromRecordset RecordSet

Finalize:

    If Not RecordSet Is Nothing Then
        If Not RecordSet.State = ADODB.ObjectStateEnum.adStateClosed Then RecordSet.Close
        Set RecordSet = Nothing
    End If
    If Not Connection Is Nothing Then
        If Not Connection.State = ADODB.ObjectStateEnum.adStateClosed Then Connection.Close
        Set Connection = Nothing
    End If
    If Err.Number <> 0 Then Err.Raise Err.Number, Err.Source, Err.Description
End Sub

Long story short: I just want the add-in to automatically add the reference 'Microsoft ActiveX Data Objects 2.8 Library'.

All help is greatly appreciated!

In answer to your question about late binding, this involves replacing the line of code

Dim Connection As ADODB.Connection

with

Dim Connection As object

and replacing

Set Connection = New ADODB.Connection

with

Set Connection = GetObject(, "ADODB.Connection")

And similarly for the other objects from that library.

Now, I am not sure if this will fix the actual issue that you are having. It sounds like there is a bug in the ActiveX library and you are hitting it, although nothing you are doing seems particularly esoteric.