В операции должен использоваться обновляемый запрос excel

Обновлено: 07.07.2024

Я в Excel 2010 подключаюсь к нескольким,отдельно доступ к БД 2010 из Excel через подключения к данным сводной таблицы.

обновление всех моих соединений приводит к сбою последнего обновления. Порядок не имеет значения, я вручную обновился в разных порядках, та же ошибка.

однако, если я сохраню и закрою после обновления нескольких, а затем вернусь и обновлю последний, нет вообще проблема.

приводит меня к мысли, что я нажимаю какую-то крышку памяти, которая сбрасывается, когда я сохраняю и закрываю.

могу ли я воссоздать этот эффект через VBA без сохранения/закрытия? есть ли лучшее решение этой проблемы?

запрос не запущен, или не удалось открыть таблицу базы данных.
проблемы с получением данные.
не удалось обновить сводную таблицу, функцию куба или срез с помощью соединения.

Код

Строку Подключения

Попытки Решения

Отключить Фоновое Обновление - уже отключен
отключить автоматическое восстановление (для сохранения памяти)
очистить "отменить стек" (для сохранения память)
'DoEvents' для задержки выполнения кода до завершения каждого обновления, изменение:

решение!

Примечание, у меня есть несколько дополнительных соединений, которые я не хотел обновлять через этот код, и добавил некоторую дополнительную, простую логику, чтобы указать, какие соединения я хотел обновить. Этот код здесь работает, чтобы обновить каждое соединение в вашем книги:

Я не знаю специфики почему это работает, какая часть этого позволяет Excel преодолеть свое самоограничение. Я хотел бы услышать, если кто знает!

Это не полный ответ, а попытка помочь отладки, так что, надеюсь, мы сможем найти решение.

Я считаю, что вы можете решить эту проблему, отладив соединения. Попробуйте заменить код обновления выше (и замену на DoEvents)следующим подразделом. Во-первых, возможно, что отображение диалога между обновлениями исправит проблему (если проблема заключается в одновременном обновлении и т. д.). Во-вторых, каждый раз, когда он работает, тщательно проверьте, что ничего не имеет измененный. Пожалуйста, сообщайте о любых открытиях или информации. Если вы все еще получаете ошибки, шаг через код и доложить строку, которая вызывает ошибку.

дополнительные вопросы вы можете ответить, если вы все еще получаете ошибки:

был ли BackgroundQuery всегда ложным?
была ли заметная задержка между каждым набором диалоговых окон (указывающая, что Excel ждет завершения обновления) или все они появились сразу после последнего один?
какая строка кода вызывает начальную ошибку? Если вы обновите соединения в обратном порядке (раскомментировав строку "шаг -1"), вы получите ошибку при том же соединении?
когда вы говорите, что можете обновить соединения вручную, это через другой макрос или через Data > > Connections > > Refresh?
ошибки, если вы вручную выберите "RefreshAll"?

извините за все вопросы, но вы должны думать о все при отладке неприятных ошибок подключения, как это.

поэтому у меня была аналогичная ошибка, когда я пытался создать скрипт VBA для автоматического обновления книги excel в данный момент времени, и было несколько вещей, которые я сделал в своем скрипте VBA, чтобы заставить это работать. Один из них отключение фонового обновления. Это может быть ваша проблема, и вы можете легко отключить ее, перейдя в свойства подключения и отключив обновление фона.

вот что я сделал в VBA, когда я получал эту ошибку, хотя я скажу, что я не использовал его с БД MS access. У меня была одна книга excel, которую я использовал как "бегун", и она открывала другие книги одну за другой и обновляла их соединения. В основном у меня была переменная для path и extension и поместите имена каждой книги в массив и выполните цикл через массив.

Я объединил путь и расширение, чтобы дать мне полное имя файла, вы увидите это в цикле.

вот как выглядела моя петля :

получить имя соединения есть несколько способов, в том числе, просто пытаюсь понять, что это вручную. Для меня, потому что я хотел сделать это так, чтобы мне не нужно было вручную вводить каждое имя соединения, я использовал неотъемлемый шаблон, который я видел с именами соединений.

в моем случае это был baseNameOfWorkbook & " POS Report"

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

Дайте мне знать, если это сработает.

вы можете использовать VBA для вызова обновлений индивидуально через activeworkbook.объект подключения. См.этот столб переполнения стека для некоторых подсказок по этому методу. Более атомистическое приложение может позволить лучше понять и контролировать. Например, как только у вас есть все шаги на месте, вы можете попробовать вставить DoEvents до решить вопрос.

очистить системную память, вы всегда можете запустить что-то вроде этого:

это очистит стек отмены, в котором находятся все обновления ваших сводных таблиц, что позволит вам отменить их, если вы сделаете это между ссылками, это может помочь вам контролировать использование памяти.

пожалуйста, расценивайте мое предыдущее предложение, поскольку я думал о решении, которое помогло мне в доступе.

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Исходный номер КБ: 328828

Требуются базовые навыки макроса, кодирования и интероперабельности. Эта статья применяется к базе данных Microsoft Access (.mdb и .accdb) и к проекту Microsoft Access (.adp).

В этой статье описываются ошибки, которые могут возникать в Microsoft Access при обновлении данных в запросах и формах. В этой статье также описываются причины ошибок и их устранение.

Операция должна использовать запрос с возможностью обновления.

Этот набор записей не обновляется.

Устранение неполадок, связанных с проблемами, которые могут возникнуть при обновлении данных в запросе

Если запрос основан на трех или нескольких таблицах и имеет отношение "много к одному", нельзя обновлять данные непосредственно в запросе. Вы можете обновить данные в форме или на странице доступа к данным. Это можно сделать на основе запроса, когда свойство формы заданной RecordsetType dynaset(Несогласованные обновления).

Когда запрос является перекрестным запросом, вы не можете обновить данные в запросе.

Если запрос — это SQL microsoft, вы не можете обновить данные в запросе.

Когда запрос вычисляет сумму, среднее значение, количество или другой тип общей суммы значений в поле, нельзя обновлять данные в запросе. Кроме того, нельзя обновить запрос, ссылающийся на поле в строке Обновление для строки из перекрестия, запроса, выбора запроса или подкожи, содержащую итоги или агрегированные функции. Чтобы решить эту проблему, используйте функцию Сводка домена в строке Обновление для запроса обновления. Вы можете ссылаться на поля из перекрестного запроса, выбора запроса или подкожи, которые содержат итоговые или совокупные функции.

Когда запрос является запросом Union, нельзя обновлять данные в запросе.

Когда свойство Unique Values запроса за установлено значение Да, вы не можете обновить данные в запросе. Чтобы решить эту проблему, установите свойство Unique Values запроса на значение No.

Если запрос включает связанную таблицу ODBC без уникального индекса или таблицу Paradox без основного ключа, нельзя обновлять данные в запросе. Чтобы решить эту проблему, добавьте основной ключ или уникальный индекс в связанную таблицу.

Если у вас нет разрешений на обновление данных для запроса или таблицы, вы не можете обновлять данные. Чтобы устранить эту проблему, назначьте разрешения на обновление данных.

Если запрос включает несколько таблиц или один запрос, а таблицы или запросы не соединены линией связи в представлении Design, нельзя обновлять данные в запросе. Чтобы устранить эту проблему, необходимо правильно присоединиться к таблицам, чтобы можно было их обновить.

Если поле, которое требуется обновить, является вычисляемой областью, данные в запросе не обновляются.

Если поле, которое вы пытаетесь обновить, является только для чтения, база данных открыта только для чтения или база данных расположена на диске только для чтения, данные в запросе не обновляются. Чтобы избежать этой проблемы, не открывайте базу данных только для чтения. Если база данных расположена на диске, который только для чтения, удалите атрибут только для чтения с диска или переместите базу данных на диск, который не только для чтения.

Когда поле в записи, которую вы пытаетесь обновить, удаляется или блокируется другим пользователем, вы не можете обновить данные в запросе. Заблокированная запись может быть обновлена, как только запись будет разблокирована.

Если запрос основан на таблицах с отношением один к многим, то типы полей, которые вы не можете изменить, являются следующими:

Присоединитесь к полю со стороны "один".
Поле "много" бокового присоединяться не появляется в таблице данных.
Присоединитесь к полю со стороны "много" после обновления данных с "одной" стороны.
Пустое поле из таблицы на "одной" стороне связи один к многим с внешним присоединиться существует.
Весь уникальный ключ таблицы ODBC — это не выход.

Вы можете разрешить любую из этих проблем, если вы принимаете правильное действие из следующего списка:

Включить каскадные обновления между двумя таблицами.
Добавьте поле присоединиться со стороны "много" к запросу, чтобы можно было добавить новые записи.
Сохраните запись. Вы можете внести изменения в поле "много" бокового присоединяться.
Введите значения в полях из таблицы на стороне "много". Это можно сделать только в том случае, если в соединяемом поле со стороны "один" содержится значение для этой записи.
Выберите все основные поля ключевых таблиц ODBC, чтобы разрешить вставки к ним.

Устранение неполадок, связанных с проблемами, которые могут возникнуть при обновлении данных в форме

Нельзя обновлять данные в форме, если форма основана на процедуре хранения с более чем одной таблицей.

Нельзя обновлять данные в форме, если форма основана на наборе ActiveX объектов данных (ADO). Формы доступа позволяют изменять данные из наборов записей ADO, если набор записей ADO создается с помощью сочетания поставщиков MSDataShape и SQL Server OLEDB.

Исходный номер КБ: 904953

Симптомы

В Microsoft Office Access 2007 или Microsoft Office Access 2003 или в Microsoft Access 2002 нельзя изменять, добавлять или удалять данные в таблицах, связанных с Microsoft Excel книгой.

Кроме того, вы можете испытывать такое поведение, если одно из следующих условий является верным:

Для получения данных из таблиц, связанных с Excel книгой, создается запрос.
Вы создаете форму, которая будет получать доступ к данным из таблиц, связанных с Excel книгой.
Вы используете DAO или ADO для программного обновления таблиц, связанных с Excel книгой.

Операция должна использовать обновленный запрос

Ошибка "3027" не может обновляться. База данных или объект доступны только для чтения.

Операция должна использовать обновленный запрос

Причина

Это ожидаемое поведение возникает, когда все следующие условия верны:

Вы используете Office Access 2007.
Вы установили Microsoft Office 2003 Пакет обновления 2 (SP2) или более поздний пакет услуг или все обновления Access 2003, которые были выпущены после Office 2003 sp2.
Установлено обновление для Access 2002 (KB904018), датированное 18 октября 2005 г.
Установлено приложение access runtime, которое включает Microsoft Office 2003 Пакет обновления 2 (SP2) или более поздний пакет служб, обновления Access 2003, выпущенные после Office 2003 года, или обновление для Access 2002 (KB904018), датированное 18 октября 2005 г. или более поздней версией.

Обходной путь

Чтобы обойти это ожидаемое поведение, используйте один из следующих методов.

Метод 1. Использование Microsoft Excel

Откройте связанную Excel в Microsoft Excel, а затем внести изменения в книгу. После завершения изменений сохраните изменения и закроите книгу.

Метод 2. Использование Office Access 2007, Access 2003 или Access 2002

Импортировать связанную Excel книгу в Access, а затем внести изменения в данные. После завершения изменений экспортировать данные в качестве Excel .xls файла.

Чтобы экспортировать таблицу из Access в Excel, запустите следующий код в Access.

В этом примере кода замените следующие места:

<MyQuery> является местообнамерщиком запроса, который вы запустите с таблицами в базе данных Access. Результат запроса экспортируется в Excel книгу.

<WorkSheets>является держателем для таблицы в Excel, в которую необходимо экспортировать результат. Чтобы запустить этот пример кода, нажмите кнопку CTRL+G, чтобы открыть немедленное окно, введите WorkArounds и нажмите кнопку ENTER.

Дополнительные сведения

Из-за юридических проблем Корпорация Майкрософт отключил функции в Access 2003 и Access 2002, которые позволяет пользователям изменять данные в связанных таблицах, которые указывают на диапазон в Excel книге. Однако при непосредственном внесении изменений в Excel книге изменения отображаются в связанной таблице в Access.

У меня есть запрос ( Query1 ), загруженный в Excel с использованием соединения из Teradata . Обычно, когда я хочу обновить запрос, я должен щелкнуть его правой кнопкой мыши и выбрать «Изменить».

Оказавшись внутри Power Query Editor , я могу открыть Source и отредактировать SQL-скрипт внутри, чтобы обновить запрос.

Вопрос в том, как мне отредактировать этот сценарий SQL , используя VBA ?

При попытке использовать Macro Recorder , я могу только обновить запрос, используя

Попытка изменить CommandText также не удалась. Вот свойства запроса.

2 ответа

Сначала мы получаем формулу запроса, используя:

Причина, по которой мы должны это сделать, заключается в том, что строка формулы содержит некоторую дополнительную информацию о соединении с нашей базой данных, а не только сам SQL-запрос. Это может выглядеть примерно так:

Чтобы обновить запрос, мы устанавливаем ThisWorkbook.Queries.Item("Query1").Formula в новую строку формулы. Также будьте осторожны при обработке двойных кавычек внутри VBA. Вот скрипт, который я использовал:

Во-первых, замените ActiveWorkbook на ThisWorkbook , поскольку вы хотите работать с этой конкретной книгой (той, в которой хранится ваш код VBA), а не с какой-либо книгой, которая оказывается в настоящее время активен .

.Refresh - это метод соединения OLEDB, которое вы хотите изменить: у вас уже есть ссылка на соединение, для которого вы хотите изменить текст команды - все, что вам нужно, это вызвать метод, другой чем .Refresh против этого, то есть попробуй набрать эту точку и посмотреть, что этот объект может предложить.

Вы можете получить текст команды для этого OLEDBConnection объекта через его свойство CommandText - попробуйте ввести его в панель быстрого доступа (Ctrl + G) и посмотрим, если вы получите тот же результат:

начиная с Jet 4, все запросы, имеющие оператор join to a SQL, который суммирует данные, будут не обновляемыми. Вы не используете соединение, но предложение WHERE точно эквивалентно соединению, и, таким образом, оптимизатор запросов Jet обрабатывает его так же, как и соединение.

боюсь, вам не повезло без временной таблицы, хотя, возможно, кто-то с большими знаниями Jet SQL, чем я могу придумать обходной путь.

кстати, возможно, он был обновляем в Jet 3.5 (доступ 97), в целом многие запросы были обновлены, а затем стали не обновляемыми при обновлении до Jet 4.

у меня была аналогичная проблема, когда следующие запросы не работали;

однако использование DLookup решило проблему;

предположим, что вы храните базу данных (файл MS-Access)в папке только для чтения, при запуске приложения соединение не полностью открыто. Следовательно, измените разрешение файла / его содержащее разрешение папки, как в C:\Program files все большинство файлов дисков c были установлены только для чтения таким образом, изменение этого разрешения решает эту проблему.

Я знаю, что мой ответ опоздал на 7 лет, но вот мое предложение в любом случае:

когда Access жалуется на запрос обновления, который включает соединение, просто сохраните запрос с RecordsetType свойство имеет значение Dynaset (Inconsistent Updates) .

иногда это позволяет обновлению работать.

Я бы попытался создать запрос обновления в Access. У меня был запрос на обновление, который я написал сам, как

запрос дал мне эту ошибку вы видите. Это работало на моем SQL Server, но, как и предыдущие ответы, синтаксис обновления доступа не является стандартным синтаксисом. Однако, когда я перестроил его с помощью мастера запросов Access (он использовал синтаксис JOIN), он работал нормально. Обычно я бы просто сделал запрос обновления passthrough для использования синтаксиса non-JET, но одна из таблиц, в которой я был соединение с было локальной таблицей доступа.

это происходит, когда нет уникального ключа MS-ACCESS для обновляемых таблиц. (Независимо от схемы SQL).

при создании ссылок MS-Access на таблицы SQL вам предлагается указать индекс (ключ) во время ссылки. Если это сделано неправильно или вообще не сделано, запрос к связанной таблице не подлежит обновлению

при связывании таблиц SQL в Access убедитесь, что при запросе доступа для индекса (ключа) вы используете именно то, что SQL использует, чтобы избежать проблема(ы), хотя указание любого уникального ключа-это все, что нужно для обновления таблицы.

Если вы не были человеком, который первоначально связал таблицу, удалите связанную таблицу из MS-ACCESS (ссылка только удаляется) и повторно свяжите ее, указав ключ правильно, и все будет работать правильно.

(немного опоздал на вечеринку. )

три способа, которыми я обошел эту проблему в прошлом:

ссылка на текстовое поле в открытой форме
DSum
функции dlookup

У меня была та же проблема.

мое решение состоит в том, чтобы сначала создать таблицу из не обновляемого запроса, а затем выполнить обновление из таблицы в таблицу, и это работает.

Mine не удалось с помощью простого оператора INSERT. Исправлено путем запуска приложения с 'Запуск от имени администратора' открыть.

MS Access-объединение таблиц в запросе обновления. как сделать его обновляемым

открыть запрос в режиме конструктора
нажмите один раз на ссылку ч/б таблиц/вид
в окне" Свойства "измените значение" уникальные записи "на"да"
сохраните запрос как запрос обновления и запустите его.

Я продолжал получать ту же ошибку, пока не сделал соединительное поле уникальным индексом в обеих соединительных таблицах. Только тогда запрос стал обновляемым.

по сути, хотя ваш SQL выглядит вполне разумно, Jet никогда не поддерживал стандартный синтаксис SQL для UPDATE . Вместо этого он использует свой собственный синтаксис (отличный от собственного UPDATE синтаксис), которая составляет очень общества. Часто единственные обходные пути "операция должна использовать обновляемый запрос" очень болезненны. Серьезно подумайте о переходе на более способный продукт SQL.

для некоторых больше деталей о ваших специфических проблемах и некоторых возможные обходные пути, см. обновление запроса на основе итогов запроса не удается.

Я продолжал получать ту же ошибку, но все SQLs выполняются в Access очень хорошо.

и когда я изменил разрешение из AccessFile.

Я даю 'Сетевая Служба' учетная запись полный контроль разрешение, эта учетная запись, если для IIS

когда я получил эту ошибку, это могло быть из-за неправильного синтаксиса обновления, но после того, как я исправил запрос обновления, я снова получил ту же ошибку. поэтому я пошел в ODBC Data Source Administrator и обнаружил, что мое соединение доступно только для чтения. После того, как я сделал соединение read-write и re-connected, он работал нормально.

сегодня в моем MS-Access 2003 с таблицей ODBC, указывающей на SQL Server 2000 с паролем sa, дал мне ту же ошибку.
Я определил первичный ключ таблицы в базе данных SQL Server и проблема исчезла.

решение, чтобы повторно получить файл из источника безопасный и применить настройку Writeability.

для дальнейшего ответа на то, что Друа упомянул в своем ответе.

Я разрабатываю базы данных в Access 2007. Мои пользователи используют среду выполнения access 2007. У них есть разрешения на чтение папки database_Front (front end) и разрешения на чтение/запись в папку database_Back.

при развертывании новой базы данных пользователь не следовал полным инструкциям по копированию передней части на свой компьютер и вместо этого создал ярлык. Запуск Front-end через ярлык создаст условие, при котором запрос не будет обновляться из-за ограничений записи файла.

копирование передней части в папку "Документы" решает проблему.

Да, это усложняет ситуацию, когда пользователи должны получить обновленную версию интерфейса, но, по крайней мере, запрос работает без необходимости прибегать к временным таблицам и тому подобное.

проверьте свою БД (разрешение базы данных) и дайте полное разрешение

перейдите в папку DB - > щелкните правой кнопкой мыши свойства - >безопасность - > изменить - > дать полный контроль & Меню Пуск - >Выполнить - > тип " uac " сделать его вниз (если он высокий)

вы всегда можете написать код в VBA, который обновляется аналогичным образом. У меня тоже была эта проблема, и мой обходной путь делал запрос select со всеми соединениями, у которых были все данные, которые я искал, чтобы иметь возможность обновлять, делая это набором записей и выполняя запрос update повторно как запрос update только таблицы обновления, только поиск критериев, которые вы ищете

Я делаю это только около 60 записей в день, делая это в несколько тысяч может занять много дольше, поскольку запрос выполняется от начала до конца несколько раз, вместо того, чтобы просто выбирать общую группу и вносить изменения. Возможно, Вам понадобится '' вокруг кавычек для tableID, так как это строка, но я уверен, что это сработало для меня.

ответ, данный выше iDevlop, работал для меня. Обратите внимание, что мне не удалось найти свойство RecordsetType в моем запросе обновления. Однако мне удалось найти это свойство, изменив запрос на запрос select, установив это свойство, как отметил iDevlop, а затем изменив запрос на запрос обновления. Это сработало, не нужно временной таблицы.

Я хотел бы, чтобы это был просто комментарий к тому, что опубликовал iDevlop, чтобы он вытекал из его решения, но у меня нет максимума достаточно очков.

Читайте также: