MS Query Won't Allow Calculation

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
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.

Code:
        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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I suggest you set Backgroundquery to False since you need the query to finish refreshing before you run the Calculate command.
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
Good eye! Thought I had set that to false, but I guess not. That did the trick.

Thanks!
 

Forum statistics

Threads
1,136,868
Messages
5,678,226
Members
419,754
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top