asked on

Code crashes on "Win 7 - Office 2010" but works on "Win XP - Office 2003"

Hi Experts!

I have this piece of code i'm usign for a while now.
Since upgrading to Win7 and Office 2010 this codes allways lets excel crash.
Any suggestions on what is should change?

Regards,
24C

Here's the code:

    Range("A1").Select
    vStartRange = "A1"
    vStopRange = Range("F1").End(xlDown).Offset(0, 8).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    Set rnActReg = Range(vStartRange & ":" & vStopRange)
    iRows = rnActReg.Rows.Count
    imaxID = 0
    For iCounter = 1 To iRows
            vrArtCode = rnActReg(iCounter, 6).Value
            vrKlrCode = rnActReg(iCounter, 7).Value
            Application.StatusBar = vrArtCode & "_" & vrKlrCode

            varSQLcmd = "SELECT "
            varSQLcmd = varSQLcmd & "`productlist`.`ID`, "
            varSQLcmd = varSQLcmd & "`productlist`.`active`, "
            varSQLcmd = varSQLcmd & "`productlist`.`categorie`, "
            varSQLcmd = varSQLcmd & "`productlist`.`artcode`, "
            varSQLcmd = varSQLcmd & "`productlist`.`kleurcode`, "
            varSQLcmd = varSQLcmd & "`productlist`.`artomschr`, "
            varSQLcmd = varSQLcmd & "`productlist`.`artomschr2`, "
            varSQLcmd = varSQLcmd & "`productlist`.`kleuromschr`, "
            varSQLcmd = varSQLcmd & "`productlist`.`artdim`, "
            varSQLcmd = varSQLcmd & "`productlist`.`prijs`, "
            varSQLcmd = varSQLcmd & "`productlist`.`levartcode`, "
            varSQLcmd = varSQLcmd & "`productlist`.`levnaam`, "
            varSQLcmd = varSQLcmd & "`leveranciers`.`Naamzaak` "
            varSQLcmd = varSQLcmd & "FROM "
            varSQLcmd = varSQLcmd & "`productlist` "
            varSQLcmd = varSQLcmd & "Left Join `leveranciers` ON `productlist`.`levnaam` = `leveranciers`.`Code` "
            varSQLcmd = varSQLcmd & "WHERE artcode = '" & vrArtCode & "' AND kleurcode = '" & vrKlrCode & "' "
           
            SQLajudyCONNECT
            If rs.RecordCount > 0 Then
                    For i = 1 To rs.RecordCount
                            
                            If imaxID < rs.Fields("ID").Value Then imaxID = rs.Fields("ID").Value
                            
                            rnActReg(iCounter, 1).Value = rs.Fields("levnaam").Value
                            rnActReg(iCounter, 2).Value = rs.Fields("naamzaak").Value
                            rnActReg(iCounter, 3).Value = rs.Fields("levartcode").Value
                            rnActReg(iCounter, 4).Value = rs.Fields("active").Value
                            rnActReg(iCounter, 5).Value = rs.Fields("categorie").Value
                            
                            
                            rnActReg(iCounter, 8).Value = rs.Fields("artomschr").Value & " - " & rs.Fields("artomschr2").Value & " - " & rs.Fields("kleuromschr").Value
                            rnActReg(iCounter, 9).Value = rs.Fields("prijs").Value
                    Next i

            End If
    Next
Application.StatusBar = False

Open in new window

GrahamSkan

It would help to know where it fails, and what the error is.

Excel frequently just issues a very general error message and doesn't enter break mode, but you could try stepping through the code to find the relevant line.

24Carat

ASKER

Hi,

Sorry for this late reply.

It is excel 2010 that crashes entirely. (program not responding)

i think it may be the ODBC connector which is being overly used

For iCounter = 1 To iRows (iRows usually is around 7000)
varSQLcmd = "blablabla"
SQLajudyCONNECT (this is my connection function to connect to mysql database)
...

ASKER CERTIFIED SOLUTION

24Carat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

24Carat

ASKER

No other answer provided

Code crashes on &quot;Win 7 - Office 2010&quot; but works on &quot;Win XP - Office 2003&quot;

Code crashes on "Win 7 - Office 2010" but works on "Win XP - Office 2003"