#excel #vba
#excel #vba
Вопрос:
У меня есть внешний запрос данных в Excel, подключенный к базе данных Microsoft Access, которую я обновляю один раз в день для заполнения таблицы в Excel, и я хочу точно измерить, сколько времени требуется для обновления запроса / подключения.
Я полагаю, что я близок к решению для этого с использованием VBA на рабочем листе и выбора TableUpdate там? Но, похоже, не получается заставить что-либо действительно работать с этим.
К вашему сведению, я делаю это, поскольку с некоторыми недавними обновлениями Excel на пару моих запросов стало уходить вдвое больше времени, и поэтому я хочу вернуться к старым Excels, а затем измерить, сколько времени требуется для обновления, и поскольку это занимает до 15 минут, я не могу сидеть и смотреть на это и записывать время, но я нажму обновить, сделаю что-нибудь еще в течение 20 минут и узнаю, когда обновление закончится.
Комментарии:
1. Я полагаю, что я близок к решению этой проблемы с использованием VBA на рабочем листе и выбора TableUpdate там — пожалуйста, опубликуйте код, который вы внедрили в настоящее время, и сообщите нам, где происходит сбой.
2. Если у вас есть запрос, для возврата результатов которого требуется более 10 секунд (после подключения), либо запрос плохо написан, выбирает слишком много столбцов из слишком большого количества таблиц, соединения не выполняются с помощью первичных / внешних ключей, ключи составные и нечисловые, в схеме отсутствуют индексы, либо комбинация вышеперечисленного.
3. @ScottHoltzman Я сделал только это: Private Sub Worksheet_TableUpdate (изначально целевой как TableObject) Range(«A1»).Value = Now () End Sub
4. @MathieuGuindon Ну, это довольно масштабный запрос, обрабатывающий некоторые очень большие наборы данных в Access (множественные объединения по крайней мере для трех больших наборов данных). И доступ очень ограничен, поэтому я не уверен, что смогу сделать гораздо больше по оптимизации «запроса» в Access.
5. Выполняются ли они через> 10 минут в Access ? Я хочу сказать, что вам следует искать способы оптимизации вашей схемы для выполнения запросов, а не способы документирования того, насколько все плохо на самом деле. Реляционные базы данных буквально созданы для этого, и у них это очень хорошо получается. Доступ действительно имеет некоторые ограничения, но если ваша схема включает составные строковые ключи, частичные и / или условные соединения, и вы выбираете из представления, которое выбирает из представления, которое выбирает из представления, проблема не в доступе.
Ответ №1:
Вы можете создать «пользовательскую» таблицу временных запросов, используя потопление классов и событий, вот так.
Класс под названием clsTimedQueryTable со следующим кодом
Option Explicit
Private WithEvents qtTimed As QueryTable
Private tmStart As Date
Private tmEnd As Date
Public Property Get RefreshTimeTaken() As Variant
RefreshTimeTaken = DateDiff("m", tmStart, tmEnd)
Debug.Print RefreshTimeTaken
End Property
Public Sub INIT(qtToTime As QueryTable)
Set qtTimed = qtToTime
End Sub
Private Sub qtTimed_AfterRefresh(ByVal Success As Boolean)
tmEnd = Now
Module1.TimeTaken = RefreshTimeTaken
Debug.Print "Ended : " amp; tmEnd
End Sub
Private Sub qtTimed_BeforeRefresh(Cancel As Boolean)
tmStart = Now
Debug.Print "Started : " amp; tmStart
End Sub
Тогда в обычном модуле есть следующее
Private clsQueryTable As clsTimedQueryTable
Public TimeTaken As Double
Private Sub SetUp()
Set clsQueryTable = New clsTimedQueryTable
clsQueryTable.INIT ActiveSheet.ListObjects(1).QueryTable
End Sub
Обновление таблицы теперь будет обрабатывать временные интервалы и передавать результат обратно в TimeTaken. Нет необходимости делать так, как я должен вводить переменную, это также из свойства класса.
Я бы также добавил функцию .RefreshTable в класс, чтобы управлять всем этим из класса, а не устанавливать класс, обновлять правой кнопкой мыши и т.д.
Решать вам.
Комментарии:
1. Спасибо за это, но это очень сложно. Я ожидал чего-то намного более простого, учитывая, что доступна функция TableUpdate?
2. Где находится функция tableupdate? Есть ли у него возможность определять время начала и окончания или только когда он обновляется?