ADODB .Size Failing in Microsoft ActiveX Data Objects 2.8

[rickincanada]

I have a Macro, written by someone else, in an Excel workbook that I distribute, which is failing on Windows XP machines. This is because VBA is attempting to access Microsoft ActiveX Data Objects 6.1, however as I understand it can't be accessed in Windows XP. I'm wondering if anyone here, without having to dig through the entire Macro, could help suggest some alternative to the snippet below? This appears to be the only element I'm relying on that is not available in Microsoft ActiveX Data Objects 2.8. It would appear that the .Size is what's looking for the 6.1 Library.

Dim cmd As ADODB.Command
...
...
For i = 0 To cmd.Parameters.Count - 1
        If cmd.Parameters.Item(i).Type = adDecimal Then
            cmd.Parameters.Item(i).Precision = cmd.Parameters.Item(i).Size
            cmd.Parameters.Item(i).NumericScale = 4
        End If
    Next i
...
...

Any help you can offer would be greatly appreciated! Thanks in advance.

Rick

 

[Kyle123]

I suspect that you need to use late binding, have a look here: Early binding vs Late binding in Office VBA | msofficefun

 

[rickincanada]

Hi Kyle,

First of all, thanks for your response. I've long suspected that using late binding would be my preferred approach, however I struggle a little to understand exactly this will work. I have a couple questions, can you tell me if I'm understanding this binding stuff correctly?

1. To change to code from above to late binding, would I simple change the Dim cmd As ADODB.Command to Dim cmd as Object and then Set rst = CreateObject("ADODB.Command") and proceed from there?
2. By doing this, how does this avoid users who don't have the same version of Microsoft ActiveX Data Objects available to them as I do from getting an error on workbook open?

Thanks so much for answering these two questions for me. I promise I've out a lot of energy into trying to read and understand other posts on this subject, however these two items are still a little unclear to me.

Rick