Запрос SQL для файла, доступного только для чтения Excel, не использует актуальную версию файла

Question

Я использую ADODB (в Excel) для выполнения запроса SQL на два файла Excel: файл A (.xlsb) и файл B (.xlsm). Этот запрос выполняется из файла B. Код запускается и выполняет первый запрос для файла A довольно легко! Однако, когда дело доходит до выполнения запроса к файлу B (т.е. к самому себе), если этот файл доступен только для чтения, запрос возвращает 0x80040E07 (-2147219793), Несоответствие типов данных в ошибке выражения критерия. Однако, если файл B открывается с разрешением на чтение и запись, запрос проходит легко. Как тот факт, что файл доступен только для чтения, может вызвать ошибку несоответствия данных?

Итак, вот мой минимальный рабочий пример. Сначала в разделе "Ссылки" моего проекта я добавил библиотеку Microsoft ActiveX Data Objects 2.8.

Вот строки подключения:

Файл А:

DBconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & sourcefile & ";" & _
            "Extended Properties=""Excel 12.0;HDR=Yes;Empty Text Mode=EmptyAsNull"";"

Файл Б:

SourceFile_Self = ThisWorkbook.FullName
Connect_Self = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & SourceFile_Self & ";" & _
               "Extended Properties=""Excel 12.0;HDR=Yes;Empty Text Mode=EmptyAsNull"";"

Открытие обоих соединений работает нормально, вот когда я пытаюсь получить запрос на выполнение файла B, в котором появляются ошибки:

Файл А:

Dim objconnection as ADODB.Connection
Set objconnection = new ADODB.Connection
objconnection.CursorLocation = adUseClient
objconnection.Open (DBconnect)


Dim objRecordset As New ADODB.Recordset

szSQL = "SELECT [Equivalent bore diameter] " & _
        "FROM " & SourceSheet & _
        " WHERE [Shaft dia] = " & MotorShaftDia & ";"

Set objRecordset = objconnection.Execute(szSQL)

Файл Б:

Set objselfConnection = New ADODB.Connection
objselfConnection.CursorLocation = adUseClient
objselfConnection.Open (Connect_Self)


Dim objRecordSetSheetMetal As ADODB.Recordset
Set objRecordSetSheetMetal = New ADODB.Recordset

SQLQuery = "SELECT [" & SeekedColumnTitle & "] as PartNumber, SUM([Total net area (SQ FT)]) as TotalQTY, [Standard Material Description], [OPER], [Sundries], [Unit], [To be ordered (if selected)] as OrderedBOM " & _
            "FROM [" & CabinetWorksheetName & "$] " & _
            "WHERE [Selected] ='X' " & _
            "GROUP BY [" & SeekedColumnTitle & "], [Standard Material Description], [OPER],[Sundries], [Unit], [To be ordered (if selected)];"


Set objRecordSetSheetMetal = objselfConnection.Execute(SQLQuery) '<--- That's where my problem is happening

Я не пытался преобразовать свой код в синтаксис Recordset.Open, чтобы проверить, сохраняется ли моя проблема, потому что я подозреваю, что это больше связано с тем, как ADOBD выполняет запрос к файлу, чем с какой-то арифметической ошибкой.

Я знаю, что в этом примере много чего не так, но, пожалуйста, помните основную тему здесь...

РЕДАКТИРОВАТЬ: Во всяком случае, я попытался сосредоточиться на устранении неполадок лучших практик! Я пытался удалить детали до тех пор, пока они не сработали, а затем добавил их обратно, пока не получилось. Наконец, я обнаружил, что проблема, кажется, здесь:

SUM([Total net area (SQ FT)]) as TotalQTY

Это поле рассчитывается в моем файле (его формула, которая дает его значение). Значение этого поля "#N/A" [тип adVarWChar] в файле только для чтения, а в файле для чтения и записи - 28,866 [тип adDouble]. Вы не можете сделать сумму строк символов! Поле представляет собой формулу, которая принимает 2 значения (высоту и ширину) и умножает их. Похоже, что эти два параметра, поступающие с другой страницы, не обновляются, что приводит к ошибке "# N / A".

Это действительно похоже на то, что таблица, запрашиваемая ADODB, не самая последняя (кажется, что она ссылается на набор данных, каким он был до открытия файла). Я попытался сохранить файл (используя "Сохранить как"), затем снова открыл этот файл как доступный только для чтения и запрос прошел (потому что поля теперь оцениваются как двойные). Но если я изменю одно из значений, это обновленное значение не будет отображаться в результатах, что подтверждает мою гипотезу о том, что ADODB выполняет запрос к последней сохраненной версии файла (не текущей / отображаемой).