Запрос не выполняется или не удается открыть таблицу базы данных excel

Обновлено: 30.06.2024

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

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

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

Я в 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 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

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

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

Симптомы

Причина

Эта проблема возникает при использовании совокупных функций в запросе и не заданной свойству "Выходные все поля" запроса нет.

Решение

Чтобы устранить эту проблему, установите свойство "Выходные все поля" запроса "Нет", а затем запустите запрос Access, использующий совокупную функцию. Для этого выполните следующие действия:

Откройте базу данных Access, которая содержит проблемный запрос.

В окне База данных щелкните Запросы в разделе Объекты.

В Access 2007 щелкните группу Запросы в левой области навигации.

Щелкните правой кнопкой мыши запрос, который необходимо изменить, а затем щелкните Просмотр дизайна.

В меню Просмотр нажмите кнопку Свойства.

В Access 2007 щелкните вкладку Design и нажмите лист свойств в группе Tools.

В диалоговом окне Свойства запросов установите значение свойства запроса "Выходные все поля" на значение No.

В access 2007 щелкните вкладку Сохраненная процедура в диалоговом окне Свойства. Убедитесь, что параметр Выход всех столбцов не выбран.

В меню Запрос нажмите кнопку Выполнить.

В Access 2007 щелкните вкладку Design и нажмите кнопку Выполнить в группе Tools.

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

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

Для этого в Access 2003 и в более ранних версиях Access выполните следующие действия:

  1. Запуск доступа.
  2. В окне Базы данных щелкните Параметры в меню Tools.
  3. В диалоговом окне Параметры нажмите кнопку "Вывод всех полей" на вкладке Таблицы и Запросы.
  4. Чтобы выполнить поиск абонентской группы для пользователя в поле Абонентская группа (телефонный контекст), нажмите кнопку Обзор.

В Access 2007 выполните следующие действия:

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

Действия по воспроизведению проблемы в Access 2003

Откройте пример базы данных Northwind.mdb.

Запустите запрос Subtotals заказа в примерной базе данных Northwind.mdb, чтобы убедиться, что запрос Order Subtotals успешно выполняется. Для этого выполните следующие действия:

  1. В окне База данных щелкните Запросы в разделе Объекты.
  2. В правой области щелкните правой кнопкой мыши запрос Subtotals заказа и нажмите кнопку Открыть.
  3. В меню Файл нажмите кнопку Закрыть.

Обратите внимание, что в запросе Subtotals заказа используется совокупная функцияSum() .

Откройте запрос Subtotals заказа в представлении Design. Для этого выполните следующие действия:

  1. В окне База данных щелкните Запросы в разделе Объекты.
  2. В правой области щелкните правой кнопкой мыши запрос Порядок subtotals, а затем нажмите Просмотр дизайна.

Щелкните в любом месте в окне Запрос за пределами сетки запросов и за пределами списков полей.

В меню Просмотр нажмите кнопку Свойства.

В диалоговом окне Свойства запросов установите значение свойства запроса "Да" для всех полей вывода.

Закройте диалоговое окно Свойства запроса.

В меню Запрос нажмите кнопку Выполнить.

На работе встретился с такой задачей «Надо сделать отчет, который брал бы данные из MySQL и закидывал его в таблицу Excel».
Ну вот я сейчас и опишу процесс конекта.
Есть:
1. Сервер MySQL
2. База данных на MySQL
3. Microsoft Excel 2010

Начнем:
1. Самое первое что нужно сделать, это установить MySQL Connector, актуальную версию можно скачать здесь
2. Нужно создать источник данных, идем вот сюда Панель управления — Администрирование — Источники данных (ODBC)
2.1. Вкладка Пользовательский DSN — Добавить — MySQL ODBC 5.1 Driver (должен появиться после манипуляций с п.1)
2.2. Откроется окно настройки источника, там в поле Data Source Name нужно написать имя сервера MySQL, порт оставляем стандартным (если вы при установке сервера не указали иной), User — имя пользователя, скорее всего root, Password — пароль, Database — тут можно указать базу, к которой подключаться, если все правильно на начальных этапах, то в выпадающем списке уже появятся существующие БД на сервере.
2.3. Ок. Источник данных создан.
3. Теперь необходимо создать строку подключения.
3.1. В любом месте компьютера создайте текстовый файл и переименуйте его в тип файла .udl
3.2. В открывшемся окне Использовать источник данных — выбрать ранее созданный источник данных (п. 2), ввести Пользователь и Пароль, жмем Ок.
3.3. Теперь созданный файл надо открыть с помощью Блокнот'а и там будет что-то подобное:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;User Source=MySQL
то, что начинается с Provider и до MySQL и есть строка подключения, т.е. Provider=MSDASQL.1;Persist Security Info=False;User Source=MySQL она нам понадобиться при настройке Excel.
4. Теперь скачиваем специально подготовленный мной документ Excel
5. Открываем, возможно будет ругаться из-за того, что у вас сервер отличный от моего.
6. Идем на вкладку Данные — Параметры — Источник данных — Свойства подключения — Вкладка Определения.
6.1. Вводим Строка подключения, которая у нас появилась ранее (п. 3.3.), вводим текст SQL команды и жмем Ок.

Все, если все правильно было сделано, то в правой части должны появиться поля, которые вы хотели выбрать. Советую на сервере протестировать синтаксис и правильность своего SQL запроса, чтобы не появлялись дополнительные вопросы. Лично я работаю с MySQL через HeidiSQL, там сделал запрос, а потом просто скопировал его в Excel.

Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.

О песочнице

Это «Песочница» — раздел, в который попадают дебютные посты пользователей, желающих стать полноправными участниками сообщества.

Если у вас есть приглашение, отправьте его автору понравившейся публикации — тогда её смогут прочитать и обсудить все остальные пользователи Хабра.

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

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