Запрос не выполняется или не удается открыть таблицу базы данных excel
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: 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 до решить вопрос.
очистить системную память, вы всегда можете запустить что-то вроде этого:
это очистит стек отмены, в котором находятся все обновления ваших сводных таблиц, что позволит вам отменить их, если вы сделаете это между ссылками, это может помочь вам контролировать использование памяти.
пожалуйста, расценивайте мое предыдущее предложение, поскольку я думал о решении, которое помогло мне в доступе.
"внешняя таблица не в ожидаемом формате."обычно происходит при попытке использовать файл Excel 2007 со строкой подключения, использующей: Microsoft.Реактивный.Oledb для.4.0 и расширенные свойства=Excel 8.0
использование следующей строки подключения, похоже, устраняет большинство проблем.
Спасибо за этот код :) я действительно ценю это. Работать на меня.
поэтому, если у вас есть diff версия файла Excel, получите имя файла, если его расширение .XLSX-файл используйте этот:
и если это .в XLS использовать:
(У меня слишком низкая репутация, чтобы комментировать, но это комментарий к записи JoshCaba, используя Ace-engine вместо Jet для Excel 2007)
Это относится и к Excel 2010.
просто добавляйте свое дело. Мой файл xls был создан функцией экспорта данных с веб-сайта, расширение файла xls, он может быть нормально открыт MS Excel 2003. Но и Microsoft.Реактивный.Oledb для.4.0 и Microsoft.ТУЗ.Oledb для.12.0 получено исключение" внешняя таблица не в ожидаемом формате".
наконец, проблема в том, что, как сказано в исключении, "это не в ожидаемом формате". Хотя это имя расширения-xls, Но когда я открываю его с помощью текстового редактора, это на самом деле хорошо сформированный html-файл, все данные находятся в
- ячейкой. Тогда я думаю, что могу разобрать его в html-формате. |
У меня была такая же проблема(используя ACE.OLEDB) и что разрешило это для меня была эта ссылка:
суть в том, что установка нескольких версий office и различных Office sdk, сборок и т. д. привел к Ацеоледбу.ссылка dll в реестре, указывающая на папку OFFICE12 вместо OFFICE14 в
C:\Program Файлы\Общие Файлы\Microsoft Общий\OFFICE14\ACEOLEDB.Dll файлы
кроме того, вы можете изменить раздел реестра, изменив путь dll в соответствии с версией Access.
Access 2007 должен использовать OFFICE12, Access 2010-OFFICE14 и Access 2013-OFFICE15
(ОС: 64-битный офис: 64bit) или (ОС: 32-битный офис: 32бит)
ключ: HKCR\CLSID\InprocServer32\
Имя Значения: (По Умолчанию)
Значение Данных: C:\Program Файлы\Общие Файлы\Microsoft Общий\OFFICE14\ACEOLEDB.Dll файлы
(OS: 64bit офис: 32bit)
ключ: HKCR\Wow6432Node\CLSID\InprocServer32\
Имя Значения: (По Умолчанию)
значение данных: C:\Program файлы (x86)\Общие файлы\Microsoft Общий\OFFICE14\ACEOLEDB.Dll файлы
Я также видел эту ошибку при попытке использовать сложные косвенные () формулы на импортируемом листе. Я заметил это, потому что это была единственная разница между двумя книгами, где один импортировал, а другой нет. Оба были 2007+ .XLSX файлы, и 12.0 двигатель был установлен.
Я подтвердил, что это была проблема:
- создание копии файла (все еще была проблема, так что это не было какой-то сохранить-как разница)
- опции все ячейки в листе с косвенными формулами
- вставка только как значения
и ошибка исчезла.
Я получал ошибки со сторонним и Oledb чтением книги XLSX. Проблема, как представляется, скрытый лист, который вызывает ошибку. Отображение листа позволило импортировать книгу.
вместо OleDb вы можете использовать взаимодействие Excel и открыть лист только для чтения.
файл может быть заблокирован другим процессом, вам нужно скопировать его, а затем загрузить его, как он говорит, в это в должности
рекомендация сохранить как Excel 2003
Если у вас все еще есть эта проблема, то проверьте свои разрешения, я пробовал многие из этих предложений, и моя конкретная проблема заключалась в том, что файл, который я хотел обработать, находился под контролем источника, и поток не имел разрешений, мне пришлось изменить все разрешения папки, и он начал работать (я обрабатывал много файлов там). Он также соответствует многим предложениям, таким как изменить имя файла или проверить, что файл не loicked другим процессом.
Я надеюсь, что это помочь тебе.
Ace поддерживает все предыдущие версии Office
этот код работает хорошо!
Это может произойти, если книга защищена паролем. Существуют некоторые обходные пути для удаления этой защиты, но большинство примеров, которые вы найдете в интернете, устарели. В любом случае простое решение-снять защиту книги вручную, иначе используйте что-то вроде OpenXML для программного удаления защиты.
столкнулся с той же проблемой и нашел этот поток. Ни одно из предложений выше не помогло, за исключением комментария @Smith к принятому ответу 17 апреля ' 13.
фон моей проблемы достаточно близок к @zhiyazw - в основном пытается установить экспортированный файл Excel (SSRS в моем случае) в качестве источника данных в пакете dtsx. Все, что я сделал, немного повозившись, это переименовал рабочий лист. Он не должен быть строчным, как предложил @Smith.
Я полагаю, туз OLEDB ожидает, что файл Excel будет следовать определенной структуре XML, но каким-то образом Службы Reporting Services об этом не знают.
этот адрес файла excel может иметь неправильное расширение. Вы можете изменить расширение с xls на xlsx или наоборот и повторить попытку.
начиная с 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, чтобы он вытекал из его решения, но у меня нет максимума достаточно очков.
Всем добрый день.
В Excel создано подключение к таблице в БД Access, автоматическое обновление подключения отключено.
Данные обновляются каждую минуту путем авто выполняемого макроса (ontime) через код ActiveWorkbook.RefreshAll. Дальше происходят разные расчеты и т.п.
Время от времени возникает следующая ошибка:
Лечится легко - полный перезапуск excel (сделано через on error go to и батник, который убивает excel и через 10 секунд запускает заново, при запуске Excel запускается "автомакрос ontime").
Крутится это круглосуточно (своего рода система мониторинга). В принципе работает, но хотелось бы искоренить эту проблему.
Может кто знает в чем может быть проблема?
полагаю, если выполнить обновление конкретного соединения, то можно еще получить код результата, ну а дальше. решить что с этим делать
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
Заменил ActiveWorkbook.RefreshAll на ActiveWorkbook.Connections("update" ).Refresh. Отчет крутится, пока ошибок не возникало.
полагаю, если выполнить обновление конкретного соединения, то можно еще получить код результата, ну а дальше. решить что с этим делать
Можете подсказать поподробнее что вы имели ввиду?
Погуглил синтаксис Connections, пока ничего путного не нашел.
Ошибка всё равно возникает.
Это может быть связано с тем, что VBA дергает каждую минуту обновление из Access?
Ошибка возникает рандомно по времени, может в течение нескольких минут после запуска, может часов 8 нормально работать.
Всё это крутится на виртуальном ПК, где больше ничего не делается.
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
Игорь, почти так у меня сейчас и есть.
если ошибки нет - то выполняется код
если ошибка есть - то запускается макрос, который отправляет письмо об ошибке + запускает reboot.bat
Читайте также: