Repost Excel Crashes on Workbook close!!

[James Wellington]

I have been experiencing a strange problem when closing one of my workbooks
which connects to the SQL server via ado. After the workbooks with code is
closed if another workbook is open and I click on another workbook, excel
will crash. If however I click on outlook or another office application,
bring it to focus and then go back to excel its fine. I have checked to
make
sure all object are being released. Any ideas... James--------------

Make sure your code has some DoEvents in (2 or 3 sometimes) before it ends
to allow Excel to clear the message queue. Also make sure you do not
reference any forms, or if you do make sure to set everything to nothing.

I have had similar probs, eventually I think it ended up being an implicit
reference to a function in a form (should have been in a module anyway).

cheers
Simon


--------------Added a couple of DoEvents and still having the same problem.
There are no forms involved just querying the SQL Server using ADO and
running it through some formulas and closing the workbook. When closing the
workbook if I have excel save the workbook this problem goes away but if I
set thisworkbook.close SaveChanges:=False excel will crash after I activate
another workbook.Help is greatly appreciated!!James

 

[Simon Murphy]

Are you setting all variables to nothing then adding a couple of doevents?

 

[Simon Murphy]

did you close all connections properly?

 

[James Wellington]

Simon,
Yes, all variables are set to nothing. I have put DoEvents after the
queries and in the beforeclose event.

James

 

[James Wellington]

I believe so. On the workbook open event I have it call mDataAccess and my
other procedures dealing with the recordsets, the recordsets are closed
after use. The connections are set to nothing on the workbook close event.
I have attached what I feel are the modules in question...

mDataAccess

Option Explicit
' **************************************************************
' Module Constant Declarations Follow
' **************************************************************
Private Const msMODULE As String = "mDataAccess"

' **************************************************************
' Module Variable Declarations Follow
' **************************************************************
Public CMPW_Connect As ADODB.Connection
Public BPCS_Connect As ADODB.Connection

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: Creates a pooled connection to a SQL Server database.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ConnectToDatabase()

Const sSOURCE As String = "ConnectToDatabase"

Dim lAttemptCMPW As Long
Dim lAttemptBPCS As Long
Dim sConnectCMPW As String
Dim sConnectBPCS As String

On Error GoTo Errorhandler

' Create the connection string to CMPW database
sConnectCMPW = "Provider=SQLOLEDB;" & _
"Data Source=" & gsDataSource & ";" & _
"Initial Catalog=" & gsCMPW_Database & ";" & _
"Integrated Security=SSPI"

' Create the connection string to SQL Server database
sConnectBPCS = "Provider=SQLOLEDB;" & _
"Data Source=" & gsDataSource & ";" & _
"Initial Catalog=" & gsBPCS_Database & ";" & _
"Integrated Security=SSPI"


Set CMPW_Connect = New ADODB.Connection
CMPW_Connect.ConnectionString = sConnectCMPW
CMPW_Connect.Open

Set BPCS_Connect = New ADODB.Connection
BPCS_Connect.ConnectionString = sConnectBPCS
BPCS_Connect.Open

' Close connection to enable connection pooling.
CMPW_Connect.Close
BPCS_Connect.Close
DoEvents
Exit Sub
ErrorExit:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
ConnectToDatabase of Module MDataAccess", vbCritical, "Error!"
Set BPCS_Connect = Nothing
Set CMPW_Connect = Nothing
Exit Sub
Errorhandler:
' We will try to make the connection three times before bailing out.
If lAttemptCMPW < 2 And CMPW_Connect.Errors.Count > 0 Then
If CMPW_Connect.Errors(0).NativeError = 17 Then
lAttemptCMPW = lAttemptCMPW + 1
Resume
End If
End If
If lAttemptBPCS < 2 And BPCS_Connect.Errors.Count > 0 Then
If BPCS_Connect.Errors(0).NativeError = 17 Then
lAttemptBPCS = lAttemptBPCS + 1
Resume
End If
End If
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
Application.IgnoreRemoteRequests = False
Stop
Resume
Else
Resume ErrorExit
End If
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DoEvents
' Return application settings
With Application
.Visible = True
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

' Terminate Database connection
Set CMPW_Connect = Nothing
Set BPCS_Connect = Nothing

' Don't save just close
ThisWorkbook.Close savechanges:=False '***Note: saving the workbook fixs
this problem
End Sub

 

[David Welch]

perhaps the problem is here

' Don't save just close
ThisWorkbook.Close savechanges:=False '***Note: saving the workbook fixs

this will cause the close macro to be run twice. It doesn't prevent the
save message box from appearing anyway, might want to set the saved
property for that.

 

[James W.]

Thank you, Thank you , Thank you! Can't believe I over looked that. All my
problems are solved. Thanks again.