Не удается получить данные из базы данных excel

Обновлено: 07.01.2025

Я в 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 до решить вопрос.

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

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

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

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: 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 предложит сохранить изменения запроса – отвечаем положительно.

Помогите с настройкой соединения с БД MS Excel.
Используются следующие параметры соединения: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SonjaW.xls;Extended Properties=Excel 8.0;Persist Security Info=False

А также помогите с настройкой SQL-запросов.
Используется альтернативный механизм автообзвона:

;Общие настройки
[General]
SrcName="ExcelFile"
Version=1
DateTimeFormat="dd.mm.yyyy h:mm:ss"

;Необработанные номера
[Unprocessed]
Priority=0
Query="select top 1 A.ID, A.Number from [Лист1$] A inner join [Лист2$] B on A.IDMan = B.ID where B.Status=0 and

B.Processing='False' and A.Status=0 order by B.ID, A.ID"
ID="ID"
Number="Number"

;Номера со статусом "Занято"
[Busy]
Priority=1
Query="select top 1 A.ID, A.Number, A.DialLastEnd from [Лист1$] A inner join [Лист2$] B on A.IDMan=B.ID where

(A.Status=2 and A.Count<

) and (B.Status=0 and B.Processing='False') order by A.Count, A.DialLastEnd"
ID="ID"
Number="Number"
EndTime="DialLastEnd"

;Номера со статусом "Не отвечает"
[NotAnswered]
Priority=3
Query="select top 1 A.ID, A.Number, A.DialLastEnd from [Лист1$] A inner join [Лист2$] B on A.IDMan=B.ID where

(A.Status=3 and A.Count<

) and (B.Status=0 and B.Processing='False') order by A.Count, A.DialLastEnd"
ID="ID"
Number="Number"
EndTime="DialLastEnd"

;Номера со статусом "Другое или неверный код"
[Other]
Priority=2
Query="select top 1 A.ID, B.Number, A.DialLastEnd from [Лист1$] A inner join [Лист2$] B on A.idman=B.ID where

(A.Status=4 and A.Count<

) and (B.Status=0 and B.Processing='False') order by A.Count, A.DialLastEnd"
ID="ID"
Number="Number"
EndTime="DialLastEnd"

;Данные об абоненте
;Типы данных: 1 - номер телефона, 2 - число, 3 - дата/время, 4 - сумма руб.
[UserValues]
Query="select 4, SummaZad, 3, DataZad as N1 from [Лист2$] where distinct IDMan from [Лист1$] where

;Перед обзвоном очередного номера
[BeforeDialNumber]
Query="update [Лист2$] A set A.Processing='True' where A.ID=(select distinct IDMan from [Лист1$] where A.ID=

update [Лист1$] B set B.DialLastBegin=GETDATE(), B.Count=B.Count + 1 where B.ID=

;После обзвона очередного номера
[AfterDialNumber]
Query="update [Лист2$] A set A.Status=(case when

=1 then 1 else 0 end), A.Processing='False' where

A.ID=(select distinct IDMan from [Лист1$] where update [Лист1$] B set B.Status=

B.DialLastEnd=GETDATE() where B.ID=

;Если обзвон был прерван
[DialBreak]
Query="update [Лист2$] A set A.Processing='False' where A.ID=(select distinct IDMan from [Лист1$] where

update [Лист1$] B set B.Status=4, B.DialLastEnd=GETDATE() where B.ID=

;Сброс номеров в начальное состояние при включении программы автообзвона
[ResetNumbers]
Query="update [Лист2$] A set A.Status=0, A.Processing='False'; update [Лист1$] B set B.Status=0,

B.DialLastBegin=null, B.DialLastEnd=null, B.Count=0"

Заранее благодарю за помощь.

Скиньте мне на почту упакованный файл "service.log" из папки сервера. Взгляните "service.log" сами. Там увидите, в чем ошибка.

В service.log ошибки следующего вида:

Error: Объект 'Лист1$' не найден ядром базы данных Microsoft Jet. Проверьте существование объекта и правильность имени и пути.

Error: Обнаружены символы за пределами инструкции SQL

Быть может, база данных excel должна располагаться на сервере программы автообзвона?
В настоящее время происходит обращение к БД Excel, расположенной на другом компьютере.

Да, вы сделали правильный вывод. Файл xls должен быть на компе сервера.

Подскажите, как указать 2 запроса на обновление таблиц в БД MS Excel в поле Query?
Указываю через ';'.
В service.log следующая ошибка:

Указали два раза поле Query и данные в двух таблицах обновляются.

Возникла ещё одна проблема: повторять попытку в Настройках дозвона устновлено 5 раз, в столбце Count видим, что совершилось уже 23 попыток дозвона. Подскажите, в чём может быть причина зацикливания программы?
Файл Excel отправила Вам на почту.

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

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

Задача для получения данных в Excel

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

Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.

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

Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Настройка Excel для получения данных с SQL сервера

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

И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»

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

Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

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

В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:

После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:

В итоге у меня загрузятся из базы вот такие данные:

Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

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