class events stop firing with ADO 2.8

[ThankYou_jeff]

Excel add-in has been working for 2 years using ado 2.7; it fails on ado 2.8.
Here's stripped down version that displays message boxes for all events used.
This test works on ado 2.7. It has a reference to "Microsoft ADO Ext.2.7. for
DDL and Security". I load this Add-in programmatically. All events fire as
expected.

Now when i use this same Add-in on a system with ADO 2.8 {in my test case XP
SP2} it begins ok. The FIRST, SECOND and THIRD EVENT occur correctly then the
events no longer fire. For example the right-click does not occur, etc. If i
change the ado reference to "Microsoft ADO Ext.2.8. for DDL and Security". It
works fine... events continue to fire.

I can cheat and have two Add-ins; one with ado 2.7 and one with 2.8. There
has to be a better way. Can you input?



--------------------------------------------------------------------------------

'***EXCEL WORKBOOK OF ADD-IN***
Option Explicit
Dim AppClass As New AppEventClass

Public Sub Workbook_Open()
MsgBox ("Public Sub Workbook_Open() FIRST EVENT")
Set AppClass.App = Application
End Sub

--------------------------------------------------------------------------------

'***AppEventClass - an Excel VBA class module***
Option Explicit
Public WithEvents App As Application

Private Sub app_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
MsgBox ("Private Sub app_SheetBeforeRightClick RIGHT CLICK")
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox ("Private Sub App_SheetActivate(ByVal Sh As Object)")
End Sub

Private Sub App_WorkbookAddinUninstall(ByVal Wb As Workbook)
On Error Resume Next
MsgBox ("Private Sub App_WorkbookAddinUninstall(ByVal Wb As Workbook)")
Call removeMe(Wb.Name)
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
On Error Resume Next
MsgBox ("Private Sub App_WorkbookBeforeSave")
End Sub

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
MsgBox ("Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook) NAME =
" & Wb.Name)
If Wb.Name = Workbooks.Item(1).Name Then Call removeMe(Wb.Name)
End Sub

Private Sub removeMe(removing As String)
MsgBox ("Private Sub removeMe() " & removing)
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox ("Private Sub App_WorkbookOpen(ByVal Wb As Workbook) " & Wb.Name
& " THIRD EVENT")
End Sub

Private Sub Class_Initialize()
MsgBox ("Private Sub Class_Initialize() SECOND EVENT")
End Sub

 

[keepITcool]

Check your references in VBE.

if ANY reference is listed as ISMISSING this may well cause the hiccup.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ThankYou_jeff wrote :

 

[ThankYou_jeff]

Thanks but there are no missing references; other than perhaps the "MS ADO
Ext.2.7 for DLL and Security" which is automatically updated to "MS ADO
Ext.2.8 for DLL and Security" when the Add-in is manually opened in the VB
IDE [Alt-F11]. Once that reference is replaced the Add-in works for ADO 2.8.
Then, of course, the Add-in will not work at all for ADO 2.7 {since "MS ADO
Ext.2.8 for DLL and Security" is truely missing. VB IDE won't automatically
downgrade to 2.7}.

 

[keepITcool]

so.. you do have a referencing problem...

also your use of ADO and ADOX is confusing.
ADO would be Microsoft ActiveX Data Objects 2.8 Library
ADOX would be MS ADO Ext.2.8 for DLL and Security

First you'd need to save the addin when the references
both to ADO and ADOX are in sync, and to the "lowest" possible version.


Perhaps it's an idea to rewrite all adox calls/object
to LATEBOUND, and remove all adox references.
(although this would mean a performance loss when objects are created,

I assume you wouldnt have that many adox calls,most of what you can do
with ADOX you can do with ADO's openschema and generally faster too.


Changing code to "late-binding":

Change all 'typed' adox object declarations to OBJECT

Change lines like
set adoxCAT = new adox.catalog
to
set adoxCAT = createobject("adox.catalog")

Change
all adox constants
to
their numeric values.

HTH


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ThankYou_jeff wrote :

Thanks but there are no missing references; other than perhaps the
"MS ADO Ext.2.7 for DLL and Security" which is automatically updated
to "MS ADO Ext.2.8 for DLL and Security" when the Add-in is manually
opened in the VB IDE [Alt-F11]. Once that reference is replaced the
Add-in works for ADO 2.8. Then, of course, the Add-in will not work
at all for ADO 2.7 {since "MS ADO Ext.2.8 for DLL and Security" is
truely missing. VB IDE won't automatically downgrade to 2.7}.

Check your references in VBE.

if ANY reference is listed as ISMISSING this may well cause the
hiccup.





--
keepITcool


ThankYou_jeff wrote :

 

[ThankYou_jeff]

Right on the money... had references "Microsoft ActiveX Data Objects 2.6
Library" and "Microsoft ADO Ext.2.7 for DLL and Security". Changed to ADO 2.7
and tossed ADOX; now works on both ado 2.7 and 2.8.

so.. you do have a referencing problem...

also your use of ADO and ADOX is confusing.
ADO would be Microsoft ActiveX Data Objects 2.8 Library
ADOX would be MS ADO Ext.2.8 for DLL and Security

First you'd need to save the addin when the references
both to ADO and ADOX are in sync, and to the "lowest" possible version.


Perhaps it's an idea to rewrite all adox calls/object
to LATEBOUND, and remove all adox references.
(although this would mean a performance loss when objects are created,

I assume you wouldnt have that many adox calls,most of what you can do
with ADOX you can do with ADO's openschema and generally faster too.


Changing code to "late-binding":

Change all 'typed' adox object declarations to OBJECT

Change lines like
set adoxCAT = new adox.catalog
to
set adoxCAT = createobject("adox.catalog")

Change
all adox constants
to
their numeric values.

HTH


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ThankYou_jeff wrote :

Thanks but there are no missing references; other than perhaps the
"MS ADO Ext.2.7 for DLL and Security" which is automatically updated
to "MS ADO Ext.2.8 for DLL and Security" when the Add-in is manually
opened in the VB IDE [Alt-F11]. Once that reference is replaced the
Add-in works for ADO 2.8. Then, of course, the Add-in will not work
at all for ADO 2.7 {since "MS ADO Ext.2.8 for DLL and Security" is
truely missing. VB IDE won't automatically downgrade to 2.7}.

Check your references in VBE.

if ANY reference is listed as ISMISSING this may well cause the
hiccup.





--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


ThankYou_jeff wrote :

Excel add-in has been working for 2 years using ado 2.7; it fails
on ado 2.8. Here's stripped down version that displays message
boxes