Using SQL in an Excel workbooks exhibits strange behavior until the devise is rebooted

I have a macro that runs a a number of different SQL statements. I use the same macro each day. The 1st step is I update the Pending worksheet. The 2nd step is to identify new contracts. New contracts are identified by a SQL statement that compares the Pending worksheet to the History worksheet. Anything on the Pending worksheet that is not on the History worksheet is a new contract and is displayed on the New Contract worksheet.

The problem I’m having is that the 2nd steps takes a couple of minutes. When I look at the New Contracts worksheet, there are the same results as yesterday.

What I found is that I save the macro and then reboot the desktop computer. Once the reboot is finished, I open the macro and complete the 2nd step. I get the results I expect.

One thing I noticed is that when I close the macro, Excel is trying to open a copy of the macro. I close that macro and reboot and I’m good to go.

I don’t understand why (1) I get the same results and (2) why is Excel opening another copy of the macro. I’ve developed macro for years and have not seen this behavior before.

Below is the code we use to open a connection and the SQL statement. This is similar to other macros within this process and in other macros and we do not have the same issue with any other SQL or macro.

Any suggestions or ideas to prevent the reboot each day would be greatly appreciated. Thanks in advance for taking the time to look at this question…….

Set WBMain = ActiveWorkbook
    
dBConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WBMain.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    
dBcn.Open dBConn
    
 vsql = ""
 vsql = "select P.* from [Pending$] P " _
        & "where P.[REPAIR_ID] IN " _
        & "(select DISTINCT P.[REPAIR_ID] from [Pending$] P " _
        & "Left Join [History$] H on P.[REPAIR_ID] = H.[REPAIR_ID] " _
        & "where H.[REPAIR_ID] IS NULL and P.[REPAIR_ID] IS NOT NULL)"

dbRS.Open vsql, dBcn

Dim i As Long
For i = 0 To dbRS.Fields.Count - 1
    DoEvents
    Cells(1, i + 1).Value2 = dbRS.Fields(i).Name
Next i

Range("A2").CopyFromRecordset dbRS
    
dbRS.Close

You need to sign in to view this answers

Related Post