Excel 2007 to 2010 Issue

[GQue]

Well, I have an issue with some code that works perfectly fine in Office 2007, but since I upgraded to Office 2010, the code now fails. Oddly, it fails when calling a Sub in my code and not at a specific area in the code.

Error messge:

The object invoked has disconnected from its clients
​

Now I read several blogs that talk about Option Explicit being required (it is in my code).

Only thing I can point out is the sub being called has an ADO DB Connection in it and it is the only location in the code that calls a DB connection.

Has anyone else had this issue? I can't understand why it works in 2007, but not in 2010.

Help pls! =)

 

[vbaNewby]

Have you added the correct references to the 2010 version? I think it is Microsoft ActiveX Data Object 2.x Library

 

[GQue]

Yes, the reference is in there... That would have caused it to bomb in 2007 as well.

Also, I neglected to say that after the error message, when I click on Debug, Excel crashes. So my thought process is that this is an Excel bug and nothing code wise that causes the issue, but figured to put it out there.

Everywhere I look, there is nothing remotely close to my issue. Most of the issues I saw where related to 2000, 2003, or 2007.

I am pretty efficient and good at correcting my own issues, but 2010 is BRAND SPANKING NEW to me.

Appreciate your help and hopefully can get an answer to my issue.

 

[vbaNewby]

Yes, the reference is in there... That would have caused it to bomb in 2007 as well.

Also, I neglected to say that after the error message, when I click on Debug, Excel crashes. So my thought process is that this is an Excel bug and nothing code wise that causes the issue, but figured to put it out there.

Everywhere I look, there is nothing remotely close to my issue. Most of the issues I saw where related to 2000, 2003, or 2007.

I am pretty efficient and good at correcting my own issues, but 2010 is BRAND SPANKING NEW to me.

Appreciate your help and hopefully can get an answer to my issue.

Looks like this might be a bug in 2010. Other people are having the exact same problems.

http://social.technet.microsoft.com/Forums/en-US/excel/thread/601a39f4-9e23-40e8-b811-12f39f450771/

 

[GQue]

Thanks again for your reply...

That post you directed me to appears to me to be about data connections to ODBC sources. That is not my issue.

My issue is that the code flows very smoothly importing the data from file exports from another system, then when it calls a sub that verifies the users listed in the data imported, it bombs with that error I specified.

And it doesn't bomb when trying to connect to the db and run the query, it bombs when I attempt to CALL THE SUB.

I put a STOP at the call in the sub, and it works fine up to that point, and when I try to step through the code at the call, it gives me the error before even going to the called sub.

I have other called sub in my code and they all work fine. The only difference I see is that the other calls are doing field clean up in the sheets, and the proplem sub is connecting to a SQL server, running a query and then using the results, cleaning up rows / columns.

But if the code in that SUB was bombing, I could figure it out, but it bombs on the Call. That is what is boogling my mind...

 

[vbaNewby]

Thanks again for your reply...

That post you directed me to appears to me to be about data connections to ODBC sources. That is not my issue.

My issue is that the code flows very smoothly importing the data from file exports from another system, then when it calls a sub that verifies the users listed in the data imported, it bombs with that error I specified.

And it doesn't bomb when trying to connect to the db and run the query, it bombs when I attempt to CALL THE SUB.

I put a STOP at the call in the sub, and it works fine up to that point, and when I try to step through the code at the call, it gives me the error before even going to the called sub.

I have other called sub in my code and they all work fine. The only difference I see is that the other calls are doing field clean up in the sheets, and the proplem sub is connecting to a SQL server, running a query and then using the results, cleaning up rows / columns.

But if the code in that SUB was bombing, I could figure it out, but it bombs on the Call. That is what is boogling my mind...

Hummm, interesting. Are you sending an ODBC argument on your call to the sub? Or is it just a simple call without any arguments?

 

[GQue]

Call with no arguments...

Tried

Call ClearAcctManager

and

ClearAcctManager

 

[vbaNewby]

Call with no arguments...

Tried

Call ClearAcctManager

and

ClearAcctManager

Perhaps show us the code so I can try making a call to it?

 

[GQue]

If Sheet1.cmdOptions.Caption = "Load Data" Then
    txtStatus.Visible = True
    frmMain.Repaint
    StartDataLoad
    ClearAcctManagers <<<--- CRASHES HERE!!
    ActiveWorkbook.SaveAs FileName:= _
        "S:\Projection Report_" & _
        Format(Now, "MMDDYY") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.Close
End If

Sub ClearAcctManagers()
Dim a As Double, eRow As Double
Dim dbConn As ADODB.Connection, myConn As String, myRS As ADODB.Recordset
Sheet2.Select
Range("A2").Select
Selection.End(xlDown).Select
eRow = Selection.Row
 
For a = 2 To eRow
    Set dbConn = New ADODB.Connection
        myConn = "PROVIDER=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;"
        myConn = myConn & "DATA SOURCE=SERVERXX;INITIAL CATALOG=MyTable;"
    dbConn.Open myConn
 
    Set myRS = New ADODB.Recordset
    With myRS
        .ActiveConnection = dbConn
        .Open "select * from client_gen"
    End With
    Do Until myRS.EOF
            If myRS.Fields(0).Value <> "MyClient" Then
                Cells(a, 5) = ""
            End If
        myRS.MoveNext
    Loop
    myRS.Close
    dbConn.Close
Next a
Range("a2").Select
Sheet8.Select
Range("A2").Select
Selection.End(xlDown).Select
eRow = Selection.Row
 
For a = 2 To eRow
    Set dbConn = New ADODB.Connection
        myConn = "PROVIDER=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;"
        myConn = myConn & "DATA SOURCE=SERVERXX;INITIAL CATALOG=MyTable;"
    dbConn.Open myConn
 
    Set myRS = New ADODB.Recordset
    With myRS
        .ActiveConnection = dbConn
        .Open "select * from client_gen"
    End With
    Do Until myRS.EOF
            If myRS.Fields(0).Value <> "MyClient" And Cells(a, 7) = "CL" Then
                Cells(a, 9) = ""
            End If
        myRS.MoveNext
    Loop
    myRS.Close
    dbConn.Close
Next a
Range("a2").Select
End Sub

It doesn't even reach the CallAcctManagers Sub, it crashes at the call...

 

[vbaNewby]

My guess is that it is crashing here:

Dim dbConn As ADODB.Connection, myConn As String, myRS As ADODB.Recordset


One more question, did you setup your ODBC connection on your local machine in the control panel?
</pre>