G
Guest
I have an Access Visual Basic problem that generates the following error in a
sub-procedure:
Microsoft Visual Basic
Run-time error '-2147221164 (80040154)':
Class not registered
when I reach the line (see the entire sub-procedure below):
rstTowns.Open "TownList", CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
The unregistered class is CurrentProject.Connection.
The problem appears when running the sub-procedure on some computers, but
not others. Until recently, it used to work on my Windows XP computer
running Access 2003. I have a vague hunch that the problem may be related to
my recent installation of a Microsoft security update or Windows XP SP2, but
I could be wrong. I do know that the sub-procedure runs fine on at least one
computer running Windows 2000 running Access 2002, and another using Windows
XP and Access 2002. (I believe that neither user has been terribly diligent
in running Windows Update.)
The sub-procedure comes almost straight out of chapter 12 in the Microsoft
Press book, "Access 2002 Visual Basic for Applications Step by Step." The
purpose of the sub-routine is to:
1. Read multiple rows that have been highlighted in a form's List Box;
2. Open the TownList table
3. Change the value of a yes/no field to Yes in those rows in the table
that correspond to the highlighted rows in the List Box.
I am using:
Microsoft Jet Engine 4.0 Version 4.00.8618.0
Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
(Note: I've tried replacing the ActiveX Data Objects 2.8 Library with older
versions all the way down to the 2.1 Library, but this makes no difference.
The Sandbox property of Jet Engine 4.0 in the Windows Registry is set to 2.
Here is the whole sub-procedure:
Private Sub UpdateTownList()
' Limit report to clinics in selected towns.
Dim cnn As Connection
Dim rstTowns As New ADODB.Recordset
Dim varPosition As Variant
' Turn off system warnings
DoCmd.SetWarnings False
' Check to see if any towns have been selected.
If SelectTowns.ItemsSelected.Count > 0 Then
'DoCmd.Hourglass True
' Run Update Query to reset the IncludeInReport field to NO for all
rows.
DoCmd.OpenQuery "Update TownList IncludeInReport to No"
' Open a recordset object in the current database.
rstTowns.Open "TownList", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
' Begin a transaction before changing data.
CurrentProject.Connection.BeginTrans
' Set the Index property to search on the TownState field of the
TownList table.
rstTowns.Index = "TownState"
' Loop through each selected town in the list.
For Each varPosition In SelectTowns.ItemsSelected
' Find the record in the TownList table
rstTowns.Seek SelectTowns.ItemData(varPosition)
' Something goes in here to set up a query based on selected
towns.
rstTowns!IncludeInReport = -1
rstTowns.Update
Next varPosition
' Save all changes for this transaction.
' CurrentProject.Connection.CommitTrans
DoCmd.Hourglass False
Else
DoCmd.OpenQuery "Update TownList IncludeInReport to Yes"
End If
' Turn system warnings back on
DoCmd.SetWarnings True
End Sub
sub-procedure:
Microsoft Visual Basic
Run-time error '-2147221164 (80040154)':
Class not registered
when I reach the line (see the entire sub-procedure below):
rstTowns.Open "TownList", CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
The unregistered class is CurrentProject.Connection.
The problem appears when running the sub-procedure on some computers, but
not others. Until recently, it used to work on my Windows XP computer
running Access 2003. I have a vague hunch that the problem may be related to
my recent installation of a Microsoft security update or Windows XP SP2, but
I could be wrong. I do know that the sub-procedure runs fine on at least one
computer running Windows 2000 running Access 2002, and another using Windows
XP and Access 2002. (I believe that neither user has been terribly diligent
in running Windows Update.)
The sub-procedure comes almost straight out of chapter 12 in the Microsoft
Press book, "Access 2002 Visual Basic for Applications Step by Step." The
purpose of the sub-routine is to:
1. Read multiple rows that have been highlighted in a form's List Box;
2. Open the TownList table
3. Change the value of a yes/no field to Yes in those rows in the table
that correspond to the highlighted rows in the List Box.
I am using:
Microsoft Jet Engine 4.0 Version 4.00.8618.0
Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
(Note: I've tried replacing the ActiveX Data Objects 2.8 Library with older
versions all the way down to the 2.1 Library, but this makes no difference.
The Sandbox property of Jet Engine 4.0 in the Windows Registry is set to 2.
Here is the whole sub-procedure:
Private Sub UpdateTownList()
' Limit report to clinics in selected towns.
Dim cnn As Connection
Dim rstTowns As New ADODB.Recordset
Dim varPosition As Variant
' Turn off system warnings
DoCmd.SetWarnings False
' Check to see if any towns have been selected.
If SelectTowns.ItemsSelected.Count > 0 Then
'DoCmd.Hourglass True
' Run Update Query to reset the IncludeInReport field to NO for all
rows.
DoCmd.OpenQuery "Update TownList IncludeInReport to No"
' Open a recordset object in the current database.
rstTowns.Open "TownList", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
' Begin a transaction before changing data.
CurrentProject.Connection.BeginTrans
' Set the Index property to search on the TownState field of the
TownList table.
rstTowns.Index = "TownState"
' Loop through each selected town in the list.
For Each varPosition In SelectTowns.ItemsSelected
' Find the record in the TownList table
rstTowns.Seek SelectTowns.ItemData(varPosition)
' Something goes in here to set up a query based on selected
towns.
rstTowns!IncludeInReport = -1
rstTowns.Update
Next varPosition
' Save all changes for this transaction.
' CurrentProject.Connection.CommitTrans
DoCmd.Hourglass False
Else
DoCmd.OpenQuery "Update TownList IncludeInReport to Yes"
End If
' Turn system warnings back on
DoCmd.SetWarnings True
End Sub