Использование «Запроса к серверу» MS Access для извлечения данных с нескольких серверов SQL Server
На нашем предприятии несколько десятков удаленных территориально от головного офиса представительств, в каждом из которых имеется база с данными о клиентах каждого представительства. Исторические данные есть и в центральном офисе, но актуальные находятся только в представительстве. Каждая БД на MS SQL Server 2005. Изредка возникает необходимость получить актуальную информацию по всем представительствам в виде таблицы Excel. Например, получить что-то вроде:
select territory, count(customerid) from customer where managerid = 1000До недавнего момента я тупо в MS SQL Server Management Studio менял вручную connect, прогонял скрипт на выборку данных для каждого удаленного офиса и копировал результаты на лист Экселя. Но как-то выдалась свободное время, и я решил автоматизировать процесс сбора данных с удаленных БД, используя старый добрый MS Access.
MS Access имеет такой интересный инструмент, как “Запрос к серверу”, который отправляет sql-инструкцию напрямую серверу базы данных ODBC. Таким образом для любого сервера БД, — SQL Server, Progress OpenEdge, Oracle, — к которому у нас имеется ODBC-драйвер, мы можем создать в Акцессе “Запрос к серверу” на родном для этого сервера диалекте sql.
Таким образом мне осталось в цикле по числу удаленных офисов сменить настройки ODBC-подключения, запустить запрос и вернуть результат в табличку Акцесса. Что для этого было сделано.
Я создал в MS Access табличку с реквизитами подключения для каждого офиса t_ServerList, табличку t0 – приемник результатов запроса для каждого офиса и написал на VBA проход по объекту ADO Recordset для сбора строки ODBC-подключения и “складированию” данных из “Запрос к серверу”с именем queryName в табличку Access t0. В случае возникновения ошибки (не факт, что каждый локальный сервер будет работать во время прогона), в таблицу t_ServerList пишется код ошибки и описание.
Ниже, собственно, сам код:
Option Compare Database Public Const queryName As String = "q0" Public Connection As ADODB.Connection, Recordset As ADODB.Recordset Sub MkPassTruQueryDefSel() 'Руками поменять в свойствах запроса Возврат записей - ДА 'В цикле по таблице серверов t_ServerList создается pass-thtough запрос и данные с выбранного сервера/базы, используя строку запроса strSql 'инсертятся в заранее созданную таблицу акцесс соотв-й корректной структуры 'Таблица с именем t0 должна быть создана заранее 'Пишется дата прохода в таблицу со списком серверов Dim strSql As String, strCon As String, serverListTableName As String, strServer As String, strDB As String Dim errAdo As ADODB.Error, strErrAdoMsg As String Dim qrdef As Dao.QueryDef, db As Dao.Database 'Текст запроса к серверу вбит руками в конструкторе запроса Set db = CurrentDb On Error Resume Next Set qrdef = db.QueryDefs(queryName) Dim strSQLFillTable As String strSQLFillTable = "insert into t0 select * from " & queryName 'Запрос на заполнение таблицы очередной порцией Set Connection = CurrentProject.Connection Set Recordset = New ADODB.Recordset serverListTableName = "t_ServerList" With Recordset 'Каждый проход из рекордсета берутся атрибуты соединения для pass-thtough запроса 'Запрос к исходной таблице со списком серверов src = "select * from " & serverListTableName & " where use=-1" .Open src, Connection, adOpenDynamic, adLockOptimistic .MoveFirst Do While (.EOF = False) strServer = .Fields("ip"): strDB = .Fields("DB") qrdef.Connect = GetTPQConString(strServer, strDB) CurrentDb.QueryDefs.Refresh CurrentProject.Connection.Execute strSQLFillTable .Fields("UseDateTime") = Now 'Запись метки времени использования .Fields("errNumber") = Err.Number : .Fields("errString") = Err.Description: Err.Clear .MoveNext Loop .Close End With Set Recordset = Nothing : Connection.Close : Set Connection = Nothing Call ShowOkMessage End Sub Public Function GetTPQConString(myServerAddress As String, myDataBase As String) As String 'Строка подключения для "запроса к серверу" GetTPQConString = "ODBC; DRIVER=SQL Server; Server=" & myServerAddress & _ ";Database=" & myDataBase & _ ";UID=odmin;PWD=pwd;" End Function Запрос на выборку дат в MS ACCESS
Подскажите пожалуйста как в MS ACCESS с помощью sql запроса создать запрос на выборку дат относительно сегодняшней даты? Например есть таблица контакты. В ней есть атрибут дата_рождения. И нам надо сделать запрос который выведет поля у которых день рождения будет в течении следующих двух дней, относительно сегодняшней даты. (в общем наподобие этой штукенции в контакте) 
Отслеживать
user177221
задан 30 мар 2016 в 7:17
93 1 1 серебряный знак 12 12 бронзовых знаков
В каком виде храните даты в базе? Какой тип? Приведите фрагмент из дизайнера таблицы с датами, а также желательно фрагмент данных.
30 мар 2016 в 7:22
Еще раз повторяю, Вам никто не сможет помочь, пока не покажете в каком виде храните даты в базе? Хоть сам файл выложите.
30 мар 2016 в 11:33
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
SELECT * FROM [Контакты] WHERE [дата_рождения] IS NOT NULL AND DATEADD("yyyy", DATEDIFF("yyyy", [дата_рождения], DATE()), [дата_рождения]) BETWEEN DATE() AND DATEADD("d", 2, DATE()) Отслеживать
ответ дан 30 мар 2016 в 7:25
531 2 2 серебряных знака 10 10 бронзовых знаков
«Несоответствие типов данных в выражении условия отбора»
30 мар 2016 в 10:01
Пока возникает только подозрение на то, что [дата_рождения] имеет тип отличный от дата. Проверить пока что нет возможности .
30 мар 2016 в 10:05
Ответ уже нашел, но с типом там все верно.
30 мар 2016 в 14:03
Проверил в ACCESS. Конструкция «DATEADD(«yyyy», DATEDIFF(«yyyy», [дата_рождения], DATE()), [дата_рождения]) BETWEEN DATE() AND DATEADD(«d», 2, DATE())» ошибок не выдает.
31 мар 2016 в 1:57
А можете кинуть в качестве примера свою бд. Не могу понять почему ругается на тип данных.
31 мар 2016 в 6:51
SELECT my_date FROM MyTable WHERE my_date >= #2016-03-30 00:00:00# AND my_date < #2016-04-02 00:00:00#;
Отслеживать
ответ дан 30 мар 2016 в 7:23
user3195373 user3195373
415 2 2 серебряных знака 6 6 бронзовых знаков
Вообще ничего не выводит(
30 мар 2016 в 9:59
Всем спасибо, но помог мне такой код: SELECT Дата_рождения FROM контактные_лица WHERE Дата_рождения Between DATE() And DATEADD("d",2,DATE());
Отслеживать
ответ дан 30 мар 2016 в 13:13
93 1 1 серебряный знак 12 12 бронзовых знаков
Это был первый вариант, который я вам написал забыв подумать. Но он не должен работать правильно, так как на момент сейчас он будет выбирать только тех, кто родился с 31.03.2016 по 2.04.2016 (то есть, никого). Если человек родился, например, 01.04.1990, то, в соответствии с вашим условием, он в выборку не попадет. Поэтому в моем ответе и появилась конструкция, которая преобразует дату из 01.04.1990 в 01.04.2016. Хотя, если ваш запрос каким то магическим образом все таки работает правильно, то я вас с этим поздравляю.
Запрос с параметром
Запрос с параметром — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле.
Можно разработать запрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами.
Пример
- Создайте запрос в режиме Конструктора на основе таблицы «Töötaja» (поля: Nimi, Perekonnanimi) и таблицы «Amet» ( поле: Nimetus).
- Чтобы определить параметр запроса, введите в строку Criteria (Условие отбора) для столбца «Amet» (должность) вместо конкретного значения слово или фразу и заключите их в квадратные скобки, например [Sisesta amet] Введи должность. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.
3. Если вы хотите, чтобы Access проверяла данные, вводимые в качестве параметра запроса, нужно указать тип данных для этого параметра. Обычно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text). Если же данные в поле запроса представляют собой даты или числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой кнопкой мыши на свободном поле в верхней части запроса и выберите кнопку
(Параметры) Появляется диалоговое окно Query Parameters (Параметры запроса).
4. В столбец Параметр (Parameter) нужно ввести название параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования), только можно не вводить квадратные скобки. В столбце Data Type (Тип данных) выберите из раскрывающегося списка необходимый тип данных. Нажмите кнопку ОК.
5. Нажмите кнопку (Запуск), чтобы выполнить запрос. При выполнении запроса появляется диалоговое окно Введите значение параметра (Enter Parameter Value),
в которое нужно ввести значение, например õpetaja. В результате выполнения запроса мы увидем только те имена и фамилии, чья должность — учитель.
В одном запросе можно ввести несколько параметров. При выполнении такого запроса для каждого из параметров будут поочередно выводиться диалоговые окна Введите значение параметра в том порядке, в котором параметры перечислены в бланке запроса.
Как сделать запрос sql в access
Запись: xintrea/mytetra_db_adgaver_new/master/base/1563334714t3wbbnui58/text.html на raw.githubusercontent.com
Выполнение SQL запросов к текущей базе данных в среде VBA (Access)
В Microsoft Office Access поддерживается выполнение SQL запросов к текущей базе данных из программного кода на VBA. Рассмотрим, как это можно использовать при разработке приложений баз данных.
Т ак как запрос выполняется к той же самой базе данный в которой выполняется модуль VBA, для выполнения SQL запроса нет необходимости работать со стандартными в подобных случаях интерфейсами ADO, ODBC и т.д. Всё можно сделать при помощи объектной модели Access.
Как известно SQL запросы можно условно разделить на две большие группы:
- Запросы, не возвращающие данных (INSERT, UPDATE, DELETE и т.д.);
- Запросы возвращающие данные (запросы на выборку (SELECT)).
Запросы , не возвращающие данных
Для выполнения запросов к текущей базе данных служит метод Execute объекта CurrentDb. Этот метод принимает в качестве параметра строку с SQL запросом , который необходимо выполнить.
Dim sql As String sql = "DELETE FROM Table1" CurrentDb.Execute sql
Dim sql As String
sql = "DELETE FROM Table1"
Это простейший запрос. Но , как быть с запросами , которые манипулируют теми или иными данными ?
Такие запросы тоже можно выполнить. Для этого нужно просто сформировать соответствующую строку.
Так , например , может быть реализовано добавление записей в таблицу :
Dim field1Value As String Dim field2Value As String Dim sql As String field1Value = "test1" field2Value = "test2" sql = "INSERT INTO Table1 (field1, field2) VALUES ('" & field1Value & "','" & field2Value & "')" CurrentDb.Execute sql
Dim field1Value As String
Dim field2Value As String
Dim sql As String
sql = "INSERT INTO Table1 (field1, field2) VALUES ('" & field1Value & "','" & field2Value & "')"
А , так их обновление :
Dim id As Integer Dim field2Value As String Dim sql As String id = 1 field2Value = "test2 variant 2" sql = "UPDATE Table1 SET field2 = '" & field2Value & "' WHERE > & id CurrentDb.Execute sql
Dim id As Integer
Dim field2Value As String
Dim sql As String
field2Value = "test2 variant 2"
sql = "UPDATE Table1 SET field2 = '" & field2Value & "' WHERE > & id
Выполнение другие типов запросов не возвращающих данных осуществляется по аналогичным принципам.
Запросы на выборку
Метод Execute объекта CurrentDb не поддерживает выполнение запросов на выборку. Поэтому для них нужен другой подход.
Чтобы работать с выборкой данных необходимо воспользоваться объектом RecordSet , который можно создать при помощи метода OpenRecordSet объекта CurrentDb. Этот метод также принимает в качестве параметра строку с SQL запросом.
После создания RecordSet мы можем перебрать все записи , как это показано ниже.
' Создаём RecordSet для запроса Dim sql As String sql = "SELECT * FROM Table1" Dim record_set As Recordset Set record_set = CurrentDb.OpenRecordset(sql) ' Перебираем одну за одной записи в ранее созданном Recordset Do Until record_set.EOF ' Выводим данные из записи в окно сообщения Dim recordString As String recordString = record_set("id").Value & " " & record_set("field1").Value & " " & record_set("field2").Value Dim dialog_result As Integer dialog_result = MsgBox(recordString, vbOKOnly, "Test") record_set.MoveNext Loop
' Создаём RecordSet для запроса
Dim sql As String
sql = "SELECT * FROM Table1"
Dim record_set As Recordset
Set record_set = CurrentDb.OpenRecordset ( sql )
' Перебираем одну за одной записи в ранее созданном Recordset
Do Until record_set.EOF
' Выводим данные из записи в окно сообщения
Dim recordString As String
recordString = record_set ( "id" ). Value & " " & record_set ( "field1" ). Value & " " & record_set ( "field2" ). Value
Dim dialog_result As Integer
dialog_result = MsgBox ( recordString , vbOKOnly , "Test" )
Помимо простого перебора доступны несколько видов поиска. Но в основном используются два.
Первый вид поиска реализован в виде методов FindFirst , FindLast , FindNext и FindPrevious объекта RecordSet.
Первые два устанавливают курсор на соответственно первую и последнюю записи , соответствующие условию поиска. Условие поиска передаётся в качестве параметра в виде строки. Само условие задаётся по аналогии с предложением WHERE в обычном SQL запросе.
Методы FindNext и FindPrevious устанавливают курсор на следующую и предыдущую записи в соответствии с условием поиска , если такие записи существуют. Условие поиска для этих методов задаётся также , как и для FindFirst и FindLast.
Обратите внимание , что эти четыре метода именно устанавливают указатель на нужную запись , а не производят фильтрацию выборки. Таким образом количество записей выбранных SQL запросом остаётся неизменным.
В следующем примере перебор записей начнётся с записи , у которой id равен 234 , так как курсор был перемещён к ней до начала обхода набора записей в цикле.
' Создаём RecordSet для запроса Dim sql As String sql = "SELECT * FROM Table1" Dim record_set As Recordset Set record_set = CurrentDb.OpenRecordset(sql) record_set.FindFirst = "id = 234" ' Перебираем одну за одной записи в ранее созданном Recordset Do Until record_set.EOF ' Выводим данные из записи в окно сообщения Dim recordString As String recordString = record_set("id").Value & " " & record_set("field1").Value & " " & record_set("field2").Value Dim dialog_result As Integer dialog_result = MsgBox(recordString, vbOKOnly, "Test") record_set.MoveNext Loop
' Создаём RecordSet для запроса
Dim sql As String
sql = "SELECT * FROM Table1"
Dim record_set As Recordset
Set record_set = CurrentDb.OpenRecordset ( sql )
record_set.FindFirst = "id = 234"
' Перебираем одну за одной записи в ранее созданном Recordset
Do Until record_set.EOF
' Выводим данные из записи в окно сообщения
Dim recordString As String
recordString = record_set ( "id" ). Value & " " & record_set ( "field1" ). Value & " " & record_set ( "field2" ). Value
Dim dialog_result As Integer
dialog_result = MsgBox ( recordString , vbOKOnly , "Test" )
Второй вид поиска можно задействовать при помощи свойства Filter того же объекта RecordSet. Условие поиска здесь также задаётся аналогично предложению WHERE в обычном SQL запросе.
Отличие данного вида поиска от предыдущего состоит в том , что в наборе данных остаются только записи , соответствующие условию поиска. Если их нет , результирующий набор данных будет пуст.
К сожалению , отбор по свойству Filter не выполнятся автоматически. После изменения этого свойства. Необходимо создать новый объект RecordSet при помощи метода OpenRecordSet ( без параметров ), который уже будет содержать результаты отбора.
В приведённом ниже примере в RecordSet будет только одна запись ( у которой id равен 234 ).
Dim sql As String sql = "SELECT * FROM Table1" Dim record_set As RecordSet Set record_set = CurrentDb.OpenRecordset ( sql ) record_set.Filter = "id = 2" Dim filtered_record_set As Recordset Set filtered_record_set = record_set.OpenRecordset Do Until filtered_record_set.EOF Dim filteredRecordString As String filteredRecordString = filtered_record_set ( "id" ). Value & " " & filtered_record_set ( "field1" ). Value & " " & filtered_record_set ( "field2" ). Value Dim filtered_dialog_result As Integer filtered_dialog_result = MsgBox ( filteredRecordString , vbOKOnly , "Test" ) filtered_record_set.MoveNext Loop
Dim sql As String
sql = "SELECT * FROM Table1"
Dim record_set As RecordSet
Set record_set = CurrentDb.OpenRecordset ( sql )
record_set.Filter = "id = 2"
Dim filtered_record_set As Recordset
Set filtered_record_set = record_set.OpenRecordset
Do Until filtered_record_set.EOF
Dim filteredRecordString As String
filteredRecordString = filtered_record_set ( "id" ). Value & " " & filtered_record_set ( "field1" ). Value & " " & filtered_record_set ( "field2" ). Value
Dim filtered_dialog_result As Integer
filtered_dialog_result = MsgBox ( filteredRecordString , vbOKOnly , "Test" )
Как мы видим механизмы для работы с запросами на выборку в VBA не очень удобны. Но, при разработке баз данных Access со сложной бизнес-логикой или сложным интерфейсом пользователя нужно быть готовым их применить.
В принципе возможностей методов Execute и OpenRecordset объекта CurrentDb достаточно для практически любых манипуляций с базой данных, но всё же стоит применять их разумно не пренебрегая и «визуальными» средствами Access.
- Полезные ссылки
- Создание объектов баз данных средствами VBA
- Делимся наработками
- Проектирование с Microsoft Access2016Оглавление
- Шаг 159 - Метод CreateField
- Код VBA для импорта данных из текстового файла
- Чтение mdb на VBA
- Восстановление удалённой таблицы Access
- Как запускать запросы из VBA
- Нормально РАБОТАЮЩИЙ диалог открытия и сохранения файла в VB Access
- Вызов диалога для выбора папки в Access (VBA)
- Вызов диалога для выбора файла в Access (VBA)
- ОГРАНИЧЕНИЕ ПО ДАТЕ
- Добавление_одной_записи в таблицу
- Считывание записи из базы по условию(DLOOKUP)
- Проверка наличия таблицы\запроса в базе
- Прилинковка текстовый и Excell таблиц
- Рекордсет
- Объектная модель (справочник по VBA для Access)
- Удаление записей с помощью VBA
- VBA Excel - Access 2010: проверка существования записи в Recordset
- Дата в SQL, при исполльзовании национальных региональных настроек существует проблема
- Формирование строки SQL-запроса в VBA
- Что мы знаем про каунтеры
- Как запускать запросы из VBA
- Access « VBA / Excel / Access / Word
- Outlook « VBA / Excel / Access / Word
- File Path « VBA / Excel / Access / Word
- Forms « VBA / Excel / Access / Word
- Data Type Functions « VBA / Excel / Access / Word
- '==================== Date Functions « VBA / Excel / Access / Word
- Excel « VBA / Excel / Access / Word
- Спецификации Access 2016
- indexMenu
- Импорт из текста
- Opening a Text File with ADO
- OpenRecordset.xlsm
- DAO.Recordset
- Функция InputBox Access
- SQL Date Functions
- Получить список таблиц в БД Access
- Получить список таблиц БД Access
- Статус бар Access
- ADO Connection Strings
- Как опознать связанную таблицу Access
- Массив из таблицы
- Управление свойствами поля. Сложность в том, что нужного свойства у поля может не быть, тогда его нужно сначала добавить. К примеру, так (DAO):
- Microsoft Access. Работа с таблицами из VBA
- TableDef.CreateField Method (DAO)
- Многозадачность в коде Access Basic
- Календарики для ввода дат
- Программное скрытие/восстановление и сворачивание/разворачивание ленты Ribbon (Access 2010 и выше).
- Access. Переход с 32-х разрядной версии офиса на 64-х разрядную.
- Программный модуль, облегчающий подключение таблиц из внешних баз
- Получить списки таблиц, запросов, форм, отчётов, макросов, модулей
- "массовый" поиск неиспользуемых запросов.
- Программное создание таблицы и ее полей в MS Access 2003
- Проверка существования таблицы средствами SQL! [new]
- FAQ: Проверка наличия таблицы в базе данных.
- Проверка существования таблицы БД MS Access
- Как вывести список всех таблиц текущей базы?
- izБорей
- Метод Application. DLookup (Access)
- Выполнение SQL запросов к текущей базе данных в среде VBA (Access)
- Джентльменский набор разработчика Access
- StatusBar
- Как определить список всех столбцов в таблице Access
- Шаг 122 - Метод GetRows