Использование excel vba для экспорта данных в таблицу ms access

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

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

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

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

Открытие и закрытие баз данных

Прежде чем вводить данные или иным образом изменять свои объекты, вам нужно будет открыть свою базу данных. После того, как вы внесете изменения в свою базу данных, вам нужно будет знать, как ее закрыть.

Как открыть существующую базу данных:

1.Перейдите на вкладку «Файл». Это приведет вас к представлению Backstage.

2.Выберите «Открыть». Появится диалоговое окно.

3.Найдите и выберите нужную базу данных, затем нажмите «Открыть».

4.При открытии базы данных может появиться одно или несколько предупреждающих сообщений.

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

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

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

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

Как закрыть базу данных:

1.Перейдите на вкладку «Файл», чтобы перейти в «Вид заставки».

2.Выберите «Закрыть базу данных».

3. Если у вас есть не сохраненные объекты, появится диалоговое окно для каждого, спрашивающего, хотите ли вы сохранить его. Выберите «Да», чтобы сохранить объект, «Нет», чтобы закрыть его без сохранения, или «Отмена», чтобы открыть базу данных.

Экспорт данных в Excel: основы

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

Стандартные сценарии экспорта данных в Excel

В отделе или рабочей группе для работы с данными используется как Access, так и Excel. Данные хранятся в базах данных Access, но для анализа и распространения его результатов используется Excel. Группа выполняет экспорт данных в Excel по мере необходимости, но этот процесс требуется сделать более эффективным.

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

Об экспорте данных в Excel

В приложении Access нет команды «Сохранить как” с возможностью сохранения данных в формате Excel. Чтобы скопировать данные в Excel, необходимо воспользоваться функций экспорта, описанной в этой статье, либо скопировать данные Access в буфер обмена, а затем вставить их в таблицу Excel.

Можно экспортировать таблицу, запрос, форму или отчет, а также отдельные записи в представлении.

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

Экспортировать макросы и модули нельзя. При экспорте формы, отчета или таблицы с подчиненными формами, отчетами или таблицами экспортируется только главная форма, отчет или таблица. Все подчиненные формы, отчеты и таблицы следует экспортировать в Excel отдельно.

В ходе операции экспорта можно экспортировать лишь один объект базы данных. Однако после нескольких операций экспорта можно выполнить в Excel слияние нескольких листов.

Access 2013: Краткое руководство по началу работы + ВИДЕО

Интерфейс Microsoft Access 2013 изменился по сравнению с предыдущими версиями, и чтобы помочь вам быстрее освоиться с ним, мы представляем вашему вниманию это руководство.

Измените размер окна или закройте базу данных: Щелкните значок Access, чтобы изменить размер окна или переместить его, либо закрыть базу данных.
Используйте элементы управления, расположенные на панели быстрого доступа: Вы можете быстро добавить или изменить элементы управления в форме или отчете, щелкнув значок инструментов, когда форма или отчет открыты в конструкторе или режиме макета.
Обращайтесь к справке: Чтобы открыть содержимое справки, щелкните вопросительный знак.
Скрывайте ленту: Чтобы скрыть ленту, оставив на виду только имена вкладок, щелкните стрелку, направленную вверх. Открывайте диалоговые окна: Чтобы открыть дополнительные параметры группы, нажмите кнопку вызова диалогового окна. Кнопки режимов: Вы можете переключиться в любой из режимов, доступных для текущего объекта, щелкнув соответствующий значок.
Кнопки вызова диалоговых окон: Если в правом нижнем углу группы команд на ленте отображается такой значок, вы можете щелкнуть его, чтобы открыть окно с дополнительными параметрами.
Строка состояния: Здесь отображается информация о текущем режиме.
Применяйте к объектам фильтры: Введите в поле поиска ключевое слово, чтобы отфильтровать объекты в базе данных.
Управление файлами: Открывайте, закрывайте и печатайте файлы, делитесь ими и сохраняйте их в форматах предыдущих версий или в виде шаблонов. Вы также можете сжать и восстановить базу данных или зашифровать ее.

Интерфейс Microsoft Access 2013 изменился по сравнению с предыдущими версиями

Вкладка «Учетная запись»

В Access 2013 на вкладке Файл выберите пункт Учетная запись. Здесь вы сможете изменить сведения о пользователе, выбрать другой фон или тему, просмотреть список доступных служб и добавить новые, отобразить сведения о своих продуктах Office и управлять подписками на них.

Вкладка «Учетная запись»

Куда исчезла команда «Создать резервную копию»

Создавать резервные копии важных данных всегда полезно. Чтобы сделать это в Access 2013, на вкладке Файл выберите команду Сохранить как. Затем в разделе Сохранить базу данных как → Дополнительно выберите команду Резервная копия базы данных.

Куда исчезла команда «Создать резервную копию»

Действия, которые вам могут потребоваться

Из приведенной ниже таблицы вы узнаете, где найти некоторые наиболее часто используемые инструменты и команды в Access 2013.

Импорт данных из Excel документа в MSSql 2008

Создаем тестовые данные в документе Excel

Мы будем использовать старый, но проверенный Excel 2003 и формат файла у нас будет xls.

Данные будут вот такие, файл я назвал test_file.xls:

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

Примечание! Сервер располагается локально, база данных называется test.

Итак, приступим, у нас есть файл, теперь запускаем средство импорта, и у нас открывается следующее окно:

Шаг 2

Нажимаем далее, где нам предлагают выбрать источник данных, в нашем случае это Excel, мы выбираем файл, версию excel и ставим галочку, что первая строка — это заголовок:

Шаг 3

Жмем далее, нам предлагают выбрать назначение, куда копировать эти данные, мы выбираем SQL Server, указываем имя сервера, т.е. его адрес, в нашем случае, как я уже сказал, он расположен локально. Также не забудьте про проверку подлинности, выбирайте тот метод, который у Вас настроен на сервере, и, конечно же, про базу данных, в которую копировать:

Снова жмем далее, где мы укажем все ли данные копировать, в нашем случае мы говорим что все:

Шаг 5

Жмем далее, и попадаем на окно выбора листа с данными и задания названия таблицы в нашей базе, я выбрал лист 1 и назвал таблицу test_table:

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

Шаг 6

Это было небольшое отступление, а в нашем примере мы жмем далее, и попадаем в окно, в котором можно сохранить все наше действия в пакет, но мы этого делать не будем, а сразу же нажмем готово:

После появится окно, где мы все проверяем и жмем готово:

Шаг 8

И в заключение у нас появится еще одно окно, так сказать результат наших действий, жмем закрыть:

Как видно, передано 3 строки, т.е. импортировано — это означает, что все наши данные, которые были в файле, импортировались.

И для того, чтобы проверить какие данные у нас импортировались, выполним в Management Studio простой запрос select.

И как видите все хорошо!

Какие разрешения необходимы? What permissions do I need?

Чтобы успешно запустить мастер импорта и экспорта служб SQL Server SQL Server , нужно иметь по крайней мере одно из указанных ниже разрешений. To run the SQL Server SQL Server Import and Export Wizard successfully, you have to have at least the following permissions. Если вы уже работаете с источником данных и назначением, вероятно, у вас уже есть нужные разрешения. If you already work with your data source and destination, you probably already have the permissions that you need.

Задачи, для выполнения которых требуются разрешения You need permissions to do these things	Разрешения, необходимые при подключении к SQL Server If you’re connecting to SQL Server, you need these specific permissions
Подключение к исходным и целевым базам данных, а также к общим папкам. Connect to the source and destination databases or file shares.	Права на вход в систему сервера и базы данных. Server and database login rights.
Экспорт или считывание данных из исходной базы данных или файла. Export or read data from the source database or file.	Разрешения SELECT на исходные таблицы и представления. SELECT permissions on the source tables and views.
Импорт или запись данных в целевую базу данных или файл. Import or write data to the destination database or file.	Разрешения INSERT для целевых таблиц. INSERT permissions on the destination tables.
Создание целевой базы данных или файла, если это применимо. Create the destination database or file, if applicable.	Разрешения CREATE DATABASE или CREATE TABLE. CREATE DATABASE or CREATE TABLE permissions.
Сохранение пакета служб SSIS, созданного с помощью мастера, если применимо. Save the SSIS package created by the wizard, if applicable.	Если вы хотите сохранить пакет в SQL Server SQL Server , достаточно разрешений для сохранения пакета в базу данных msdb . If you want to save the package to SQL Server SQL Server , permissions sufficient to save the package to the msdb database.

UnitOfWork

Давайте теперь посмотрим класс UnitOfWork

Данный класс в основном должен содержать только репозитории и метод Commit для сохранения данных в базе. И может содержать несколько вспомогательных членов.

В моем примере я добавил метод Init, чтобы через этот метод можно было внедрить объекты типа DataBase и Recordset. Т.к. в стандартном конструкторе на VBA нет возможности передать параметры. Такой механизм передачи объектов извне класса называется внедрение зависимостей.

Private pUserRepository As UserRepository

Public Function Init(DataBase As DataBase, Recordset As ADODB.Recordset)
    Set pUserRepository = New UserRepository
    Set pUserRepository.DataBase = DataBase
    Set pUserRepository.Recordset = Recordset
End Function

Давайте посмотрим класс DataBase. Сам класс DataBase еще проще всё что он делает это запускает соединение при каждой инициализации и открывает соединение.

Public Connection As New ADODB.Connection
Private pStr As String


Private Sub Class_Initialize()
    pStr = "Driver={SQL Server Native Client 11.0};Server=localhost;Database=TestDb;Trusted_Connection=yes;"
    Connection.ConnectionString = pStr
    If Connection.State = 0 Then
        Connection.Open
    End If
End Sub

Друзья я еще не рассказывал, как работать с базами через библиотеку ADODB. Если вы хотите более подробный урок по этой теме просто напишите мне или пролайкайет видео.

Замечания

Свойство 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 ).

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

Импорт данных из Excel (любые файлы) в Microsoft SQL Server x64

Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server

В данном случае мы также используем провайдер Microsoft.ACE.OLEDB.12.0, сначала проверяем, зарегистрирован ли он на сервере.

В случае, если провайдер не установлен, его необходимо скачать и установить.

Скачиваем файл x64.

Шаг 3 – Включаем распределенные запросы на SQL Server

Необходимость включения возможности использования распределенных запросов (OPENDATASOURCE и OPENROWSET) на Microsoft SQL Server x64 также есть, поэтому сначала включаем ее, выполнив точно такую же инструкцию.

Шаг 4 – Настройка провайдера Microsoft.ACE.OLEDB.12.0

В этом случае, скорей всего, настройка провайдера не потребуется, поэтому сначала сразу пробуем выполнить SQL запросы (обратиться к данным в Excel), и если возникает ошибка (все с тем же сообщением 7399 и 7330), то пробуем включить параметры AllowInProcess и DynamicParameters (для отключения укажите 0 вместо 1).

Шаг 5 – Выполняем SQL запрос, обращение к файлу Excel

Здесь используются точно такие же параметры в SQL запросах, что и в предыдущем примере. Для удобства продублирую их еще раз.

Примеры обращения к файлу Excel (TestExcel.xlsx).

OPENROWSET

OPENDATASOURCE

Linked Server

4 Пример создания приложения для автоматизации формирования договоров

Создание базы данных и необходимых таблиц:

Запускаем Microsoft Access и в меню
«Файл» выбераем «Создать». В окне «Создание
файла» выбераем «Новая база данных».
Сохраняем созданную базу данных. В данном
примере база данных будет находиться
в корневом каталоге диска C: и называться
Dogovors.mdb. В открывшемся окне базы данных
переходим на вкладку «Таблицы» и щелкаем
два раза мышью по строке «Создание таблицы
в режиме конструктора». В созданной таблице
определяем три столбца ( рис. 4.1).

Рисунок 4.1 – Структура таблицы «Шаблоны»

Сохраняем эту таблицу
как «Шаблоны» и закрываем
окно конструктора. В окне базы данных
на вкладке «Таблицы» еще раз
щелкаем по строке «Создание таблицы
в режиме конструктора». Набор столбцов
для новой таблицы должен выглядеть
так:

Имя столбца

Тип данных

Размер

НомерДоговора

Текстовый (первичный ключ)

Город

Текстовый

Дата

Дата/время

Организация

Текстовый

Представитель

Текстовый

Должность

Текстовый

ЮрОснование

Текстовый

100

Сохраняем эту таблицу
с именем «Договоры» и закрываем
окно конструктора таблицы.

На вкладке «Таблицы»
окна базы данных щелкнем два раза
мышью по созданной таблице «Шаблоны»,
чтобы открыть ее в режиме ввода
данных. В первую строку этой таблицы
в столбец «Описание» вводим «Шаблон
договора», а затем выделяем ячейку
в столбце «Шаблон» и в меню
«Вставка» выбираем «Объект». В открывшемся
окне переставляем переключатель в
положение «Создать из файла», затем
нажимаем на кнопку «Обзор» и выбираем
шаблон C:\ DogovorTemplate. dot. Затем нажимаем на
кнопку OK, чтобы поместить шаблон внутрь
базы данных.

Создание формы Access и программного кода для формирования файла договора:

В окне базы данных переходим
на вкладку «Формы» и щелкаем
два раза мышью по строке «Создание
формы с помощью мастера». Откроется
окно мастера создания форм. На первом
экране мастера в списке «Таблицы»
и «Запросы» выбираем «Таблица: Договоры»,
затем помещаем в список «Выбранные
поля» все поля из этой таблицы
и нажимаем на кнопку «Далее». На следующем
экране выбираем внешний вид формы
в один столбец и нажимаем на кнопку
«Далее». На следующем экране выбираем
стиль и нажимаем на кнопку «Далее».
На последнем экране в окне определения
имени формы вводим имя формы
«Форма для занесения договоров»,
переставляем переключатель в положение
«Изменить макет формы» и нажимаем
на кнопку «Готово». Форма будет
открыта в режиме конструктора. Произведем
расстановку и дооформление созданных
элементов на форме средствами конструктора.

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

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

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

Public LocalUsers As New Collection
Public DataBase As DataBase
Public Recordset As ADODB.Recordset

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

Public LocalUsers As New Collection
Public DataBase As DataBase
Public Recordset As ADODB.Recordset


Public Function GetAll() As Collection
    Dim selectAllQuery As String:
    selectAllQuery = "Select Id, Email, UserName from "
    
    If DataBase Is Nothing Then MsgBox "DataBase not found set it"
    If Recordset Is Nothing Then MsgBox "RecordSet not found set it"
    Recordset.Open selectAllQuery, DataBase.Connection
    
    Dim userCollection As New Collection
    Dim user As user
    With Recordset
        While (Not .EOF)
            Set user = New user
            
            user.Id = .Fields(0).Value
            user.Email = .Fields(1).Value
            user.UserName = .Fields(2).Value
            
            userCollection.Add user
            
            .MoveNext
        Wend
    End With
    
    Recordset.Close
    Set GetAll = userCollection
End Function

Public Function Add(user As user)
    LocalUsers.Add user
End Function

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

Обновление данных и именование диапазонов

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

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

При расширении диапазона внешних данных и возврате дополнительных записей Microsoft Excel может заполнить формулами соседние столбцы или столбцы внутри диапазона данных, чтобы они оставались рядом с соответствующими данными.

Также можно выбрать способ добавления новых данных на лист.

Microsoft Excel автоматически именует диапазоны внешних данных следующим образом.

Если лист имеет несколько диапазонов внешних данных из одного источника, они нумеруются. Например MyText, MyText_1, MyText_2 и так далее.

Кроме того, изменять названия диапазонов внешних данных можно в диалоговом окне Свойства диапазона данных.

MS Access — импорт данных.
Для импорта данных Excel в другие базы данных Microsoft Access, базы данных индексно-последовательного метода доступа (ISAM) или базы ODBC также могут использоваться конструкции, поддерживаемые поставщиком данных Jet.

«Вид -> Панель инструментов»«Формы»«Формы»«Текстовое поле»

Запрос SELECT к базе на VBA

Здесь дела обстоят чуть интересней, чем с остальными конструкциями SQL.

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

Здесь мы уже используем циклы VBA Access для того чтобы перебрать все значения в нашем наборе записей.

Но, достаточно часто бывает необходимо получить не все значения из набора записей, а всего лишь одно, например, название месяца по его коду. И для этого использовать цикл как-то накладно, поэтому мы можем просто написать запрос, который вернет всего одно значение и обращаться именно к нему, например, получим название месяца по коду 5

Для универсальности здесь мы уже обратились не по имени ячейки, а по ее индексу, т.е. 0, а это самое первое значение в Recordset, в итоге мы получили значение «Май».

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

Заметка! Если Вас интересует SQL и T-SQL, рекомендую пройти наши курсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Запросы к базе INSERT, UPDATE, DELETE на VBA

Чтобы долго не тянуть сразу приступим, допустим, нам нужно добавить строку в нашу тестовую таблицу (код прокомментирован
)/

Private Sub start_Click()
«Объявляем переменную для хранения строки запроса
Dim sql_query As String
«Записываем в нее нужный нам запрос
sql_query = «INSERT INTO test_table (id, name_mon) VALUES («6», «Июнь»)»
«Выполняем его
DoCmd.RunSQL sql_query
End Sub

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

Как видим, данные вставились.

Для того чтобы удалить одну строку пишем вот такой код.

Private Sub start_Click()
«Объявляем переменную для хранения строки запроса
Dim sql_query As String
«Записываем в нее запрос на удаление
sql_query = «DELETE test_table WHERE id = 6»
«Выполняем его
DoCmd.RunSQL sql_query
End Sub

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

Для обновления данных записываем в переменную sql_query запрос update, надеюсь, смысл понятен.

Восстановление базы данных Microsoft Access (.mdb, .accdb)

Читайте, как восстановить утерянные или удалённые файлы базы данных Access или их резервные копии. Наиболее распространённые причины утери базы данных. Microsoft Access – это одно из приложений пакета программ Microsoft Office, которое является мощным, доступным и относительно простым инструментом управления базами данных.

В Microsoft Access можно импортировать или прочесть файлы других баз данных, а также Excel, SharePoint, папки Outlook, и текстовые XML или HTML файлы. Пользователь также может самостоятельно создать базу контактов, задач, событий, проектов или основных фондов и т. пр.

Все данные можно сохранять в файлах баз данных форматов .mdb (для более ранних версий Access) или .accdb (начиная с Access 2007).

Вот теперь все нормально :

Прим. Удалить пользователя CHIEF можно командой DROP USER CHIEF CASCADE (только сначала хорошенько подумайте, а то шефу ваша идея может не очень-то и понравиться :- ) :

На следующем шаге нужно будет создать соединение .

. ну скажем, с именем MyConn приблизительно сл. образом :

Для тестирования параметров соединения жмем кнопку Test (см. рис. выше) . Если все прошло в штатном режиме, должны увидеть наше вновь созданное соединение :

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

На следующей форме оцениваем данные в режиме предварительного просмотра:

Указываем имя вновь созданной таблицы (Sotrudniki для случая нашего примера) :

Теперь нужно сделать выбор полей для переноса — берем все :

На следующей форме жмем, само-собой, Finish .

. и , опаньки, получаем такое сообщение :

Аварийное сообщение User does not have privileges to allocate an extent in the specified tablespace указывает на то, что у пользователя нет прав перераспределять выделенное пространство . ‘Лечиться’ такая беда командой GRANT UNLIMITED TABLESPACE to CHIEF :

Увы, придется всю манипуляцию по импорту Excel-таблички проделать заново .

Если все было сделано правильно, то в конце мастера импорта должны увидеть сообщение с ледующего содержания:

А окончательно достигнутый результат должен выглядеть таким образом :

Структуру (описание полей) нашей вновь созданной таблицы можно просмотреть и откорректировать на вкладке Columns :

Теперь можно работать с табличкой Sotrudniki. Например, составить такой нехитрый запрос Select * from Sotrudniki Where Zarplata > 40000 , который будет выводить всех сотрудников, у которых зарплата выше 40 000 неких у.е. :