Class not registered error 1

[theSizz]

I read the sdillard post below but there doesn't seem to be any answer and the post was orginally posted 2 years ago. Maybe somebody has some information for me.

I have a Visual Basic problem that generates the following error in a sub-procedure:
Run-time error '-2147221164 (80040154)'
Class not registered

Until recently the sub-procedure use to work on my Windows XP computer running Access 2000. I have a vague hunch that the problem may be related to my recent installation of a Microsoft security update or the recent installation of Windows XP SP2.

I am using:
Microsoft Jet Engine 4.0 Version 4.00.8618.0
Visual Basic for Applications
Microsoft Access 9.0 Object Library
Ole Automation
Microsoft ActiveX Data Objects 2.1 Library

Here is the sub-procedure that produces the error.

Sub CountSales()
Dim r As New Recordset
r.Open "SELECT count(*) FROM sales " & _
"WHERE datevalue(date) Between #" & _
Me.StartDate & "# AND #" & _
Me.EndDate & "#", _
CurrentProject.Connection
Me.Command0.Enabled = r(0)
End Sub

I can’t find anything on Microsoft’s knowledge base that addresses this problem.

Can anyone offer any help?

P.S. I've tried uninstalling SP2 but the problem still persists.

Thank You

 

[PCLewis]

You have to dim r as ADODB.Recordset because Recordset will refer to a DAO recordset and your're using the ADO type Open which expects an ADO recordset.

 

[theSizz]

this sub has worked for the past 2 years with the code exactly as written above.
So I don't understand why I should change it now.
I believe it has something to do with the installation of XP Service Pack 2.

 

[PCLewis]

Does that mean you tried my suggestion and it did not work?

DOes 2 years working mean the code is 100% and something broke or could it mean something was broken and this let the code work?

Do not make the mistake of assuming the former Your code as written looks incorrect to me and when I tested it on my system (let us assume it is a "known good" system) I get an error on the statement

Dim r As New Recordset - Invaliud use of New keyword.

This is as I expect because the New keyword is not valid with respect to a DAO.Recordset. When I remove the NEW keyword, I get the following error:

r.Open "SELECT count... - Method or Data Member not Found

Which I also expect because your the DAO Recordset does not have an Open method.

If you add the ADODB. in your dim statement it will work. Your code was working before when it was ambiguous.

 

[theSizz]

Thanks PCLewis
I did't mean to be flippant. I tried your suggestion and now I receive the following error:

Run-time error 91
Object variable or with block variable not set.

Here's the code as it appears now. Is this the same code that you wrote? Thank you very much for responding.



Sub CountSales()
Dim r As ADODB.Recordset

r.Open "SELECT count(*) FROM sales " & _
"WHERE datevalue(date) Between #" & _
Me.StartDate & "# AND #" & _
Me.EndDate & "#", _
CurrentProject.Connection
Me.Command0.Enabled = r(0)
End Sub

 

[PCLewis]

The problem you have now is that you took the New keyword away.

Dim r as New ADODB.RecordSet

or (for the purist)

Dim r as ADODB.RecordSet
Set r = New ADODB.RecordSet

or (for late binding where you don't need a reference to ADODB)

Dim r as Object
Set r = CreateObject("ADODB.RecordSet")

Any of these would work.

 

[theSizz]

Hi PC
Here's the way the code looks now and I still get the
'class not registered' error'

Sub CountSales()

Dim r As ADODB.Recordset
Set r = New ADODB.Recordset

r.Open "SELECT count(*) FROM sales " & _
"WHERE datevalue(date) Between #" & _
Me.StartDate & "# AND #" & _
Me.EndDate & "#", _
CurrentProject.Connection
Me.Command0.Enabled = r(0)
End Sub

Any other suggestions would be appreciated. I've posted this on 3 other forums and have recevied no responses. I appreciate your time and help.

 

[PCLewis]

Dude, why won't you tell me where the error is?

Usually it will be on a particular line in your code.

Can you run the following line of code in your immediate window and paste the results here for me?

for x=1 to application.References.Count :debug.Print application.References(x).Name ,application.References(x).IsBroken, application.References(x).Major & "." &  application.References(x).Minor , application.References(x).FullPath   : next

 

[theSizz]

Hi PC,
Well I bit the bullet and did a clean installation of Windows XP. This fixed the problem, no more error message.

Here's what I think happened. When I installed XP SP2 it installed Microsoft Data Access Components.
When you run MDAC setup it installs new data access DLLs and also updates existing system DLLs on the system. If one or more of these DLLs is in use by an application, it can lead to an unsuccessful MDAC setup. All of the office 2000 applications use MDAC. When I installed SP2 I had the Word application open so I think this corrupted the files.

I ran your code in the immediate window and no glitches.
Thank you very much for your responses and support.

 

[PCLewis]

Yep, you never mentioned the MDAC setup in the original post and having done a number od SP2 installs I could nto fathom how that could be the root cause. If the Class not registered error was soming up on the ADODB.Recordset part of the Dim line then I would have suggested checking your Jet install because there is very little else that can cause that kind of problem.

At least you've sorted it now