Формирование строки SQL-запроса в VBA. Выполнить запрос из Access через Excel запрос в VBA Access как запустить запрос из vba

Эй, я только что-то узнали, как поместить мои заявления SQL в VBA (или по крайней мере их выписывать), но я не знаю, как получить возвращенные данные?

У меня есть несколько форм (диаграмма формы), основанные на запросах, которые я запускать довольно регулярные параметры против, просто изменяя временные рамки (например, топ-10 продаж за месяц своего рода вещи). Тогда у меня есть процедуры, которые автоматически передают объект диаграммы в презентации PowerPoint. Так что я все эти запросы предварительно построены (например, 63), и диаграмма формы, чтобы соответствовать (ну да.... 63... я знаю, что это плохо), а затем все эти вещи созданы на «открыто/закрыть»событие, приводящее к следующему (его, как моей самой лучшей попытка быть хаком.... или домино; в зависимости от того вы предпочитаете).

Так что я пытался узнать, как использовать операторы SQL в VBA, так что в конце концов, я могу сделать все это там (я, возможно, нужно сохранить все эти диаграммы формы, но я не знаю, потому что я, очевидно, не хватает понимания).

Так в стороне от вопроса, что я спросил наверху, кто может дать совет? Спасибо

6 ответов

10

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

Dim dbs As Database Dim rs As Recordset Dim strSQL As String Set dbs = CurrentDb strSQL = "your query here Set rs = dbs.OpenRecordset(strSQL) If Not (rs.EOF And rs.BOF) Then rs.MoveFirst "get results using rs.Fields() Else "Use results

За комментарием: возьмите взгляд на класс записей . Он содержит коллекцию под названием поля, которые являются столбцами, которые возвращаются из вашего запроса. Не зная вашу схему, то трудно сказать, но что-то вроде...

Rs.MoveFirst Do While Not rs.EOF "do something like rs("SomeFieldName") rs.MoveNext Loop

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

Использование параметризованных QueryDef и вызывать его из VBA.
Запрос проще проектировать... легко testable..and легко доступны с помощью VBA или формы.

Dim qd as querydef set qd = currentdb.querydefs!myquerydef qd.parameters!parm1=val1

или qd.execute

Dim rs as recordset set rs = qd.openrecordset()

Вот функция, которую вы могли бы рассмотреть рефакторинг взять в строке, и вы будете иметь возможность повторно использовать в любом месте вашего кода.

Так что константный или построить строку для вашего SQL заявление, и поп-музыки в вашем продезинфицировать, NON SQL ИНЖЕКЦИЕЙ строку в качестве аргумента:)

StrSQL = "SELECT * FROM Customer WHERE ID = " & EnsureParamIsNotSQLInjection(customerID)

Затем вызвать функцию/к югу от где вам нужно, чтобы получить данные/набор записей/выполнить инструкцию. Создав несколько доступа к данным функциям/подлодки, где вы можете просто запустить оператор UPDATE или извлекаемую одно значение, или извлечь полномасштабные записи.

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

Sub DoStuff(strSQL) Set adoCon = Server.CreateObject("ADODB.Connection") strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db\Database.mdb") "strConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db\Database.mdb") adoCon.Open strConnString Set rsMain = Server.CreateObject("ADODB.Recordset") rsMain.Open strSQL, adoCon Do While NOT rsMain.EOF customerName = rsMain("CustomerName") "silly example RsMain.MoveNext Loop rsMain.Close Set adoCon = Nothing End Sub

Другой способ сделать это, что, кажется, никто не упомянул, чтобы связать ваш график с одной сохраненной QueryDef, а затем во время выполнения, перепишем QueryDef. Теперь, я не рекомендую изменять сохраненные QueryDefs для большинства контекстов, поскольку она вызывает фронтального раздувание и, как правило, даже не нужно (в большинстве контекстов, где вы используете сохраненный QueryDef могут быть отфильтрованы в той или иной, в том контексте, в котором они используются, например, в качестве одной из форм RecordSource, вы просто передать один аргумент в DoCmd.OpenForm).

Графики разные, так как SQL вождения графики не могут быть изменены во время выполнения.

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

PARAMETERS !! Long;

Если вы используете ссылки формы, это важно, что вы делаете это, потому что с Access 2002 на, то Jet Expression Service не всегда корректно обрабатывает их, когда элементы управления Null. Определение их в качестве параметров выпрямляет эту проблему (которая не присутствовала перед тем Access XP).

Одна из ситуаций, в которых вы должны переписать QueryDef для графа, если вы хотите, чтобы позволить пользователю выбрать N в заявлении TOP N SQL. Другими словами, если вы хотите, чтобы иметь возможность выбрать ТОП 5 или ТОП 10 или ТОП 20, вам придется изменить сохраненный QueryDef, так как N не может быть параметризованы.

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

Уже пробовал

1. con.Execute("EXEC myQuery") , но тот сказал мне, что это не может быть find myQuery.
2. 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

У меня есть файл Excel, который мне нужно автоматизировать. Когда пользователь открывает отчет Excel, ему будет предложено обновить данные. Если они говорят да, то мне нужно запустить запрос...

Как выполнить макрос VBA через запрос представления в MS Access 2013 из JS ActiveX ADO? Функция VBA предназначена для получения текущего пользователя, вошедшего в систему с помощью: Public Declare...

Я пытаюсь запустить запрос, хранящийся в базе данных access от excel vba. Запрос работает нормально, если я открываю и запускаю его в базе данных access, но вызываю ошибку при запуске его из модуля...

Как выполнить сохраненный запрос в MS Access 2007 в VBA? Я не хочу копировать и вставлять SQL в VBA. Я скорее просто выполняю имя запроса. Это не сработает... VBA не удается найти запрос....

Как я могу выполнить запрос для возврата записей в базе данных ms-access с использованием кода VBA?

Как выполнить запрос в MS access db из кода Excel VBA или макроса. Запрос MS-Access принимает некоторые параметры, которые необходимо передать из Excel. Спасибо

У меня есть ситуация очень похожая на следующий пост: Запрос доступа к excel 2010 для создания графика через vba В моем случае я экспортирую таблицу, но я хочу сделать гораздо больше для файла...

У меня есть запрос UPDATE pass through, сохраненный в Access 2007. Когда я дважды щелкаю на запросе pass through, он успешно выполняется. Как я могу получить этот запрос для выполнения из VBA? Я бы...

У меня есть огромный набор данных, который мне нужно импортировать из Excel в Access (~800k строк). Однако я могу игнорировать строки с определенным значением столбца, которые составляют как 90%...

есть ли способ выполнить запрос MDX в пределах Excel VBA? Я думал, что это можно сделать через ADO , так же, как и в случае SQL (да, я знаю, что SQL отличается от MDX - проблема, которая много раз...

Данный урок посвящен 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 и улучшим восприятие нашей программы.

На сегодня это все. Удачи!

С помощью макрокоманды ОткрытьЗапрос в базах данных Access можно открывать запросы на выборку и перекрестные запросы в режиме таблицы, в Конструкторе или в режиме предварительного просмотра. Это действие запускает запрос на изменение. Вы также можете выбрать режим ввода данных для запроса.

Примечание: Данная макрокоманда доступна только в среде базы данных Access (MDB или ACCDB). Если вы используете среду проекта Access (ADP), см. макрокоманды ОткрытьПредставление , ОткрытьСохраненнуюПроцедуру и ОткрытьФункцию . Макрокоманда ОткрытьЗапрос недоступна в веб-приложениях Access.

Настройка

Макрокоманда ОткрытьЗапрос имеет следующие аргументы:

Примечания

Если для аргумента Вид задано значение Таблица , Access отображает результирующий набор, если используется запрос на выборку, перекрестный запрос, запрос на объединение или запрос к серверу, свойство ReturnsRecords которого имеет значение Да . Если это запрос на изменение, запрос определения данных или запрос к серверу, для свойства ReturnsRecords которого задано значение Нет , запрос выполняется.

Макрокоманда ОткрытьЗапрос аналогична двойному щелчку запроса в области навигации или его щелчку правой кнопкой мыши в области навигации и выбору представления. При использовании макрокоманды можно выбрать дополнительные параметры.

Советы

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

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

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

Чтобы выполнить макрокоманду ОткрытьЗапрос в модуле Visual Basic для приложений (VBA), используйте метод ОткрытьЗапрос объекта DoCmd .