Sql запрос на содержание строки access vba. Как получить данные из запроса SQL в Microsoft Access VBA? Запросы к базе INSERT, UPDATE, DELETE на 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 заявления от него
vba excel-vba ms-access-2007 adodb excel

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

Но я хочу получить доступ к запросу, который я сохранил в базе данных доступа. Итак, как мне вызвать сохраненный запрос в базе данных, которую я только что подключил.

Пробовал уже

  1. con.Execute ("EXEC myQuery"), но тот сказал мне, что не может быть найти myQuery.
  2. 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-запросов, а также строки, служащие значениями свойств форм, отчетов и т.д., параметров функций и т.д., которые тоже должны соответствовать синтаксису 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 = "" 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" ) & "#));"
    Примечание:

  • Microsoft JET SQL оперирует датами в американском формате, т.е. именно в вышеуказанном виде Месяц/День/Год.
  • Не пропустите символы # (он обрамляет всю константу типа дата-время) и \ (он предохраняет / и : от их замены в соответствии с региональными настройками, что имеет обыкновение делать функция Format и что мешает правильной работе команды SQL).
  • Рекомендуется почитать здесь: о способах хранения даты/времени.
  • NB! Не стоит пользоваться преобразованием даты в Integer (или Long), т.к. в Access"е и в SQL Server"е одной и той же дате соответствуют разные числа и при сравнении можно получить неожиданный результат.

    Составив такой стринг, содержащий команду SQL, и отправив его на выполнение, вы можете получить ошибку. В таком случае распечатайте этот стринг в отладочное окно и посмотрите на него глазами. Возможно, ошибка сразу бросится в глаза. Если не бросится, создайте новый запрос в конструкторе запросов, переключите в режим SQL, вставьте туда текст запроса и запустите на выполнение. Если ошибка есть, то она будет показана более явным образом.