MS Query Won't Allow Calculation

[ExcelChampion]

I added an MS Query statement to VBA to query data from an Access database. After it performs the query, its should calculate a worksheet with formulas that calculate based on data from the query. The issue is that the sheet will not calculate (calculation is set to Manual.) If I remove the Query code, the worksheet calculates. Put the code back and it won't calculate. I've tried looping the calculation multiple times, I've moved the calculation to elsewhere in the code, I've tried looping through all of the code multiple times, all to no avail. Now here's the weird thing: The macro runs based on a worksheet_change event in a third worksheet. If I trigger that event twice using the same parameters, it calculates. If I change the parameters, it won't calculate.

Has anyone else come across this or is this just one of those things?

Thanks.

        With ActiveWorkbook.Connections("Query from MS Access Database").ODBCConnection
            .BackgroundQuery = True
            .CommandText = Array( _
            "SELECT tbl_OHLCV.DATE, tbl_OHLCV.OPEN, tbl_OHLCV.HIGH, tbl_OHLCV.LOW, tbl_OHLCV.CLOSE, tbl_OHLCV.VOLUME, tbl_OHLCV." _
            , "ADJ_CLOSE, tbl_OHLCV.STOCK_SYMBOL" & Chr(13) & "" & Chr(10) & "FROM `" & ThisWorkbook.Path & "\Stock History.accdb`.tbl_OHLCV tbl_" _
            , "OHLCV" & Chr(13) & "" & Chr(10) & "WHERE (tbl_OHLCV.DATE>={ts '" & Format(Now() - 400, "yyyy-mm-dd hh:mm:ss") & "'}) AND (tbl_OHLCV.STOCK_SYMBOL='" & Sheets("Charts").Range("B2") & "')" & Chr(13) & "" & Chr(10) & "ORDER BY tbl_OHLCV.D" _
            , "ATE DESC")
            .CommandType = xlCmdSql
            .Connection = Array(Array( _
            "ODBC;DSN=MS Access Database;DBQ=" & ThisWorkbook.Path & "\Stock History.accdb;DefaultDir=" & ThisWorkbook.Path & "") _
            , Array(";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
            .RefreshOnFileOpen = False
            .SavePassword = False
            .SourceConnectionFile = ""
            .SourceDataFile = ""
            .ServerCredentialsMethod = xlCredentialsMethodIntegrated
            .AlwaysUseConnectionFile = False
        End With
        With ActiveWorkbook.Connections("Query from MS Access Database")
            .Name = "Query from MS Access Database"
            .Description = ""
        End With
        ActiveWorkbook.Connections("Query from MS Access Database").Refresh
    
    Sheets("Metrics").Calculate

 

[RoryA]

I suggest you set Backgroundquery to False since you need the query to finish refreshing before you run the Calculate command.

 

[ExcelChampion]

Good eye! Thought I had set that to false, but I guess not. That did the trick.

Thanks!