Выгрузка данных из access в шаблон word и excel

Initial Considerations

Compounded from client requirements over the years, I’ve identified these are the main features of an export-to-Excel capability:

• Automated export of data sets from queries
• Export directed to workbooks in folders of the client’s choice
• Automated formatting of worksheets on completion of an export
• The possibility of users creating their own queries.

All DMW databases supplied to clients are split — Front End (FE) and Back End (BE). FEs contain queries, forms, reports, macros and modules, and, as appropriate, a table or two, as I’ll explain below.

BEs contain tables only.

From the viewpoint of a developer, these are considerations:

• Users not to be allowed to modify the structure of the BE
• Users not to be allowed to alter the FE
• Provision for users to create their own queries
• Provision for incorporation of users’ own queries into FE
• Give suitable control of the process to the client’s IT.

Were users to be allowed to tinker, it would be an impractical and time-consuming task for me to provide upgrades and on-gong support to the client. And, the client would not be happy with the bill.

Выгрузка таблиц из 1С

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

Например, вам нужно получить всю номенклатуру с видом «Материалы» при этом нужны только колонки «Номенклатура» и «Единица».

В том случае, когда необходимо выгрузить данные с их фильтрацией, первым делом, необходимо настроить список в меню «Ещё» табличной части.

В появившемся окне из левой части (доступные поля) необходимо выбрать те, по которым вы будете делать отбор (в данном случае «Вид номенклатуры»). В правой части окна устанавливаются значения параметров отбора. При этом вид сравнения не ограничивается одним лишь равенством.

После того, как был установлен обор, в форме списка справочника «Номенклатура» будут отображаться лишь те позиции, которые подходят под заданное условие.

Следующим шагом будет вывод данного списка в табличный документ.

В меню «Еще» выберите пункт «Вывести список».

Если вам нужно вывести не все колонки, как в нашем примере, то установите «галочки» только на тех, которые необходимы.

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

Репозиторий (UserRepository)

Сам репозиторий довольно простой для начала я добавил три публичных свойства, чтобы был доступ к самой базе SQL Server через объект типа DataBase. Добавил свойство RecordSet для работы с ним внутри метода GetAll. В принципе сам класс UserRepositroy можно запускать независимо, но для реализации шаблона я инициализирую репозиторий в классе UnitOfWork.

И далее два основных метода. Метод GetAll и метод Add. Метод GetAll просто получает все записи из базы данных и переводит их в объект коллекцию типа User c помощью встроенного объекта RecordSet. Внутри метода я запускаю SQL запрос на выборку нужных мне полей. А далее через объект RecorSet получаю их.

Метод Add добавляет объект типа User в глобальную переменную LocalUsers с ней мы будем работать через класс UnitOfWork, чтобы сохранить данные в базе.

Импортировать данные из базы данных Access в Excel

Простой ответ на этот вопрос — твердое «да», и, к счастью для вас, вы собирались обсудить, как выполнить работу наилучшим и легким способом. Имейте в виду, что мы используем Access и Excel из Microsoft Office 365, поэтому, если вы используете более старую версию, все может работать иначе. Итак, откройте электронную таблицу Excel и:

1. Выберите меню данных
2. Далее нажмите на Получить данные
3. Выбирать Из базы данных
4. Нажмите на Из базы данных Microsoft Access
5. Добавьте информацию о Access в Excel.

Давайте поговорим об этом более подробно.

Выберите меню данных

Первое, что вам нужно сделать здесь, это убедиться, что ваш файл Microsoft Access сохранен на жестком диске вашего компьютера. Оттуда запустите Microsoft Excel и обязательно щелкните раздел ленты с надписью «Данные».

Очень важно, чтобы вы нажимали «Данные» и ничего больше, поэтому имейте это в виду, прежде чем двигаться дальше

Нажмите Из базы данных Microsoft Access.

Итак, следующее, что вам нужно сделать здесь, это выбрать, откуда вы хотите собирать данные. Поскольку мы говорим о Microsoft Access, вам необходимо будет щелкнуть «Получить данные», как показано на изображении выше.

Сделав этот выбор, нажмите «Из базы данных», затем «Из базы данных Microsoft Access», расположенную в раскрывающемся меню.

Добавьте информацию о Access в Excel

Как только вы сделаете то, что мы изложили выше, вы должны увидеть небольшое окно в Microsoft Excel. Используйте его, чтобы найти и выбрать файл Access, и сразу после открытия появится другое окно. Обязательно щелкните одну из таблиц в левой части окна, а затем нажмите кнопку ниже с надписью «Загрузить».

Когда это будет сделано, информация должна автоматически появиться в вашем документе Excel. Оттуда продолжайте и сохраните его для использования в будущем.

Импорт и экспорт таблиц Excel в базах данных Access

Интеграция Access 2010 с другими приложениями осуществляется с помощью вкладки Внешние данные ленты. Здесь присутствуют области Импорт и Экспорт. Обмен информацией осуществляется с БД Access, электронными таблицами Excel, текстовыми и XML-файлами, возможен экспорт в PDF и XPS, отправка по электронной почте. При щелчке на кнопке Дополнительно появляется выпадающее меню, позволяющее работать с форматами HTML, dBASE, распределенными базами данных, а также с источниками данных ODBC.

Для экспорта в офисные форматы Word и Excel щелкаем на соответствующей кнопке на меню и указываем местонахождение будущего файла. Вариант окна для экспорта в электронные таблицы MS Excel представлен на рис. 1. На втором шаге экспорта имеется возможность сохранить текущий экспорт для последующего выполнения этой операции. Сохраненная операция вызывается пиктограммой Сохраненные операции экспорта. Аналогично можно поступать и с операциями импорта. Установив соответствующий флажок, можно инициировать открытие электронной таблицы непосредственно после экспорта. Экспорт в другие форматы выполняется аналогично и никаких затруднений не вызывает.

Рис. 1. Экспорт в Excel. Выбор места

Импорт из всех файлов внешних по отношению к Access форматов выполняется только потаблично, то есть каждая таблица должна быть импортирована (и экспортирована) отдельно. Исключение составляют только базы данных самого Access — в этом случае можно указать сразу все таблицы, которые необходимо импортировать.

Рассмотрим пример импорта файлов формата XLS. Щелкнув на соответствующей пиктограмме области Импортировать и связать (рис. 2), мы увидим окно, аналогичное изображенному на рис. 1.

Файл можно выбрать для импорта, для добавления данных в существующую таблицу, а также для связывания таблицы, как внешней. В последнем случае набор действий с таблицей ограничен. В частности, изменение данных связанной таблицы из Access невозможно. Выбрав файл и щелкнув на кнопке ОК, расположенной справа внизу, увидим диалоговое окно, в котором можно уточнить область импорта листы рабочей книги. (листы) или именованные диапазоны.

Рис. 2. Область Импортировать и связать

Ход дальнейшего импорта не зависит от того, что импортируется — диапазон или рабочий лист. В нижней части окна представлен макет будущей таблицы базы данных. Выбрав объект и щелкнув на кнопке Далее, перейдите в окно, в котором можно задать заголовки полей. Если первая строка списка действительно содержит заголовки, установите флажок Первая строка содержит заголовки столбцов и щелкните на кнопке Далее.

Иногда на этом этапе появляется сообщение о неполном соответствии первой строки строке имей. Это означает, что в первой строке что-то не так — отсутствует запись, поле имеет не текстовый формат и т. п. Может быть, правее или левее списка имеются какие-то данные, на которые список был автоматически расширен, и в этом случае заголовок столбца по попятной причине отсутствует. При появлении такого сообщения имеет смысл прервать импорт и просмотреть таблицу в Excel внимательнее. Впрочем, если структура таблицы и данные понятны и очевидны, можно продолжить импорт п внести все необходимые исправления уже в таблице MS Access.

Следующее диалоговое окно позволяет сохранить данные в новой или существующей таблице. Выбирая уже существующую таблицу, следует убедиться в том, что ее структура полностью соответствует структуре импортируемой таблицы. То же касается и добавления данных в существующую таблицу. Например, MS Excel «считает» формат числовых полей двойным с плавающей точкой, соответственно, все числовые поля существующей таблицы должны быть именно такого формата.

Вероятно, в общем случае правильнее импортировать таблицу как новую, подправить форматы и уже потом добавлять данные в существующую таблицу, хотя обычно Access правильно преобразует форматы.

голоса

Рейтинг статьи

Экспорт данных из Access в шаблон Word

Вся разработка делится на две части, это:

• Настройка шаблона Word;
• Настройка выгрузки данных в шаблон.

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

Примечание! Я использую Microsoft Word 2003.

Открываем шаблон Word, для начала добавим необходимую панель инструментов, для этого нажимаем «Вид -> Панель инструментов» и ставим галочку «Формы». Теперь у Вас отобразилась панель инструментом «Формы». Все, что осталось сделать — это вставить в местах, в которых необходимо выводить данные, элементы «Текстовое поле», которые доступны на только что добавленной панели инструментов.

После добавления поля, у Вас появится серая область, которая свидетельствует о том, что поле добавлено. Теперь необходимо задать имя этого поля, для того чтобы потом из access вставлять в него значения (стандартное названия не очень удобное). Для этого щелкните правой кнопкой мыши по полю и нажмите «Свойства». В поле закладка напишите желаемое имя этого поля, я в примере назвал его MyTestPole.

Создайте столько полей, сколько Вам нужно.

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

Переходим к более интересной задачи, это к реализации самой выгрузки из Access в этот шаблон на VBA.

Примечание! Я использую Access в связке с MS SQL 2008, поэтому и данные буду брать от туда.

Код VBA для выгрузки данных в шаблон Word

Допустим, у Вас есть форма, сделайте на ней кнопку (я назвал ее testbutton) и в событие нажатие кнопки вставьте следующий код VBA:

   
   Private Sub testbutton_Click()
   
   'Объявляем переменные
   Dim FileDialog As FileDialog
   Dim rsd As ADODB.Recordset
   Dim strSQL As String
   Dim WordApOb As Object
   Dim WordOb As Object
   Dim path As String
   Set rsd = New ADODB.Recordset
   
   'запрос к базе данных для получения необходимых данных
   strSQL = "select * from dbo.table where KOD = " & Me.kod & ""
   rsd.open strSQL, CurrentProject.Connection
  
  'Выбираем шаблон
   Set FileDialog = Application.FileDialog(msoFileDialogOpen)
   
   'убираем множественный выбор, он нам не нужен
   FileDialog.AllowMultiSelect = False
  
  'очистим и установим фильтры
   FileDialog.Filters.Clear
   FileDialog.Filters.add "Word", "*.doc"
   
   'установим фильтр по умолчанию
   FileDialog.FilterIndex = 1
   
   'проверяем, что сделал user, если выбрал шаблон, то начинаем работу
   If FileDialog.Show = False Then
     'Если нет, то выходим
     Set dlgFile = Nothing
     Exit Sub
   End If
   
   'получаем путь к файлу
   path = Trim(FileDialog.SelectedItems(1))
   
   'Очистим переменную
   Set FileDialog = Nothing
   If path <> "" Then
   
   'Будем отслеживать ошибки
   On Error GoTo Err_testbutton_Click
   
   'Создаем объект Word
   Set WordOb = CreateObject("Word.document")
   
   'Задаем нашему документу значение из шаблона
   Set WordOb = GetObject(path)
   
   'Задаем значение объекту word.Application
   Set WordApOb = WordOb.Parent
   
   'делаем приложение word видимым
   WordApOb.Visible = True
   
   'ищем наше поле в шаблоне
   WordOb.Bookmarks("mytestpole").Select
   
   'задаем ему новое значение из нашего Recordset
   WordApOb.Selection.TypeText Text:=Nz(rsd.Fields("field").Value, " ")
   'и так далее по всем полям
   
   'в конце перейдем на начало нашего документа
   WordApOb.Selection.Goto wdGoToFirst
   'и активируем его
   WordApOb.Activate
   
   'Очистим переменные
   Set WordOb = Nothing
   Set WordApOb = Nothing
   
   Exit_testbutton_Click:
     Exit Sub
   
   Err_testbutton_Click:
     MsgBox Err.Description
     'в случае ошибки будем делать следующие
     'закроем word без сохранения
     WordOb.Close (wddonotsavechanges)
     WordApOb.Quit
    'и также очистим переменные
     Set WordOb = Nothing
     Set WordApOb = Nothing
     Resume Exit_testbutton_Click
    End If
   
   End Sub

Код прокомментирован, поэтому сложностей возникнуть не должно. Здесь весь смысл сводится к созданию объекта word.document и word.application. А после мы уже работаем с нашими объектами, т.е. заполняем их.

The Controlling Procedure

This is the sequence of routines that the controlling program, named sExportData, will call:

fnGetPathFromKey This function subroutine will look for KEY.ini and get the export path from it. If fnGetPathFromKey cannot locate KEY.ini, or is unable the find the information about the export path, then it will return an error message to sExportData.

fnCheckPath This function subroutine will seek to confirm the existence of the folder to which dumps are to be directed. If fnCheckPath cannot locate that folder it will report the folder as missing to sExportData.

fnExportToExcel This will complete the export program passing data from Access and into Excel, the opening Excel to display the data, and the formatting of the worksheet.

Метасимволы оператора LIKE

Самый распространенный метасимвол — %
. Он означает любые символы. Например, если нам надо найти слова,
начинающиеся с букв «вел», то мы напишем LIKE «вел%», а если мы хотим найти слова, которые содержат символы «клуб», то мы
напишем LIKE «%клуб%». Например:

Еще один часто используемый метасимвол — _
. В отличие от %, который обозначает несколько или ни одного символа,
нижнее подчеркивание обозначает ровно один символ. Например:

Обратите внимание на пробел между метасимволом и «рыб», если его пропустить, то запрос не сработает, т.к. метасимвол _

обозначает ровно один символ, а пробел — это тоже символ

На сегодня достаточно. В следующем уроке мы научимся составлять запросы к двум и более таблицам. А пока попробуйте
самостоятельно составить запросы к таблице posts (сообщения).

Данный урок посвящен SQL запросам
к базе данных на VBA Access
. Мы рассмотрим, как на VBA осуществляется запросы INSERT, UPDATE, DELETE к базе данных, а также научимся получать конкретное значение из запроса SELECT.

Те, кто программируют на VBA Access
и работая при этом с базой данных SQL сервера, очень часто сталкиваются с такой простой и нужной задачей как посыл SQL запроса к базе данных, будь то INSERT, UPDATE или простой SQL запрос SELECT . А так как мы начинающие программисты мы тоже должны уметь это делать, поэтому сегодня займемся именно этим.

Мы уже затрагивали тему получения данных с SQL сервера, где как раз на VBA писали код для получения этих данных, например в статье про Выгрузку данных в текстовый файл из MSSql 2008 или также немного затрагивали в материале Выгрузка данных из Access в шаблон Word и Excel , но так или иначе там мы рассматривали это поверхностно, а сегодня предлагаю поговорить об этом чуть более подробней.

Базы данных и 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 используются практически в каждой программе.

У коллекции 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 не получается.

голоса

Рейтинг статьи

The Supports Method of the Recordset Object

   <source lang="vb">

Sub SupportsMethod()

   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   
   rst.ActiveConnection = CurrentProject.Connection
   rst.CursorType = adOpenStatic
   rst.LockType = adLockOptimistic
   rst.CursorLocation = adUseServer
   rst.Open Source:="Select * from Employees ", _
       Options:=adCmdText
   Debug.Print "Bookmark " & rst.Supports(adBookmark)
   Debug.Print "Update Batch " & rst.Supports(adUpdateBatch)
   Debug.Print "Move Previous " & rst.Supports(adMovePrevious)
   Debug.Print "Seek " & rst.Supports(adSeek)
   rst.Close
   Set rst = Nothing

End Sub

</source>
   
  

Язык sql: where условие

Пример БД «Институт»:
Выводить данные преподавателя из таблицы teachers , фамилия которого Иванов

Between в SQL (между)

Пример БД «Институт»:
Вывести фамилию и зарплату преподавателя, зарплата которого между 5000 и 10000.

Пример БД «Институт»:
Вывести фамилию и зарплату преподавателя, зарплата которого не находится в диапазоне от 5000 до 10000.

Предикат IN

Пример БД «Институт»:
вывести имена преподавателей, зарплата которых составляет 5000 , 10000 или 11000

Пример БД «Институт»:
вывести имена преподавателей, зарплата которых не находится среди значений: 5000 , 10000 или 11000

Подготовка к экспорту

Прежде чем выполнять операцию экспорта, следует проверить данные, которые будут экспортироваться, на предмет сообщений об ошибках и ошибочных значений. Ошибки рекомендуется устранить до экспорта данных в Excel. В противном случае в ходе экспорта могут возникнуть неполадки, а в ячейки листа Excel могут быть вставлены пустые значения. Дополнительные сведения о проблемах, которые могут возникнуть при экспорте данных в Excel, см. в разделе Решение проблем, связанных с отсутствующими и неверными значениями.

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

Export to Excel Subroutine

My clients most like to see these actions performed by the export program:

• Formatting of column headings
• Application of a currency format to columns, where appropriate
• Application of a date format to columns, where appropriate
• Adjustment of columns widths to suit content
• Appropriate naming of the worksheet’s tab.

The third and final subroutine in the program is fnExportToWorkbook. This routine contains lines of code aimed at smartening up the contents of the worksheet created by the export:

Function fnExportToWorkbook( _

query$, path$, _

fileName$, wksName$, _

colsCurrency$, colsDate$ _

) As String

On Error GoTo errHandler

Dim xlApp As Object, wkbk As Object, wks As Object

Dim file$

Dim formatCur$, formatDate$, intColor&

Dim arrayCols() As String, col$, n%, i%, w!

Dim cell As Range

Dim msg$

‘ Worksheet formats

formatCur$ = «£#,##0.00»

formatDate$ = «yyyy-mm-dd»

intColor& = RGB(100, 200, 200)

‘ Create workbook

file$ = path$ & fileName$

DoCmd.TransferSpreadsheet _

TransferType:=acExport, _

SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _

TableName:=query$, _

FileName:=file$, _

HasFieldNames:=True

‘ Open workbook

Set xlApp = CreateObject(«Excel.Application»)

With xlApp

.Visible = True

Set wkbk = .Workbooks.Open(file$)

End With

‘ Format worksheet

Set wks = wkbk.worksheets(1)

With wks

.Name = wksName$

‘ Currency columns

arrayCols = Split(colsCurrency$, «,»)

For i = LBound(arrayCols) To UBound(arrayCols)

With .Columns(arrayCols(i))

.NumberFormat = formatCur$

End With

Next i

‘ Date columns

arrayCols = Split(colsDate$, «,»)

For i = LBound(arrayCols) To UBound(arrayCols)

With .Columns(arrayCols(i))

.NumberFormat = formatDate$

End With

Next i

‘ Filters

With .Range(«A1»)

.Select

.autofilter

End With

‘ Column width adjustments

With .Cells

.Select

.EntireColumn.AutoFit

End With

n% = .Cells(1, 1).End(xlToRight).Column

For i% = 1 To n%

With .Cells(1, i%)

w! = .EntireColumn.ColumnWidth

.EntireColumn.ColumnWidth = w! + 4

.HorizontalAlignment = xlCenter

.Interior.Color = intColor&

.Font.Bold = True

End With

Next i%

End With

With xlApp.ActiveWindow

.SplitColumn = 0

.SplitRow = 1

.FreezePanes = True

End With

msg$ = vbNullString

procDone:

Set wks = Nothing

Set wkbk = Nothing

Set xlApp = Nothing

fnExportToWorkbook = msg$

Exit Function

errHandler:

msg$ = _

Err.Number & «: » & Err.Description

Resume procDone

End Function

Build a string text from result set

   <source lang="vb">

Sub MyFirstConnection4()

   Dim myConnection As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strSQL As String
   Dim strOutput As String
  
   strSQL = "SELECT FirstName, LastName FROM Employees"
   
   Set myConnection = CurrentProject.Connection
   
   Set myRecordset = New ADODB.Recordset
   myRecordset.Open strSQL, myConnection
   
   Do Until myRecordset.EOF
      strOutput = strOutput + myRecordset.Fields("FirstName") & " " & _
                              myRecordset.Fields("LastName") & vbCrLf
      myRecordset.MoveNext
   Loop
   myRecordset.Close
   msgBox strOutput
   myConnection.Close
   Set myConnection = Nothing
   Set myRecordset = Nothing

End Sub

</source>
   
  

Замечания

Свойство Recordset (Набор записей) возвращает объект набора записей, который является источником данных, просматриваемых в форме, отчете, списке или поле со списком. Если форма основана, например, на запросе, ссылка на свойство Recordset эквивалентна клонированию объекта Recordset с помощью того же запроса. Однако в отличие от использования свойства RecordsetClone (Копия набора записей), назначение в качестве текущей другой записи в наборе записей, возвращаемом свойством Recordset формы, также задает текущую запись этой формы.

Это свойство можно задать только с помощью кода Visual Basic для приложений (VBA).

Доступность для чтения и записи свойства Recordset определяется типом набора записей (ADO или DAO) и типом данных (Access или SQL), которые содержатся в нем.

Чтение и запись

Чтение и запись

Чтение и запись

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

Global rstSuppliers As ADODB.RecordsetSub MakeRW()DoCmd.OpenForm «Suppliers»Set rstSuppliers = New ADODB.RecordsetrstSuppliers.CursorLocation = adUseClientrstSuppliers.Open «Select * From Suppliers», _CurrentProject.Connection, adOpenKeyset, adLockOptimisticSet Forms(«Suppliers»).Recordset = rstSuppliersEnd Sub

Свойство Набор записей служит для следующих целей:

Связывание нескольких форм с общим набором данных. Это позволяет синхронизировать несколько форм. Например:

Set Me.Recordset = Forms!Form1.Recordset

Использование методов, не имеющих непосредственной поддержки в формах, вместе с объектом Recordset. Например, свойство Recordset можно использовать вместе с методами ADO Find или DAO Find в пользовательском диалоговом окне для поиска записи.

Добавление в транзакцию (для которой можно выполнить откат) набора изменений, влияющих на несколько форм.

Изменение свойства Recordset также может привести к изменению свойств RecordSource (Источник записей), RecordsetType (Тип набора записей) и RecordLocks (Блокировка записей). Кроме того, некоторые связанные с данными свойства могут быть переопределены, например свойства Filter (Фильтр), FilterOn (Фильтр включен), OrderBy (Порядок сортировки) и OrderByOn (Сортировка включена).

Вызов метода Requery набора записей формы (например, Forms(0).Recordset.Requery ) может привести к превращению формы в свободную. Чтобы обновить данные в форме, привязанной к набору записей, следует задать для свойства RecordSource (Источник записей) формы само это свойство ( Forms(0).RecordSource = Forms(0).RecordSource ).

Если форма привязана к набору записей, при использовании команды «Фильтр по форме» возникает ошибка.

Check Your References

Thanks to Marek, who on 2020-10-14 commented wisely on an ommission from my article at the time:

“Just in case this is of any help. I’m looking at your Export to Excel procedures on .

“I’m not sure if it would be helpful to add the need to add the reference to the Microsoft Excel Object Library as my version of Access was missing this.

“Kind regards and thank you for sharing your code.”

In your code, Access is making calls to Excel. For it to work you need to tell Access that you’re using Excel. This is how you tell Access:

1. Go into the Visual Basic Editor in which you’ve written your code
2. From the Tools menu, choose References to display the References dialog box –

If Microsoft Excel ##.# Object Library is not included in your list of ticked references, scroll down the list and tick it there
Click OK
From the Debug menu, choose Compact.

The fifth action checks whether or not Access has got the hang of things.

SQL Server Data Import to Excel using ADO

Function ImportSQLtoRange

The function inserts SQL Server data to the target Excel range using ADO.

Function ImportSQLtoRange(conString As String, query As String, target As Range) As Integer

    On Error Resume Next

    ' Object type and CreateObject function are used instead of ADODB.Connection,
    ' ADODB.Command for late binding without reference to
    ' Microsoft ActiveX Data Objects 2.x Library

    ' ADO API Reference
    ' https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/ado-api-reference?view=sql-server-ver16

    ' Dim con As ADODB.Connection
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")

    con.ConnectionString = conString

    ' Dim cmd As ADODB.Command
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")

    cmd.CommandText = query
    cmd.CommandType = 1         ' adCmdText

    ' The Open method doesn't actually establish a connection to the server
    ' until a Recordset is opened on the Connection object
    con.Open
    cmd.ActiveConnection = con

    ' Dim rst As ADODB.Recordset
    Dim rst As Object
    Set rst = cmd.Execute

    If rst Is Nothing Then
        con.Close
        Set con = Nothing

        ImportSQLtoRange = 1
        Exit Function
    End If

    Dim ws As Worksheet
    Dim col As Integer

    Set ws = target.Worksheet

    ' Column Names
    For col = 0 To rst.Fields.Count - 1
        ws.Cells(target.row, target.Column + col).Value = rst.Fields(col).Name
    Next
    ws.Range(ws.Cells(target.row, target.Column), _
        ws.Cells(target.row, target.Column + rst.Fields.Count)).Font.Bold = True

    ' Data from Recordset
    ws.Cells(target.row + 1, target.Column).CopyFromRecordset rst

    rst.Close
    con.Close

    Set rst = Nothing
    Set cmd = Nothing
    Set con = Nothing

    ImportSQLtoRange = 0

End Function

Code comments:

• The query parameter can contain a SELECT or EXECUTE query.
• The resulting data will be inserted starting from the top left cell of the target range.
• Using Object types and the CreateObject function instead of direct use of ADO types
  lets to avoid setting ActiveX Data Objects 2.x Library references on user computers.
  This code works in Microsoft Excel 2003-2016.
• Always use Set Nothing statements for ADODB.Connection and ADODB.Recordset objects to free resources.

Test Code of SQL Server Data Import to Excel using ADO

Sub TestImportUsingADO()

    Dim conString As String
    conString = GetTestConnectionString()

    Dim query As String
    query = GetTestQuery()

    Dim target As Range
    Set target = ThisWorkbook.Sheets(2).Cells(3, 2)

    target.CurrentRegion.Clear

    Select Case ImportSQLtoRange(conString, query, target)
        Case 1
            MsgBox "Import database data error", vbCritical
        Case Else
    End Select

End Sub

Adding Missing DAO and ADO References

If either DAO or ADO is missing we need to add them! To do this we need the References dialog box which can be opened by clicking on References in the Tools drop-down menu.

Adding DAO References

To set the DAO reference find the references below (they are dependent on your version of Access).

Microsoft Office 12.0 Access Database Engine Objects Library (Access 2007)

Microsoft Office 14.0 Access Database Engine Objects Library (Access 2010)

Microsoft Office 15.0 Access Database Engine Objects Library (Access 2013)

Adding ADO References

To set the ADO reference find the references below (they are dependent on your version of Access).

Microsoft ActiveX Data Objects 6.0 Library (Access 2007)

Microsoft ActiveX Data Objects 6.1 Library (Access 2010/2013)

Работа с БД Access через VBA Excel

Суть проекта: необходим макрос в Excel, который бы добавлял/изменял строки в таблице Access. Пока что получается только добавлять новые данные в таблицу с помощью следующего кода (найдено на просторах гугла):

Код открывает базу данных, находит нужную таблицу и вставляет данные с листа Excel в соответствующие поля в Access.

Теперь мне, например, нужно найти какую-то запись с определенным значением в поле «ФИО» и внести изменения в найденную строчку (например, изменить номер договора).

Подскажите, пожалуйста, как это сделать?

Импорт таблицы Excel в БД Access через VBA через кнопку в Excel

Подключение к БД access через VBA excelPrivate Sub CommandButton1_Click() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset.

Сохранение Excel-файла через макрос VBA в Access

Как установить пароль в Access через VBA Excel?Доброго времени суток! Есть Excel’евская программы которая подключается к БД Access, все работает.