Виконання запитів sql в vba. Виконати запит із Access через Excel Query до VBA. Запит SELECT до бази на VBA

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 як новий 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 як String Dim myParamater як String Dim myExpression as String "Set everything up. sqlText = getSqlTextFromQuery("myQuery") myParameter = "(ReplaceMe00000001)" myExpression . sqlText = VBA.Replace(sqlText, myParameter, myExpression) "The use the SQL. 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-запитів, а також рядки, що служать значеннями властивостей форм, звітів і т.д., параметрів функцій і т.д., які також повинні відповідати синтаксису SQL. При цьому треба пам'ятати, що константи, що входять до такого рядка, повинні бути теж оформлені за правилами SQL, а саме:

у числах десятковим роздільником має бути точка,

рядки повинні бути укладені в лапки або апострофи (а лапки або апострофи відповідно всередині рядка подвоєні),

дати повинні бути укладені в ґрати та написані за американським форматом (mm/dd/yyyy) з дробовою рисою як роздільник.

Якщо це порушити, то можливі такі ефекти:

число виду 10,5 з комою замість точки буде сприйнято як список із двох чисел 10 і 5, що призведе до якоїсь невідповідності у кількості полів,

рядок виду Vasya без лапок і апострофів буде сприйнятий як ім'я поля, якщо таке поле є, або як ім'я параметра, який відразу буде запитаний,

дата виду 1/2/2010 або 1-2-2010 без грат буде сприйнята як арифметичний вираз (з поділом та відніманням відповідно),

дата виду 1.2.2010 буде сприйнята як дробове число з двома десятковими точками та призведе до помилки,

дату з ґратами, але не за американським форматом буде сприйнято як дату, але іншу (день і місяць буде переставлено).

Нижче в кожному розділі наведено приклад рядка 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) & "));"
Примітка:

VBA v1- Для цілих чисел. Для дробових чисел це окремий випадок, тільки коли системним роздільником є точка.

VBA v2- Найбільш правильний варіант, т.к. у класичному програмуванні допускається з'єднувати лише рядки з рядками, тоді як VBA v1 використовує неявне перетворення типів, хоча нарікань цього способу цілих чисел був. (Приклад наведений для випадку, коли системним роздільником є кома.)

Іноді використовують для перетворення функцію CStr(), але вона не завжди застосовна, т.к. повертає число у вигляді рядка, де воно записується через системний роздільник, тоді як SQL як роздільник сприймає лише точку.

NB!При використанні нетипових системних роздільників вищенаведені приклади можуть працювати, у разі треба програмно замінювати системний роздільник на точку. Нижче наведено функцію, яка повертає системний роздільник.
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) & "" ));"
Примітка:

VBA v1: DoubleQuote() - функція, що подвоює лапки.

Приклад:
умова вибірки:
a"a"s SQL:
WHERE field=" a""a"s VBA:
strWhere = " WHERE field=""" & "a""""a"s" & """ "

VBA v2:: DoubleApostrophe() - функція, що подвоює апострофи.

Приклад:
умова вибірки:
a"a"s SQL:
WHERE field=" a"a""s VBA:
strWhere = " WHERE field="" & "a""a""s" & "" "

Згадані вище функції DoubleQuote і DoubleApostrophe - це НЕ вбудовані функції Аксесу, а функції користувача, реалізація яких залишається на розсуд програміста. Зокрема, в Аксесі версій 2000 і вище можна для цього використовувати вбудовану функцію Replace, а в 97 і нижче - ось таку функцію:

Public Function Replace97(StrMain As String , StrFind As String , StrZam As String ) As String On Error GoTo err Dim pos As Long If StrFind = "The GoTo err If StrMain = "" The Replace97 = StrZam 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" ) & "#));"
Примітка:

Microsoft JET SQL оперує датами американському форматі, тобто. саме у вищезгаданому вигляді Місяць/День/Рік.

Не пропустіть символи # (він обрамляє всю константу типу дата-час) і \ (він оберігає / і : від їх заміни відповідно до регіональних налаштувань, що має звичай робити функція Format і що заважає правильній роботікоманди SQL).

Рекомендується прочитати тут: про способи зберігання дати/часу.

NB!Не варто користуватися перетворенням дати Integer (або Long), т.к. в Access" і в SQL Server" одній і тій же даті відповідають різні числа і при порівнянні можна отримати несподіваний результат.

Склавши такий стринг, що містить команду SQL, і відправивши його виконання, ви можете отримати помилку. У такому випадку роздрукуйте цей стрінг у вікні налагодження і подивіться на нього очима. Можливо, помилка відразу впаде у вічі. Якщо не кинеться, створіть новий запит у конструкторі запитів, переключіть у режим SQL, вставте туди текст запиту та запустіть виконання. Якщо помилка є, вона буде показана більш явно.

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 заяви від нього

vba excel-vba ms-access-2007 adodb excel

5 Відповідей

Я думаю, що ви можете розглядати його як процедуру, що зберігається.

Якщо ми почнемо прямо перед Dim sqlQuery As String

Dim cmd як новий 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"

Просто залиши осторонь Екзека.

Ви також можете додати параметри, це трохи застаріло, але має допомогти:

Це свого роду халтура, але ви можете запросити запит. Тобто замініть рядок SQL на наступний:

SqlQuery = "SELECT * FROM QueryName;"

Перед запуском цієї програми необхідно переконатись, що база даних Access була збережена ie. натисніть Ctrl+S (недостатньо, щоб запит був запущений у Access).

Я зміг запустити запит на оновлення, який вже був збережений у Access using:

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

Це давало мені помилки, доки я не замінив прогалини в імені запиту підкресленням як у базі даних Access, так і в інструкції execute.

Пройшло багато часу відколи ця нитка була створена. Якщо я все правильно розумію, то можу додати щось корисне. Я дав назву тому, що описує 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. Ось робочий приклад:

Виконати запит у 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 - проблема, яка багато разів...

Цей урок присвячений 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 "Цик 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. End Sub

Для універсальності ми вже звернулися за ім'ям осередку, а, по її індексу, тобто. 0, а це найперше значення в Recordset, в результаті ми отримали значення "Травень".

Як бачите, все досить просто. Якщо Вам досить часто потрібно набувати конкретного значення з бази ( як в останньому прикладі), то рекомендую вивести весь код в окрему функцію (Як написати функцію на VBA Access 2003) з одним вхідним параметром, наприклад код місяця ( якщо розглядати наш приклад) і просто де необхідно вивести це значення, викликати потрібну нам функцію з потрібним параметром і все, цим ми значно зменшимо код VBA і покращимо сприйняття нашої програми.

На сьогодні це все. Успіхів!