Как в vba excel подключиться к sql
Обновлено: 07.07.2024
Очень часто у программистов и пользователей, у которых реализовано приложение в связке клиент (Access – adp проект) и сервер (Microsoft SQL Server – база данных) возникает необходимость импорта данных, с помощью adp проекта, в базу данных, причем чтобы это могли делать простые пользователи, т.е. максимально просто, без привлечения программистов. И сегодня мы поговорим о реализации данной возможности.
Так как импортируемые данные могут быть разные, сегодня мы рассмотрим 2 примера импорта.
- Импорт данных из Excel
- Импорт изображения в базу данных и ее дальнейшее отображение в отчетах и формах
Импорт данных из Excel в Microsoft SQL Server на VBA Access
И начнем мы с импорта данных из таких форматов как xls и dbf, так как это очень часто требуется в работе, а если у кого и не часто, то все равно рано или поздно такая необходимость возникнет.
Когда у меня стояла задача реализовать такой импорт, я нашел много способов и средствами Microsoft SQL сервера, и встроенными средствами Access, но в каждом из них были небольшие минусы, иными словами, не для каждодневного использования пользователями. Но мне встретился такой способ, который меня полностью устроил и именно о нем я сейчас расскажу.
Данный способ заключается в подключении напрямую к файлу источнику через поставщика Microsoft.Jet.OLEDB.4.0.
Для начала давайте определимся, с какими данными мы будем работать.
Допустим, у нас есть файл «file.xls» со следующими данными:
Примечание! Называйте лист в файле, на котором располагаются данные, также как и сам файл.
Импортировать мы будем во временную таблицу, например, testimport, из которой Вы легко сможете переносить или обновлять данные в других таблицах (например, через процедуру).
Таблицу создадим вот так:
Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка T-SQL.
Теперь можно переходить к самому процессу импорта, для визуального оформления нам понадобится одна форма и всего лишь одна кнопка на ней (как Вы реализуете форму это Ваше дело, или Вы просто добавите данную кнопку на существующую форму у себя в приложении, т.е. форма здесь не главное).
Вы добавили кнопку, теперь в событие «Нажатие кнопки» добавьте следующий код:
Общий смысл заключается в том, что Вы выбираете файл, а если конкретней, то считываете путь к файлу, затем подключаетесь к этому файлу, считываете данные и записываете в свою базу данных.
Примечание! Если Вы хотите накапливать данные в таблице (в нашем случае testimport), то в файле источнике, добавляете какой-нибудь признак, чтобы потом иметь возможность выбрать те данные, которые Вы только что загрузили (например, столбец с датой, и в базе соответственно тоже добавьте), если не хотите, то перед импортом данных в таблицу, очищайте ее, иначе все данные будут накапливаться.
Импорт изображения в Microsoft SQL Server на VBA Access
Теперь перейдем к импорту изображения и его дальнейшее отображение. Мне это понадобилось тогда, когда возникла необходимость выводить некую картинку в отчетах, но при условии того, что проект adp был один общий, а баз было несколько, т.е. для разных групп пользователей (несколько филиалов), и картинки во всех филиалах разные, поэтому статически прописать в отчетах было нельзя, поэтому мне пришлось искать другие решения.
Примечание! Примеры ниже тестовые, поэтому Вы можете создать свою таблицу со своими полями, и, соответственно, свою форму.
На форму добавьте объект «Рисунок», первоначально придется выбрать любой рисунок для добавления данного объекта на форму, потом его можно очистить (я его назвал kartinka). Источник записей формы будет наша вновь созданная таблица. И, конечно же, кнопочку для добавления рисунка. Для кнопки в событии «Нажатие кнопки» вставьте следующий код:
Для наглядности, чтобы увидеть, что Вы импортировали картинку в базу, на форме в событии «Текущая запись» вставьте следующий код:
Теперь, где Вам нужно выводить картинку, например, в отчетах, Вы также создадите объект рисунок и в событии отчета «Открытие» будете присваивать значение свойства PictureData из базы данных и все. Например, вот так:
Здесь, конечно же, следовало бы предусмотреть возможность отсутствия данных, но для примера сойдет. Также если у Вас несколько картинок, то укажите условие в запросе.
Смысл заключается в использовании свойства PictureData объекта «Рисунок», и таким способом у Вас будут без проблем отображаться картинки разных форматов, в отличие от распространенного ole объекта, для которого необходимо устанавливать специальные ole сервера для корректного отображения различных форматов файлов.
Вот в принципе и все, если что непонятно пишите в комментариях, может, чем помогу. Удачи!
Служба MySQL должна быть сначала установлена в системе, поэтому я не буду много говорить об этом. Вы можете использовать официальный установочный пакет или использовать базу данных в интегрированной среде PHP.
1.1 Включить поддержку сценариев
Перед использованием необходимо запустить поддержку подключения к базе данных в VBE. Нажмите Alt + F11, чтобы открыть VBE, выберите «Инструменты» - «Справочник» в строке меню, во всплывающем справочном окне найдите «Библиотека объектов данных Microsoft ActiveX 6.1» и «Библиотека записей объектов данных Microsoft ActiveX 6.0», поместите предыдущий Установите флажок и нажмите ОК.
1.2 Установите службу подключения MySQL ODBC
Выберите операционную систему и версию системы, загрузите соответствующий файл MSI и установите.
Здесь следует отметить, что при загрузке и установке разъема ODBC выберите свойОдинаковое количество версий базы данных, Вместо системных битов, в противном случае будет отображаться запрос о том, что драйвер базы данных не найден после установки
(я попытался установить 64-битный соединитель в 64-битной системе, после установки соединение запрашивает, что драйвер не найден, и затем 32-битный соединитель успешно установлен)
1.3 Добавить источник данных ODBC
Откройте «Панель управления» - «Администрирование» - «Источник данных ODBC», во всплывающем окне справа от вкладки «Пользовательский DSN» выберите «Добавить», в новом источнике базы данных появятся два драйвера MySQL. Для «MySQL ODBC 5.3 ANSI Driver» и «MySQL ODBC 5.3 Unicode Driver» ясно, что разница между ними заключается в стандарте кодирования. Я выбрал версию Unicode. Выберите один из них и нажмите Готово
Порядок конфигурации 1.1 и 1.2 может быть произвольным, но 1.3 должен быть после 1.2, в противном случае параметр MySQL не может быть найден при добавлении нового источника данных в источник данных ODBC.
2. VBA подключается к MySQL
После настройки поддержки среды в соответствии с шагом 1.1 вы можете использовать код в VBA для подключения к MySQL.
Сначала определите объект подключения
Подключиться к базе данных
Таким образом, соединение с базой данных успешно!
Вы можете использовать объект подключения State Атрибуты и Version Свойства для просмотра состояния и версии базы данных (проверьте, успешно ли установлено соединение)
Наконец, закройте соединение с базой данных
3. VBA чтение и запись таблицы данных MySQL
3.1 Чтение данных MySQL в Excel
Код выглядит следующим образом:
По сравнению с предыдущим кодом приведенный выше код более ADODB.Recordset и rs.Open ， ADODB.Recordset Используется для выполнения операторов SQL и получения результирующего набора, возвращенного оператором запроса.
Здесь следует упомянуть, что существует два способа выполнения операторов SQL в VBA, один из которых заключается в использовании объектов подключения: conn.Execute , Первый параметр - это оператор SQL, другой выполняется с использованием объекта результирующего набора: rs.Open Таким образом, есть два необходимых параметра, а именно оператор SQL и объект подключения, как в приведенном выше примере. rs.Open "select * from `test`", conn 。
Следующие две строки Range Используется для копирования результатов запроса в таблицу Excel.
3.2 Записать данные в MySQL
На самом деле, для записи данных нужно всего лишь изменить оператор SQL в приведенном выше примере на UPDATE или INSERT Вот и все, я не много говорю.
4. Непосредственно работать MySQL в Excel
MySQL представил подключаемый модуль Excel для работы с базой данных MySQL непосредственно в Excel.
Просто скачайте и запустите установку.
Затем откройте (перезапустите) Excel. При открытии вас могут спросить, нужно ли добавить надстройку. После открытия нажмите «Данные» в строке меню, вы можете увидетьMySQL for ExcelВещи. Нажмите на него, и появится локальная база данных MySQL.
Вы можете щелкнуть правой кнопкой мыши базу данных и отредактировать информацию о соединении:
Или дважды щелкните, чтобы открыть каталог базы данных, по-прежнему открывать каталог таблицы данных, а затем выбрать одну из таблиц данных. Ниже будут представлены три параметра, а именно: импорт данных MySQL, редактирование данных MySQL, добавление данных MySQL. Если вы выберете «Импорт» или «Редактировать», новый лист будет добавлен в Excel для обработки данных. Если вы решите добавить его, он не будет. Что касается конкретных операций, то все они в стиле дурака, поэтому я не буду вдаваться в подробности.
Примечание: EXCEL VBA обычно имеет следующие 3 шага для подключения к базе данных SQL:
1. VBA необходимо создать объект подключения перед подключением к базе данных: вы можете использовать следующие методы
Вы также можете добавить ссылку, а именно: Инструменты ---- Ссылки - найдите самую высокую версию библиотеки объектов данных ActiveX Microsoft, проверка прошла успешно, как показано на следующем рисунке:
Затем за один шаг: Dim CN as New ADODB.Connection
2. После создания объекта наиболее критичным и наиболее важным является объединение строк, соединяющихся с базой данных: существует два способа соединения строк базы данных: локальное соединение и удаленное IP-соединение;
Строка локального соединения: strCn = "Поставщик = sqloledb; Сервер = имя локальной базы данных; База данных = имя базы данных; Интегрированная безопасность = SSPI; Информация о постоянной безопасности = Ложь;" Среди них Integrated Security = SSPI очень важен и не может отсутствовать. Конкретное значение может быть Baidu;
Строка удаленного IP-соединения: strCn = "Provider = sqloledb; Источник данных = IP-адрес удаленной базы данных; Исходный каталог = имя базы данных; ID пользователя = имя для входа; Пароль = пароль; Persist Security Info = False;" Это относительно просто, если доступ к удаленной конфигурации базы данных;
3. Когда база данных подключена, данные читаются. Есть два способа чтения данных.
Один проходит через цикл For, а другой - копирует содержимое набора записей в ячейку. Range("A2").CopyFromRecordset rs Путь, как правило, исправлен.
Весь код выглядит следующим образом:
Это завершает весь процесс от открытия соединения с базой данных до чтения данных и до закрытия базы данных.
Интеллектуальная рекомендация
зеркало Zynq
Сначала выберите fsbl.elf, затем файл .bit и, наконец, файл .elf проекта. Файл BOOT, запрограммированный NandFlash, является двоичным файлом bin, а файл BOOT, запрограммированный QSPIFlash, является Ф.
Qt - UI пакет
1. Профиль кнопки кнопки 1. 2. Результаты видов и виджет 3. Контейнер для контейнера Добавьте страницу: сначала выберите текущую страницу, затем выберите Щелкните правой кнопкой мыши StackedWidget, чт.
Самые ценные цветы в горшках известны как "Цветы долголетия", красочные и легко выращиваемые, цветущие в течение многих лет.
Цветок долголетия Рождественская гирлянда, капуста, всемирно известный цветок, после того, как она была завезена в Китай, получила название «цветок долголетия» Цветок долголетия Цветки огр.
Руководство по установке MySQL5.7.21 под Linux
Руководство по установке MySQL5.7.21 под Linux В этой статье в основном рассказывается, как установить MySQL 5.7.21 под Linux. Требуемая среда: CentOS 6.9 mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz Уд.
Найдите простые числа в пределах 1 миллиона с помощью Python
1. Определите, является ли число простым Во-первых, давайте посмотрим, как определить, является ли число простым или нет. Вспомните, когда я был ребенком, я изучал математику. Простые числа - это прос.
Мало пользователей, да и начинающих программистов, которые знают о возможности 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 сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!
Читайте также: