Solved: Late binding

[Kaizer]

I've checked around how to write the late binding code for ADO. However for some reason it gives me the "Application defined or object define error" message at line "ADOrs.Open...".

Can you help fix the code below, pls?

Private Sub Late_Binding()
Dim ADOcn As Object
Dim ADOrs As Object
Dim sFilePath As String
Dim sSQL As String
Dim MyConnect As String
Dim s As String

sFilePath = ActiveWorkbook.FullName
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sFilePath & ";" & _
"Extended Properties=Excel 12.0"

sSQL = "SELECT [2012$].[Period] FROM [2012$]"


Set ADOcn = CreateObject("ADODB.Connection")
Set ADOrs = CreateObject("ADODB.Recordset")

ADOcn.Open MyConnect

ADOrs.Open sSQL, ADOcn, adOpenForwardOnly, adLockOptimistic

Set ADOrs = Nothing
Set ADOcn = Nothing 'Set to NOTHING

End Sub

[Trebor76]

Hi Kaizer,

You need a reference to the latest 'Microsoft ActiveX Data Objects n.n Library' *.dll file from within the Visual Basic Editor. You should include this in as a note in your code as I have done as a reminder.

Regards,

Robert


Option Explicit

Private Sub Late_Binding()

'Requires a reference (Tools > References) to 'Microsoft ActiveX Data Objects n.n Library'

Dim ADOcn As Object
Dim ADOrs As Object
Dim sFilePath As String
Dim sSQL As String
Dim MyConnect As String
Dim s As String

sFilePath = ActiveWorkbook.FullName
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sFilePath & ";" & _
"Extended Properties=Excel 12.0"

sSQL = "SELECT [2012$].[Period] FROM [2012$]"

Set ADOcn = CreateObject("ADODB.Connection")
Set ADOrs = CreateObject("ADODB.Recordset")

ADOcn.Open MyConnect

ADOrs.Open sSQL, ADOcn, adOpenForwardOnly, adLockOptimistic

Set ADOrs = Nothing
Set ADOcn = Nothing 'Set to NOTHING

End Sub

HTH

Robert

[Aflatoon]

If you want to late bind, add

const adopenforwardonly as long = 0
const adlockoptimistic as long = 3
to your code.

[Kaizer]

Trebor76, Aflatoon,

Thanks for your feedback.

My goal is to have a code that will work w/o reference to the ADO library.

Aflatoon,

I've added two lines you suggested to my code and now have a different error message i.e. "Automation error". :cool:

[Aflatoon]

Is that the complete error message, and is that the complete code?

[Kaizer]

Is that the complete error message, and is that the complete code?
The error message "Run-Time error '-2147217908 (80049e0c)': Automation error"

... and the code
Const adopenforwardonly As Long = 0
Const adlockoptimistic As Long = 3
Private Sub Late_Binding()

Dim ADOcn As Object
Dim ADOrs As Object
Dim sFilePath As String
Dim sSQL As String
Dim MyConnect As String
Dim s As String

sFilePath = ActiveWorkbook.FullName
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sFilePath & ";" & _
"Extended Properties=Excel 12.0"

'sSQL = "SELECT [2012$].[Period] FROM [2012$]"


Set ADOcn = CreateObject("ADODB.Connection")
Set ADOrs = CreateObject("ADODB.Recordset")

ADOcn.Open MyConnect
's = ADOcn

ADOrs.Open sSQL, ADOcn, adopenforwardonly, adlockoptimistic

Set ADOrs = Nothing
Set ADOcn = Nothing 'Set to NOTHING

End Sub

[Kenneth Hobs]

I guess this worked with early binding?

I am not sure why you commented out the SQL string.

Try using F8 to step through the code to see what line it errors.

[Kaizer]

I guess this worked with early binding?

I am not sure why you commented out the SQL string.

Try using F8 to step through the code to see what line it errors.

Yes, the reason was in the missing SQL string. I was testing the code with different options and forgot I commented out the SQL string.

Thank you very much. Now it's resolved.

[Aflatoon]

Note also that you should close both the recordset and connection before you set them to nothing, and it is not recommended to use ADO with an open source workbook due to memory leaks.