Как запустить запрос с переменными на vba. Как получить данные из запроса SQL в Microsoft Access VBA? Запросы к базе INSERT, UPDATE, DELETE на VBA
Access сохранил запрос, разработанный с помощью конструктора запросов под названием "myQuery". База данных подключается к системе через соединение ODBC. Macros все включены.
Excel имеет соединение ADODB для подключения к базе данных через
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "MyDatabase.accdb" End With
Обычно вы идете вперед и просто пишете свой SQL, что совершенно нормально, а затем просто делаете что-то вроде
Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...
Но я хочу получить доступ к запросу, который я сохранил в базе данных access. Итак, как я могу вызвать сохраненный запрос в базе данных, которую я только что подключил.
Уже пробовал
- con.Execute("EXEC myQuery") , но тот сказал мне, что это не может быть find myQuery.
- rs.Open "myQuery", con но это один недействителен и хочет SELECT / etc заявления от него
5 Ответов
6
Я думаю, что вы можете рассматривать его как хранимую процедуру.
Если мы начнем прямо перед Dim sqlQuery As String
Dim cmd as new ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()
Затем возьмите свою работу набора записей после этого.
1
Ты был почти там:
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "z:\docs\MyDatabase.accdb" End With con.Execute "MyQuery"
Просто оставь в стороне Экзека.
Вы также можете добавить параметры, это немного устарело, но должно помочь:
0
Это своего рода халтура, но вы можете запросить запрос. То есть замените строку sql на следующую:
SqlQuery = "SELECT * FROM QueryName;"
Перед запуском этой программы необходимо убедиться, что база данных Access была сохранена ie. нажмите Ctrl+S (недостаточно, чтобы запрос был запущен в Access).
0
Я смог запустить запрос обновления, который уже был сохранен в Access using:
Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc
Это давало мне ошибки, пока я не заменил пробелы в имени запроса подчеркиванием как в базе данных Access, так и в инструкции execute.
0
Прошло много времени с тех пор, как эта нить была создана. Если я все правильно понимаю, то могу добавить что-нибудь полезное. Я дал название тому, что описывает OP, то есть процессу использования SQL из запроса, сохраненного в ACCDB, для запуска в VBA через DAO или ADOBD. Я дал ему имя "поставщик свойств объекта", даже с аббревиатурой OPP в моих заметках, и для имени объекта prefix/suffix.
Идея заключается в том, что существующий объект в ACCDB (обычно запрос) предоставляет свойство (обычно SQL), которое необходимо использовать в VBA. Я собрал функцию только для того, чтобы высосать SQL из запросов для этого; см. ниже. Предупреждение: извините, но это все в DAO, я не очень-то использую ADODB. Надеюсь, вы все еще найдете эти идеи полезными.
Я даже зашел так далеко, что разработал метод использования / вставки заменяемых параметров в SQL, который исходит из этих OPP запросов. Затем я использую VBA.Replace(), чтобы сделать замену, прежде чем использовать SQL в VBA.
Путь объекта DAO к SQL запроса в ACCDB выглядит следующим образом:
MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL
Я использую заменяемые параметры, оценивая то, что должно быть заменено, и выбирая необычное имя для параматера, которое не может существовать в реальной базе данных. По большей части, единственные замены, которые я сделал, - это имена полей или таблиц или выражения предложений WHERE и HAVING. Поэтому я называю их такими вещами, как" {ReplaceMe00000001}", а затем использую функцию Replace() для выполнения этой работы...
SqlText = VBA.Replace(sqlText, "{ReplaceMe00000001}", "SomeActualParameter") ...
а затем используйте sqlText в VBA. Вот рабочий пример:
Public Function MySqlThing() Dim sqlText as String Dim myParamater as String Dim myExpression as String "Set everything up. sqlText = getSqlTextFromQuery("myQuery") myParameter = "{ReplaceMe00000001}" myExpression = "SomeDateOrSomething12/31/2017" "Do the replacement. sqlText = VBA.Replace(sqlText, myParameter, myExpression) "Then use the SQL. db.Execute sqlText, dbFailOnError End Function Function getSqlTextFromQuery(ByVal oppName As String) As String Dim app As Access.Application Dim db As DAO.Database Dim qdefs As DAO.QueryDefs Dim qdef As DAO.QueryDef Dim sqlText As String Set app = Access.Application Set db = app.CurrentDb Set qdefs = db.QueryDefs Set qdef = qdefs(oppName) oppGetSqlText = qdef.SQL End Function
Выполнить запрос в Access MakeTable от Excel
У меня есть файл Excel, который мне нужно автоматизировать. Когда пользователь открывает отчет Excel, ему будет предложено обновить данные. Если они говорят да, то мне нужно запустить запрос...
Выполнить функцию VBA через запрос представления в MS Access 2013 из JS ActiveX ADO
Как выполнить макрос VBA через запрос представления в MS Access 2013 из JS ActiveX ADO? Функция VBA предназначена для получения текущего пользователя, вошедшего в систему с помощью: Public Declare...
Выполнить сохраненный запрос, содержащий "function" в access db из excel
Я пытаюсь запустить запрос, хранящийся в базе данных access от excel vba. Запрос работает нормально, если я открываю и запускаю его в базе данных access, но вызываю ошибку при запуске его из модуля...
MS Access-выполнить сохраненный запрос по имени в VBA
Как выполнить сохраненный запрос в MS Access 2007 в VBA? Я не хочу копировать и вставлять SQL в VBA. Я скорее просто выполняю имя запроса. Это не сработает... VBA не удается найти запрос....
Как выполнить запрос в ms-access в коде VBA?
Как я могу выполнить запрос для возврата записей в базе данных ms-access с использованием кода VBA?
Запустите запрос доступа от excel и передайте ему paramerts
Как выполнить запрос в MS access db из кода Excel VBA или макроса. Запрос MS-Access принимает некоторые параметры, которые необходимо передать из Excel. Спасибо
Управление книгой Excel из Access 2010 VBA
У меня есть ситуация очень похожая на следующий пост: Запрос доступа к excel 2010 для создания графика через vba В моем случае я экспортирую таблицу, но я хочу сделать гораздо больше для файла...
Выполнить SQL Серверный Сквозной Запрос Из Access VBA
У меня есть запрос UPDATE pass through, сохраненный в Access 2007. Когда я дважды щелкаю на запросе pass through, он успешно выполняется. Как я могу получить этот запрос для выполнения из VBA? Я бы...
Импорт огромного набора данных в Access из Excel через VBA
У меня есть огромный набор данных, который мне нужно импортировать из Excel в Access (~800k строк). Однако я могу игнорировать строки с определенным значением столбца, которые составляют как 90%...
Любой запрос MDX в пределах Excel vba?
есть ли способ выполнить запрос MDX в пределах Excel VBA? Я думал, что это можно сделать через ADO , так же, как и в случае SQL (да, я знаю, что SQL отличается от MDX - проблема, которая много раз...
С помощью макрокоманды ОткрытьЗапрос в базах данных Access можно открывать запросы на выборку и перекрестные запросы в режиме таблицы, в Конструкторе или в режиме предварительного просмотра. Это действие запускает запрос на изменение. Вы также можете выбрать режим ввода данных для запроса.
Примечание: Данная макрокоманда доступна только в среде базы данных Access (MDB или ACCDB). Если вы используете среду проекта Access (ADP), см. макрокоманды ОткрытьПредставление , ОткрытьСохраненнуюПроцедуру и ОткрытьФункцию . Макрокоманда ОткрытьЗапрос недоступна в веб-приложениях Access.
Настройка
Макрокоманда ОткрытьЗапрос имеет следующие аргументы:
Аргумент макрокоманды | Описание |
|---|---|
|
Имя запроса |
Имя открываемого запроса. Выберите имя в раскрывающемся списке. Это обязательный аргумент. При выполнении в базе данных библиотеки макроса, содержащего макрокоманду ОткрытьЗапрос , Access сначала ищет запрос с этим именем в базе данных библиотеки, а затем в текущей базе данных. |
|
Представление, в котором будет открываться запрос. Выберите в поле Вид значение Таблица , Конструктор , Предварительный просмотр , Сводная таблица или Сводная диаграмма . По умолчанию используется Таблица . Примечание: Представления "Сводная таблица" и "Сводная диаграмма" недоступны в версиях Access, начиная с Access 2013. |
|
|
Режим данных |
Режим ввода данных для запроса. Этот параметр относится только к запросам, открытым в режиме таблицы. Выберите Добавить (пользователи смогут добавлять новые записи, но не изменять существующие), Изменить (пользователи смогут изменять существующие записи, а также добавлять новые) или Только для чтения (пользователи смогут только просматривать записи). По умолчанию используется значение Изменить . |
Примечания
Если для аргумента Вид задано значение Таблица , Access отображает результирующий набор, если используется запрос на выборку, перекрестный запрос, запрос на объединение или запрос к серверу, свойство ReturnsRecords которого имеет значение Да . Если это запрос на изменение, запрос определения данных или запрос к серверу, для свойства ReturnsRecords которого задано значение Нет , запрос выполняется.
Макрокоманда ОткрытьЗапрос аналогична двойному щелчку запроса в области навигации или его щелчку правой кнопкой мыши в области навигации и выбору представления. При использовании макрокоманды можно выбрать дополнительные параметры.
Советы
Вы можете перетащить запрос из области навигации в окно конструктора макросов. При этом будет автоматически создана макрокоманда ОткрытьЗапрос , которая открывает запрос в режиме таблицы.
Если переключиться в Конструктор, когда открыт запрос, значение аргумента Режим данных удаляется. Этот параметр не будет действовать, даже если пользователь вернется в режим таблицы.
Если вы не хотите отображать системные сообщения, которые обычно появляются при выполнении запросов на изменение (в них говорится о том, что это запрос на изменение, и указано количество записей, на которые он влияет), вы можете отключить их с помощью макрокоманды ЗадатьПредупреждение .
Чтобы выполнить макрокоманду ОткрытьЗапрос в модуле Visual Basic для приложений (VBA), используйте метод ОткрытьЗапрос объекта DoCmd .
Access сохранил запрос, который был разработан с помощью построителя запросов myQuery. База данных подключена к системе через соединение ODBC. Макросы все включены.
Excel Has устанавливает соединение ADODB для подключения к базе данных через
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "MyDatabase.accdb" End With
Обычно вы просто пишете свой SQL, который прекрасно работает, а затем просто делаете что-то вроде
Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...
Но я хочу получить доступ к запросу, который я сохранил в базе данных доступа. Итак, как мне вызвать сохраненный запрос в базе данных, которую я только что подключил.
Пробовал уже
- con.Execute ("EXEC myQuery"), но тот сказал мне, что не может быть найти myQuery.
- rs.Откройте "myQuery", но он недействителен и требует от него операторов SELECT/etc
5 ответов
Я думаю, что вы можете рассматривать это как хранимую процедуру.
Если мы начнем прямо перед Dim sqlQuery As String
Dim cmd as new ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()
Затем заберите свою работу с набором записей после этого.
Вы были почти там
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "z:\docs\MyDatabase.accdb" End With con.Execute "MyQuery"
Просто оставь Exec.
Вы также можете добавить параметры, это немного устарело, но должно помочь: обновить 2 поля в базе данных Access данными Excel и, возможно, макросом
Мне удалось запустить запрос на обновление, который уже был сохранен в Access, используя:
Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc
Это давало мне ошибки до тех пор, пока я не заменил пробелы в имени запроса подчеркиванием как в базе данных Access, так и в операторе execute.
Это своего рода хакерская работа, но вы можете запросить запрос. То есть замените строку SQL следующим:
SqlQuery = "SELECT * FROM QueryName;"
Перед запуском необходимо убедиться, что база данных Access была сохранена, т.е. нажмите Ctrl + S (недостаточно, чтобы запрос был выполнен в Access).
Давно с момента создания этой темы. Если я правильно понимаю, я мог бы добавить что-то полезное. Я дал имя тому, что описывает OP: это процесс использования SQL из запроса, сохраненного в ACCDB, для запуска в VBA через DAO или ADOBD. Я назвал его «Провайдер свойства объекта», даже с акронимом OPP в моих заметках и для префикса/суффикса имени объекта.
Идея заключается в том, что существующий объект в ACCDB (обычно запрос) предоставляет свойство (обычно SQL), которое необходимо использовать в VBA. Я собрал воедино функцию, просто чтобы высосать SQL из запросов для этого; Смотри ниже. Предупреждение: извините, но это все в DAO, я не особо пользуюсь ADODB. Надеюсь, вы все еще найдете идеи полезными.
Я даже зашел так далеко, что разработал метод использования/вставки заменяемых параметров в SQL, который приходит из этих запросов OPP. Затем я использую VBA.Replace() для замены перед использованием SQL в VBA.
Путь объекта DAO к SQL-запросу в ACCDB выглядит следующим образом:
MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL
Я использую заменяемые параметры, оценивая то, что нужно заменить, и выбирая необычное имя для параметра, которое не может существовать в реальной базе данных. По большей части единственными заменами, которые я сделал, являются имена полей или таблиц или выражения предложений WHERE и HAVING. Поэтому я называю их такими, как "{ReplaceMe00000001}", а затем использую функцию Replace() для выполнения работы...
SqlText = VBA.Replace(sqlText, "{ReplaceMe00000001}", "SomeActualParameter")
А затем используйте sqlText в VBA. Вот рабочий пример:
Public Function MySqlThing() Dim sqlText as String Dim myParamater as String Dim myExpression as String "Set everything up. sqlText = getSqlTextFromQuery("myQuery") myParameter = "{ReplaceMe00000001}" myExpression = "SomeDateOrSomething12/31/2017" "Do the replacement. sqlText = VBA.Replace(sqlText, myParameter, myExpression) "Then use the SQL. db.Execute sqlText, dbFailOnError End Function Function getSqlTextFromQuery(ByVal oppName As String) As String Dim app As Access.Application Dim db As DAO.Database Dim qdefs As DAO.QueryDefs Dim qdef As DAO.QueryDef Dim sqlText As String Set app = Access.Application Set db = app.CurrentDb Set qdefs = db.QueryDefs Set qdef = qdefs(oppName) oppGetSqlText = qdef.SQL End Function
Данный урок посвящен SQL запросам к базе данных на VBA Access . Мы рассмотрим, как на VBA осуществляется запросы INSERT, UPDATE, DELETE к базе данных, а также научимся получать конкретное значение из запроса SELECT.
Те, кто программируют на VBA Access и работая при этом с базой данных SQL сервера, очень часто сталкиваются с такой простой и нужной задачей как посыл SQL запроса к базе данных, будь то INSERT, UPDATE или простой SQL запрос SELECT . А так как мы начинающие программисты мы тоже должны уметь это делать, поэтому сегодня займемся именно этим.
Мы уже затрагивали тему получения данных с SQL сервера, где как раз на VBA писали код для получения этих данных, например в статье про Выгрузку данных в текстовый файл из MSSql 2008 или также немного затрагивали в материале Выгрузка данных из Access в шаблон Word и Excel , но так или иначе там мы рассматривали это поверхностно, а сегодня предлагаю поговорить об этом чуть более подробней.
Примечание! Все примеры ниже рассмотрены с использованием ADP проекта Access 2003 и базы данных MSSql 2008. Если Вы не знаете что вообще такое ADP проект то это мы рассматривали в материале Как создать и настроить ADP проект Access
Исходные данные для примеров
Допустим, у нас есть таблица test_table, которая будет содержать номера и названия месяцев в году (запросы выполнены с использованием Management Studio )
CREATE TABLE .( NOT NULL, (50) NULL) ON GO
Как я уже сказал, мы будем использовать ADP проект, настроенный на работу с MS SQL 2008, в котором я создал тестовую форму и добавил кнопку start с подписью «Выполнить» , которая нам понадобится для тестирования нашего кода, т.е. весь код мы будем писать в обработчике события «Нажатие кнопки ».
Запросы к базе INSERT, UPDATE, DELETE на VBA
Чтобы долго не тянуть сразу приступим, допустим, нам нужно добавить строку в нашу тестовую таблицу (код прокомментирован )/
Private Sub start_Click() "Объявляем переменную для хранения строки запроса Dim sql_query As String "Записываем в нее нужный нам запрос sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "Июнь")" "Выполняем его DoCmd.RunSQL sql_query End Sub
В данном случае запрос выполняется с использованием текущих параметров подключения к базе данных. Можем проверить, данные добавились или нет.
Как видим, данные вставились.
Для того чтобы удалить одну строку пишем вот такой код.
Private Sub start_Click() "Объявляем переменную для хранения строки запроса Dim sql_query As String "Записываем в нее запрос на удаление sql_query = "DELETE test_table WHERE id = 6" "Выполняем его DoCmd.RunSQL sql_query End Sub
Если мы проверим, то увидим, что нужная строка удалилась.
Для обновления данных записываем в переменную sql_query запрос update, надеюсь, смысл понятен.
Запрос SELECT к базе на VBA
Здесь дела обстоят чуть интересней, чем с остальными конструкциями SQL.
Первое, допустим, нам нужно получить все данные из таблицы, и, к примеру, мы их обработаем и выведем в сообщении, а Вы, конечно же, можете использовать их для других целей, для этого мы пишем вот такой код
Private Sub start_Click() "Объявляем переменные "Для набора записей из базы Dim RS As ADODB.Recordset "Строка запроса Dim sql_query As String "Строка для вывода итоговых данных в сообщении Dim str As String "Создаем новый объект для записей set RS = New ADODB.Recordset "Строка запроса sql_query = "SELECT id, name_mon FROM test_table" "Выполняем запрос с использованием текущих настроек подключения проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Циклом перебираем записи While Not (RS.EOF) "Заполняем переменную для вывода сообщения str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "переход к следующей записи RS.MoveNext Wend "Вывод сообщения msgbox str End Sub
Здесь мы уже используем циклы VBA Access для того чтобы перебрать все значения в нашем наборе записей.
Но, достаточно часто бывает необходимо получить не все значения из набора записей, а всего лишь одно, например, название месяца по его коду. И для этого использовать цикл как-то накладно, поэтому мы можем просто написать запрос, который вернет всего одно значение и обращаться именно к нему, например, получим название месяца по коду 5
Private Sub start_Click() "Объявляем переменные "Для набора записей из базы Dim RS As ADODB.Recordset "Строка запроса Dim sql_query As String "Строка для вывода итогового значения Dim str As String "Создаем новый объект для записей set RS = New ADODB.Recordset "Строка запроса sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Выполняем запрос с использованием текущих настроек подключения проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Получаем наше значение str = RS.Fields(0) msgbox str End Sub
Для универсальности здесь мы уже обратились не по имени ячейки, а по ее индексу, т.е. 0, а это самое первое значение в Recordset , в итоге мы получили значение «Май» .
Как видите, все достаточно просто. Если Вам достаточно часто требуется получать конкретное значение из базы (как в последнем примере ), то рекомендую вывести весь код в отдельную функцию (Как написать функцию на VBA Access 2003) с одним входящим параметром, например, код месяца (если рассматривать наш пример ) и просто, где необходимо вывести это значение, вызывать нужную нам функцию с нужным параметром и все, этим мы значительно уменьшим код VBA и улучшим восприятие нашей программы.
На сегодня это все. Удачи!
Аксесс позволяет создавать программно строки SQL-запросов, а также строки, служащие значениями свойств форм, отчетов и т.д., параметров функций и т.д., которые тоже должны соответствовать синтаксису SQL. При этом надо помнить, что входящие в такую строку константы должны быть тоже оформлены по правилам SQL, а именно:
Если это нарушить, то возможны следующие эффекты:
Ниже в каждом разделе приведен пример строки SQL, которая должна получиться при ее программном создании, а затем код VBA, который ее создает. Еще ниже дана очень полезная рекомендация.
1. Использование чисел
SELECT
*
FROM
Table
WHERE
(((Table
.Quanty)=12
.197
));
VBA v1
Dim q As
Single
q = 12
.197
strSQL = "SELECT * "
_
& "FROM Table "
_
& "WHERE (((Table.Quanty)="
& q & "));"
VBA v2
Dim q As
String
q = "12,197"
strSQL = "SELECT * "
_
& "FROM Table "
_
& "WHERE (((Table.Quanty)="
& Str
(q) & "));"
Примечание:
Function GetDecimalSeparator() As String GetDecimalSeparator = Format (0 #, "." ) End Function Также следует учесть, что в этом случае некоторые стандартные действия Access могут не работать.
2. Использование строк
SELECT
*
FROM
Table
WHERE
(((Table
.Name)="All"
));
VBA v1
Dim q As
String
q = "All"
strSQL = "SELECT * "
_
& "FROM Table "
_
& "WHERE (((Table.Quanty)="
""
& DoubleQuote(q) & ""
"));"
VBA v2
Dim q As
String
q = "All"
strSQL = "SELECT * "
_
& "FROM Table "
_
& "WHERE (((Table.Quanty)="" & DoubleApostrophe(q) & ""
));"
Примечание:
Пример:
условие выборки:
a"a"s
SQL:
WHERE
field="a""a"s
"
VBA:
strWhere = "WHERE
field=""
" & "a""""a"s
" & """
"
Пример:
условие выборки:
a"a"s
SQL:
WHERE
field="a"a""s
"
VBA:
strWhere = "WHERE
field="
" & "a""a""s
" & ""
"
Public
Function
Replace97(StrMain As
String
, StrFind As
String
, StrZam As
String
) As
String
On
Error
GoTo
err
Dim
pos As
Long
If
StrFind = ""
Then
GoTo
err
If
StrMain = ""
Then
Replace97 = StrZam: Exit
Function
Do
Until
InStr(1
, StrMain, StrFind) = 0
pos = InStr(1
, StrMain, StrFind)
StrMain = mid
(StrMain, 1
, pos - 1
) & StrZam & mid
(StrMain, pos + Len(StrFind), Len(StrMain))
Loop
Replace97 = StrMain
Exit
Function
err:
Replace97 = StrMain
End
Function
3. Использование дат
SELECT
*
FROM
Table
WHERE
(((Table
.TimeOpen)=#3
/31
/2003
11
:17
:19
#));
VBA
Dim q As
Date
q = Now
strSQL = "SELECT * "
_
& "FROM Table "
_
& "WHERE (((Table.TimeOpen)=#"
& Format
(q, "mm\/dd\/yy hh\:mm\:ss"
) & "#));"
Примечание:
Составив такой стринг, содержащий команду SQL, и отправив его на выполнение, вы можете получить ошибку. В таком случае распечатайте этот стринг в отладочное окно и посмотрите на него глазами. Возможно, ошибка сразу бросится в глаза. Если не бросится, создайте новый запрос в конструкторе запросов, переключите в режим SQL, вставьте туда текст запроса и запустите на выполнение. Если ошибка есть, то она будет показана более явным образом.
