ActiveX cannot create object...ADO recordset 1

[mike777]

I have this code:

Sub miketest()
    Dim cnADO As New ADODB.Connection, rstADO As New ADODB.Recordset, sSQL As String, fldADO As ADODB.Field
    cnADO.Open CurrentProject.Connection
    sSQL = "select count(0) from u_lead_tracking"
    rstADO.Open sSQL, cnADO
    MsgBox rstADO.Fields(0)
    rstADO.Close
    Set rstADO = Nothing
    cnADO.Close
    Set cnADO = Nothing
End Sub

I just put the "CurrentProject.Connection" part in there to see if it would work. It wasn't working with my connection string, either. With "CurrentProject.Connection", it still doesn't work.
In my References, I have "Microsoft ActiveX Data Objects 2.7 Library" selected, and there isn't any apparent problem there.
when I run this code in break mode (or any mode), when it gets to the cdADO.Open line, I get the: Error #429. ActiveX can't create object..
I am clueless at this point. Can you help?!!
Thank you!!
-Mike

 

[CajunCenturion]

You might try the following:

Sub miketest()
Dim rstADO As New ADODB.Recordset, sSQL As String, fldADO As ADODB.Field

rstADO.ActiveConnection = CurrentProject.Connection
sSQL = "select count(0) from u_lead_tracking"
rstADO.Open sSQL
MsgBox rstADO.Fields(0)
rstADO.Close
Set rstADO = Nothing
End Sub


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[mike777]

CajunCenturion-
I tried that and I get the exact same result.
Thanks anyway.
-Mike

 

[VBslammer]

Sounds like you need to reinstall MDAC.

VBSlammer

Unemployed in Houston, Texas

 

[cmmrfrds]

This connection is already Open.
cnADO.Open CurrentProject.Connection
You could.
Set cnADO = CurrentProject.Connection

sSQL = "select count(0) from u_lead_tracking"

rstADO.Open sSQL, cnADO, 3, 3

Check to see if a recordset was returned before trying to reference a field.

if rstADO.EOF Then
debug.print "no records"
exit sub
end if

 

[PeteJohnston]

If you have rstADO defined as

Dim rstADO As New ADODB.Recordset

do you need to create a new instance of the object before you refer to it, or does the

rstADO.Open sSQL, cnADO

do that implicitly?

Would it work without the ActiveX Data Objects stuff?

Sub miketest()
    Dim rst As Recordset, sSQL As String

    sSQL = "select count(0) As C_Field from u_lead_tracking"
    Set rst = CurrentDb.OpenRecordset(sSQL)
    MsgBox rstADO.Fields("C_Field")
    rst.Close
    Set rst = Nothing

End Sub

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[RobCPA]

I wrote the following faq which has worked for most who have tried it: faq705-2882

The faq is geared toward DAO. Do you have DAO checked in the references. I realize that you're using ADO versus DAO, but it may be worth a try.

Rob

 

[mike777]

Hello all.
Hmmm...interesting...I'm inclined to think that the re-installing MDAC is the most promising solution. I'll get back to you on what works.
Many thanks.
-Mike Kemp

 

[cmmrfrds]

I just noticed the count in the select statement.

What is count(0), shouldn't it be count(*)

 

[mike777]

VBSlammer.
That did it!!
Many thanks.
Good luck on your employment!!
-Mike