Recordset vba access

Замечания
Объект Recordset представляет набор записей в основной таблице или набор записей, который получается в результате выполнения запроса.
Объекты Recordset используются для обработки данных в базе данных на уровне записи. При работе с объектами доступа к данным (DAO) почти все операции выполняются с помощью объектов Recordset. Каждый объект Recordset состоит из записей (строк) и полей (столбцов). Существуют объекты Recordset пяти типов:

Объект Recordset типа таблицы — программное представление основной таблицы; используется для добавления, изменения или удаления записей из отдельной таблицы базы данных (только в рабочей области Microsoft Jet).
Объект Recordset типа динамического набора записей — набор обновляемых записей, полученный в результате выполнения запроса. Объект Recordset типа динамического набора записей позволяет добавлять, изменять или удалять записи в основной таблице или таблицах. В объекте Recordset этого типа динамического набора могут содержаться поля из одной или нескольких таблиц базы данных. Этот тип объектов соответствует указателю ключевого набора записей ODBC.
Объект Recordset типа статического набора записей — статическая копия набора записей, используемая для поиска данных или создания отчетов. Объект Recordset типа статического набора записей может содержать поля из одной или нескольких таблиц базы данных, но не допускает обновления полей. Этот тип объектов соответствует указателю статического набора записей ODBC.
Объект Recordset типа статического набора записей с последовательным доступом — аналогичен статическому набору записей с тем лишь исключением, что в нем отсутствует указатель. Пользователь имеет возможность прокручивать записи только вперед. Это повышает быстродействие в ситуациях, когда достаточен однократный проход по набору записей. Этот тип объектов соответствует указателю набора записей с последовательным доступом ODBC.
Объект Recordset динамического типа результирующий набор записей запроса по одной или нескольким основным таблицам, в котором допускается добавление, изменение или удаление записей. В таком объекте Recordset отображаются записи, добавляемые, удаляемые или изменяемые в основных таблицах другими пользователями. Этот тип объектов соответствует динамическому указателю ODBC (только в рабочей области ODBCDirect).

При создании нового объекта Recordset тип объекта определяется с помощью аргумента тип метода OpenRecordset.
В рабочей области Microsoft Jet, если аргумент тип не задан, механизм DAO пытается создать тип объекта Recordset с максимально возможными функциональными характеристиками, начиная с таблицы. Если таблицу создать не удается, делается попытка создать динамический набор записей, затем статический набор записей и наконец, набор записей с последовательным доступом.
В рабочей области ODBCDirect, если аргумент тип не задан, механизм DAO пытается создать тип объекта Recordset, обеспечивающий максимально быстрое выполнение запроса, начиная с набора записей с последовательным доступом. Если объект этого типа создать невозможно, механизм DAO делает попытку создать статический набор записей, затем динамический набор записей, и наконец, объект Recordset динамического типа.
При создании объекта Recordset на основе неприсоединенного объекта TableDef в рабочей области Microsoft Jet создаются объекты Recordset типа таблицы. На основе присоединенных таблиц или таблиц в базах данных ODBC, подключенных к ядру Microsoft Jet, могут быть созданы только объекты Recordset типа динамического набора записей или статического набора записей.
Новый объект Recordset автоматически добавляется в семейство Recordsets при открытии объекта и автоматически удаляется из семейства при закрытии объекта.
При описании в программе объекта Recordset и содержащего его объекта Database с помощью объектных переменных необходимо обеспечить, чтобы эти переменные имели одинаковую область определения или одинаковое время жизни. Например, при описании общей переменной, представляющей объект Recordset, необходимо обеспечить, чтобы переменная, представляющая объект Database, содержащий объект Recordset, также была общей, либо описать эту переменную в процедуре Sub или Function с ключевым словом Static.
В приложении допускается определение произвольного числа объектных переменных, представляющих объект Recordset. Разные объекты Recordset могут иметь доступ к одним таблицам, запросам и полям без возникновения конфликтов.
Объекты Recordset типа динамического набора записей, статического набора записей и статического набора записей с последовательным доступом сохраняются в локальной оперативной памяти. Если в оперативной памяти не хватает места, ядро базы данных Microsoft Jet сохраняет избыточные данные в каталоге временных файлов на диске. Если и этот объем окажется недостаточным, возникает перехватываемая ошибка.
Объекты Recordset по умолчанию помещаются в семейство Fields. Используемым по умолчанию свойством объекта Field является свойство Value (значение). Эти используемые по умолчанию характеристики позволяют упростить программу.
Если созданный объект Recordset содержит записи, то текущей записью становится первая запись. Если записи в объекте отсутствуют, свойство RecordCount получает значение 0, а свойства BOF и EOF значение True.
Изменить положение указателя текущей записи позволяют методы MoveNext, MovePrevious, MoveFirst и MoveLast. Для объектов Recordset типа статического набора записей с последовательным доступом поддерживается только метод MoveNext. При выполнении цикла по всем записям с помощью методов Move («прохода» по объекту Recordset) свойства BOF и EOF используются для проверки начальной и конечной границы объекта Recordset.
Для объектов Recordset типа динамического статического набора записей в рабочей области Microsoft Jet допускается также использование методов группы Find, таких как FindFirst, позволяющих найти запись, удовлетворяющую определенным условиям. Если запись не обнаруживается, свойство NoMatch получает значение True. Для объектов Recordset типа таблицы допускается сканирование записей с помощью метода Seek.
Тип созданного объекта Recordset задается свойством Type, а возможность изменения записей в объекте определяется свойством Updatable.
Сведения о структуре основной таблицы, такие как имя и тип данных каждого поля (объект Field) и индекса (объект Index) сохраняются в объекте TableDef.
При ссылках на объект Recordset используют его порядковый номер в семействе или значение свойства Name (имя):
Для одного источника данных или базы данных допускается открытие нескольких объектов Recordset, что приводит к образованию повторяющихся имен в семействе Recordsets. В этом случае необходимо присваивать объекты Recordset объектным переменным и ссылаться на них по именам переменных.

Читать еще: Access select case

Пример
Следующая программа открывает объект Recordset типа статического набора записей с последовательным доступом, демонстрирует его доступность только для чтения, а затем осуществляет проход по объекту Recordset с помощью метода MoveNext.

9.5.3 Перемещение по Recordset

Перемещение по Recordset в ADO/VBA, свойства BOF, EOF, Bookmark, методы Move(), MoveNext(), MovePrevious(), MoveFirst(), MoveLast(), Find(), Seek()

После того, как объект Recordset создан, нам необходимо выполнять с ним различные операции. Самое простое действие, с которого мы начнем — перемещение по объекту Recordset.

В Recordset всегда имеется ограниченное количество записей (столько, сколько мы получили с источника). Изначально курсор устанавливается на первую запись в Recordset (убедиться в этом можно при помощи свойства AbsolutePosition). Однако, если мы дадим команду MovePrevious() (один раз), ошибки не произойдет (если мы попробуем выполнить команду MovePrevious() второй раз, то возникнет ошибка). AbsolutePosition вернет загадочное значение (-2). Связано это с тем, в Recordset перед первой записью, полученной с источника помещается специальная запись BOF (от Begin Of File, хотя никаких файлов, конечно же, нет). Проверить, находимся ли мы на этой специальной записи, можно при помощи свойства BOF. Например, такой код:

вернет нам вначале False, а затем True.

Точно так же после последней записи в Recordset находится специальная запись EOF (End Of File). Проверить, не находится ли курсор на ней, можно при помощи аналогичного одноименного свойства EOF.

Иногда бывает так, что сразу после открытия Recordset и BOF, и EOF одновременно возвращают True. Объяснение такой ситуации очень простое — в Recordset с источника по каким-то причинам не вернулось ни одной записи. Рекомендуется во избежание неожиданностей предусматривать сразу после открытия Recordset проверку на наличие в нем записей.

После того, как мы определились с нашей текущей позицией в Recordset, необходимо разобраться с тем, как можно по нему перемещаться. Проще всего это делать при помощи методов Move…(). Вот их краткое описание:

Move() — этот метод принимает два параметра: NumRecords — на сколько записей необходимо переместиться (это число может быть и отрицательным, что значит — переместиться назад) и второй параметр — необязательный — имя закладки, с которой нужно начать перемещение. Можно использовать три встроенные закладки: для текущей, первой и последней записи. Если имя закладки не указано, то перемещение начинается с текущей позиции.
MoveFirst(), MoveLast(), MoveNext() и MovePrevious() — назначение этих методов понятно из названия: перемещение на первую, последнюю, следующую и предыдущую запись соответственно.

Необходимо отметить, что перемещение назад (при помощи MovePrevious() или Move() с отрицательным значением) для курсора, открытого как Forward-only, может привести к совершенно непредсказуемому результату (в зависимости от источника данных) — от ошибки до перехода на случайную запись.

Чаще всего для прохода по всем записям используется такой нехитрый алгоритм:

‘что-то делаем с каждой записью, например,

‘получаем значение нужного поля

Если нужно напрямую перепрыгнуть на нужную запись, можно использовать методы Find() и Seek().

Метод Find() предназначен для поиска по значению одного столбца. Он принимает в качестве параметра критерий поиска, насколько нужно отступить от исходной позиции, направление поиска и откуда нужно начать поиск. Очень удобно, что при определения критерия поиска можно использовать оператор Like с подстановочными символами. При обнаружении нужной записи метод Find() переставляет курсор на найденную запись, если же запись не обнаружена, то курсор устанавливается на EOF (или BOF, если поиск был назад). Например, чтобы найти все немецкие фирмы в нашем Recordset для таблицы Customers, можно использовать код вида

rs.Find «country = ‘Germany'»

Do While Not rs.EOF

Debug.Print «Название фирмы: «; rs.Fields(«CompanyName»)

rs.Find «country = ‘Germany'», 1, adSearchForward, mark

В этом примере используются еще незнакомые нам объекты Fields и Bookmark, но их назначение понятно: объект Field нужен нам, чтобы вывести название фирмы, а объект Bookmark — чтобы продолжить поиск позиции +1 от последней найденной записи.

Метод Seek() отличается от метода Find() тем, что они ищет значение по индексу (объект Index для Recordset создается либо программным способом, либо автоматически — если на таблицу, на основе который был создан Recordset, было наложено ограничение Primary Key). Этот метод работает только для серверных курсоров с типом команды TableDirect, и поэтому к использованию не рекомендуется.

Теперь — о еще одном свойстве, которое может сильно помочь в перемещении по Recordset (и которое уже встречалось в наших примерах) — свойстве Bookmark. Это свойство очень простое — достаточно присвоить его значение переменной типа Variable, когда указатель стоит в нужном месте Recordset, а затем присвоить этому свойству значение этой переменной, чтобы опять на него вернуться — как в нашем примере с поиском.

Вообще говоря, значение, которое возвращает это свойство, изначально совпадает с номером записи в Recordset, однако Microsoft честно предупреждает, что таким способом пользоваться закладкой очень не рекомендуется — если курсор стоит в одном и том же месте, свойство Bookmark может возвращать разные значения.

Базы данных и ADO Работа с полями объекта Recordset в ADO/VBA

9.5.4 Коллекция Fields и объекты Field

Работа с полями объекта Recordset в ADO/VBA, коллекция Fields и объекты Field, свойства Name и Value

Главное содержание Recordset — это то, что лежит в ячейках на пересечении строк (в Recordset они называются записями — records и представлены соответствующими объектами Record) и столбцов. В Recordset столбцы называются полями — объектами Field, которые сведены в коллекцию Fields. Объекты Record используются нечасто — поскольку имен у них нет, а переходить между записями проще при помощи свойств и методов самого объекта Recordset — AbsolutePosition, Find(), Move() и т.п. Коллекция же Fields и объекты Field используются практически в каждой программе.

Читать еще: Access select into

У коллекции Fields все свойства стандартные, как у каждого объекта Collection:

Count — сколько всего столбцов в Recordset
Item — возможность вернуть нужный столбец (объект Field) по имени или номеру. Поскольку это свойство является свойством по умолчанию, то можно использовать код, как в нашем примере: rs.Fields(«CompanyName»). Есть еще один вариант синтаксиса для обращения к этому свойству:

Методы же имеются как стандартные, так и специфические:

Append() — возможность добавить новый столбец в Recordset. Delete() — соответственно, удалить столбец. Обе команды разрешено выполнять только на закрытом Recordset (пока не был вызван метод Open() или установлено свойство ActiveConnection).
Update() — сохранить изменения, внесенные в Recordset (будет произведена попытка создать новый столбец на источнике данных, если источник данных по каким-то причинам принимать эти изменения отказался, возникнет ошибка), CancelUpdate() — отменить изменения, внесенные в Recordset.
Refresh() — загадочный метод, который ничего не делает (о чем честно написано в документации). Обновить структуру Recordset данными с источника можно только методами самого объекта Recordset.
Resync() — работает только для коллекции Fields объекта Record (не Recordset), обновляя значения в строке.

Намного больше интересных свойств у объекта Field:

ActualSize — реальный размер данных для текущей записи, DefinedSize — номинальный размер данных для столбца (в байтах), в соответствии с полученной с источника информацией.
Attributes — возможность определить битовую маску для атрибутов столбца (допускает ли пустые значения, можно ли использовать отрицательные значения, можно ли обновлять, используется ли тип данных фиксированной длины и т.п.)
Name — просто строковое имя столбца. Для столбцов, полученных с источника, доступно только на чтение.
NumericScale и Precision — значения, которые определяют соответственно допустимое количество знаков после запятой и общее максимальное количество цифр, которое можно использовать для представления значения.
Value — самое важное свойство объекта Field. Определяет значение, которое есть в столбце (если мы пришли через коллекцию Fields объекта Record, то для этой записи, если через Fields объекта Recordset — то для текущей записи). Пример применения — в нашей строке для поиска. Доступно и на чтение, и на запись (в зависимости от типа указателя). ADO позволяет работать с большими двоичными данными (изображения, документы, архивы), что очень удобно. OriginalValue — значение, которое было в этом столбце до начала изменений, UnderlyingValue — значение, которое находится на источнике данных (пока мы работали с Recordset, оно могло быть изменено другой транзакцией, и поэтому OriginalValue и UnderlyingValue могут не совпадать). Свойство Value — это свойство по умолчанию, поэтому эти две строки равноценны:

Status — значение, отличное от adFieldOK (значение 0) означает, что поле было недавно программно добавлено в Recordset или при добавлении возникла ошибка на источнике данных.
Type — тип данных в соответствии с приведенной в документации таблицей. Например, для типа данных nvarchar возвращается 202.

У объекта Field есть только два метода — AppendChunk() и GetChunk(). Оба этих метода используются только для работы с большими двоичными типами данных (изображениями, документами и т.п.), когда работать обычными способами через свойство Value не получается.

Как в Access на VBA выгрузить Recordset в Excel?

Всем привет! Материал сегодня будет посвящен рассмотрению возможности Access выгружать Recordset в Excel на VBA. Данный способ достаточно простой и предполагает использование Recordset как формы, так и специально созданного объекта.

Ранее мы с Вами уже рассматривали возможность выгрузки данных из Access в Excel в материале «Выгрузка данных из Access в шаблон Word и Excel», но там мы использовали специально созданный шаблон, что не совсем удобно, если например, нам необходимо выгрузить просто набор данных с заголовками полей.

Также если кого интересует, недавно мы рассматривали возможность выгрузки данных из базы MS SQL Server в формат CSV (текстовый файл с разделителями) с помощью VBA Access в материале «Экспорт данных в CSV файл из Microsoft SQL Server, используя Access 2003».

Сейчас я покажу простой пример реализации возможности выгружать наборы данных с заголовками из базы MS SQL Server средствами VBA Access в Excel.

Весь смысл сводится в использование метода CopyFromRecordset, который позволяет переносить Recordset в Excel в том виде, какой он и есть, т.е. в виде таблицы. Единственное что нам необходимо будет сделать, это выгрузить заголовки, чтобы было понятней, что за данные содержатся в той или иной колонке.

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

Примечание! В качестве примера источником данных у меня будет выступать MS SQL Server 2012 Express, а в качестве клиента ADP проект Access 2003. Также на компьютере клиенте установлен Microsoft Office 2010.

Исходные данные

Допустим, на сервере у нас есть таблица TestTable.

Читать еще: Count в access

И она содержит следующие данные.

Также допустим, что в ADP проекте Access у нас есть форма, источником данных которой выступает наша тестовая таблица TestTable.

Код VBA для выгрузки Recordset формы в Excel

Сначала давайте рассмотрим пример выгрузки объекта Recordset формы в Excel. Для этого добавляем на форму кнопку, для примера я ее назвал RSExportInExcel. В обработку события нажатие кнопки вставляем следующий код, я его прокомментировал:

Сохраняем изменения и пробуем нажать на кнопку. В итоге у нас запустится Excel, а в нем будут необходимые нам данные.

Примечание! Свойства HorizontalAlignment и VerticalAlignment могут не работать, если на компьютере не установлен Microsoft Office 2010, поэтому в случае возникновения ошибок связанных с этими свойствами просто закомментируйте их.

Код VBA для выгрузки объекта Recordset в Excel

Теперь давайте напишем код, который позволяет выгружать объект Recordset, данные которого получены, скажем с помощью запроса к базе данных. Для этого добавьте еще одну кнопку (я ее назвал RSExportInExcel2) и вставьте немного модифицированный код:

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

На этом у меня все! Надеюсь, материал был Вам полезен, пока!