Выполнить запрос из Access через Excel Query в VBA. Как послать запрос к базе на VBA Access Запуск запроса в access из excel 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 не может быть параметризованы.
Аксесс позволяет создавать программно строки 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, вставьте туда текст запроса и запустите на выполнение. Если ошибка есть, то она будет показана более явным образом.
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
