Как в Access на VBA импортировать данные в базу Microsoft SQL Server?

Очень часто у программистов и пользователей, у которых реализовано приложение в связке клиент (Access – adp проект) и сервер (Microsoft SQL Server – база данных) возникает необходимость импорта данных, с помощью adp проекта, в базу данных, причем чтобы это могли делать простые пользователи, т.е. максимально просто, без привлечения программистов. И сегодня мы поговорим о реализации данной возможности.

Так как импортируемые данные могут быть разные, сегодня мы рассмотрим 2 примера импорта.

  1. Импорт данных из Excel
  2. Импорт изображения в базу данных и ее дальнейшее отображение в отчетах и формах

Импорт данных из Excel в Microsoft SQL Server на VBA Access

И начнем мы с импорта данных из таких форматов как xls и dbf, так как это очень часто требуется в работе, а если у кого и не часто, то все равно рано или поздно такая необходимость возникнет.

Когда у меня стояла задача реализовать такой импорт, я нашел много способов и средствами Microsoft SQL сервера, и встроенными средствами Access, но в каждом из них были небольшие минусы, иными словами, не для каждодневного использования пользователями. Но мне встретился такой способ, который меня полностью устроил и именно о нем я сейчас расскажу.

Данный способ заключается в подключении напрямую к файлу источнику через поставщика Microsoft.Jet.OLEDB.4.0.

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

Допустим, у нас есть файл «file.xls» со следующими данными:

Pole1

Pole2

Pole3

Pole4

1

4

7

10

2

5

8

11

3

6

9

12

Примечание! Называйте лист в файле, на котором располагаются данные, также как и сам файл.

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

Таблицу создадим вот так:

   
   CREATE TABLE [dbo].[testimport](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [col1] [int] NULL,
        [col2] [int] NULL,
        [col3] [int] NULL,
        [col4] [int] NULL,
        
   CONSTRAINT [PK_testimport] PRIMARY KEY CLUSTERED 
   (
        [id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO

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

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

Вы добавили кнопку, теперь в событие «Нажатие кнопки» добавьте следующий код:

   
   'Объявляем переменные
   Dim FileDialog As FileDialog
   Dim cnn As ADODB.Connection
   Dim rs  As ADODB.Recordset
   Dim rstProj As ADODB.Recordset
   Dim path  As String
   Dim counter As Integer
   Dim strSQL As String
   Dim strFile As String
   
   'создаем объект "Диалоговое окно выбора файла"
   Set FileDialog = Application.FileDialog(msoFileDialogOpen)
   'Убираем множественный выбор файлов, нам нужен только один
   FileDialog.AllowMultiSelect = False
   'очистим и установим новые фильтры
   FileDialog.Filters.Clear
   FileDialog.Filters.add "Excel и DBF", "*.xls, *.dbf"
   'зададим фильтр по умолчанию (с индексом 1 т.е. первый), можно и не писать,
   'но вдруг Вы заходите установить несколько фильтров
   FileDialog.FilterIndex = 1
   
   'проверяем, что сделал пользователь, выбрал файл или нет
   If FileDialog.Show = False Then
      'Если нет, то выходим
      Set dlgFile = Nothing
      Exit Sub
   End If
   'получаем путь к файлу
   path = Trim(FileDialog.SelectedItems(1))
   'очищаем переменную с объектом
   Set FileDialog = Nothing
  
   'проверяем, что за путь нам вернулся, если ничего не вернулось, то все пропускаем
   If path <> "" Then
    'Создаем подключение к источнику
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    'Определим имя файла для запроса
    strFile = Mid(path, InStrRev(path, "\") + 1, Len(path) - InStrRev(path, "\") - 4)
    'Смотрим, что за файл выбрал пользователь
    'Если XLS то заходим сюда
    If UCase(path) Like "*.XLS" Then
      'Задаем путь к файлу
      cnn.Properties("Data Source") = path
      'Задаем тип файла
      cnn.Properties("Extended Properties") = "Excel 8.0"
      'Строка запроса
      strSQL = "select pole1, pole2, pole3, pole4  from [" + strFile + "$]"
    'Если DBF то сюда
    ElseIf UCase(path) Like "*.DBF" Then
      'Задаем путь к файлу
      cnn.Properties("Data Source") = Mid(path, 1, InStrRev(path, "\"))
      'Задаем тип файла
      cnn.Properties("Extended Properties") = "dBase IV"
      'Строка запроса
      strSQL = "select pole1, pole2, pole3, pole4  from  [" + strFile + "]"
    End If
   
   'устанавливаем подключение к источнику
   cnn.Open
   'создаем Recordset для данных из источника
   Set rs = New ADODB.Recordset
   'открываем и считываем данные из источника
   rs.Open strSQL, cnn
   'создаем Recordset для данных из базы
   Set rstProj = New ADODB.Recordset
   'Открываем данный Recordset
   rstProj.Open "dbo.testimport", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   'переменная для подсчета количества импортируемых записей
   counter = 0
   
   'Запускаем цикл для переноса данных из источника в базу,
   'т.е. одна итерация цикла это одна строка в файле и базе данных
    While Not (rs.EOF)
      'присваиваем значения из столбца источника столбцу в базе данных
      With rstProj
        .AddNew
        .Fields("col1") = rs.Fields("pole1").Value
        .Fields("col2") = rs.Fields("pole2").Value
        .Fields("col3") = rs.Fields("pole3").Value
        .Fields("col4") = rs.Fields("pole4").Value
        .Update
        'Увеличиваем наш счетчик
       counter = counter + 1
      End With
      'читаем следующую строку
      rs.MoveNext
    Wend
    
   'закрываем подключение к базе MSSql
    rstProj.Close
    Set rstProj = Nothing
    'закрываем источник данных
    cnn.Close
    Set cnn = Nothing
    'Очистим Recordset
    Set rs = Nothing
    'и выведем на экран, сколько мы импортировали строк
    MsgBox counter
    
   End If

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

Примечание! Если Вы хотите накапливать данные в таблице (в нашем случае testimport), то в файле источнике, добавляете какой-нибудь признак, чтобы потом иметь возможность выбрать те данные, которые Вы только что загрузили (например, столбец с датой, и в базе соответственно тоже добавьте), если не хотите, то перед импортом данных в  таблицу, очищайте ее, иначе все данные будут накапливаться.

Импорт изображения в Microsoft SQL Server на VBA Access

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

Примечание! Примеры ниже тестовые, поэтому Вы можете создать свою таблицу со своими полями, и, соответственно, свою форму.

Создадим таблицу

   
   CREATE TABLE [dbo].[kartinki](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [Picture] [varbinary] (MAX)  ,
        
   CONSTRAINT [PK_kartinki] PRIMARY KEY CLUSTERED 
   (
        [id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO

На форму добавьте объект «Рисунок», первоначально придется выбрать любой рисунок для добавления данного объекта на форму, потом его можно очистить (я его назвал kartinka). Источник записей формы будет наша вновь созданная таблица. И, конечно же, кнопочку для добавления рисунка. Для кнопки в событии «Нажатие кнопки» вставьте следующий код:

   
   'Объявляем переменные
   Dim rs As Recordset
   Dim FileDialog  As FileDialog
   Dim path As String
   Set rstProj = New ADODB.Recordset
   'тоже что и в примере выше
   Set FileDialog = Application.FileDialog(msoFileDialogOpen)
   FileDialog.AllowMultiSelect = False
   FileDialog.Filters.Clear
   FileDialog.Filters.add "Картинки и рисунки", "*.jpg, *.gif, *.bmp, *.png"
   'Для разнообразия добавим еще один фильтр
   FileDialog.Filters.add "Все файлы", "*.*"
   FileDialog.FilterIndex = 1
   
   If FileDialog.Show = False Then
      Set FileDialog = Nothing
      Exit Sub
   End If

   path = Trim(FileDialog.SelectedItems(1))
   
   If path <> "" Then
      Set FileDialog = Nothing
      'присваиваем нашей картинке на форме выбранную картинку, т.е. задаем путь
      kartinka.Picture = path
      rstProj.Open "[dbo].[kartinki]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
      Set rs = rstProj
      'создаем новую строку и переносим бинарные данные в нашу таблицу в базе
      rs.AddNew
      rs![Picture] = kartinka.PictureData
      rs.Update
      'все это дело закрываем
      rstProj.Close
      Set rstProj = Nothing
      Set rs = Nothing
      'пересчитаем данные на форме
      Me.Recalc
   End If

Для наглядности, чтобы увидеть, что Вы импортировали картинку в базу, на форме в событии «Текущая запись» вставьте следующий код:

   
   If Me.NewRecord Then
      kartinka.Picture = ""
   Else
      kartinka.PictureData = Me![Picture]
   End If

Теперь, где Вам нужно выводить картинку, например, в отчетах, Вы также создадите объект рисунок и в событии отчета «Открытие» будете присваивать значение свойства PictureData из базы данных и все. Например, вот так:

   
   Dim strSQL As String
   Set rs = New ADODB.Recordset
   strSQL = "select picture from dbo.kartinki"
   rs.Open strSQL, CurrentProject.Connection
   rez = CStr(rs.Fields(0))
   rs.Close
   Me.kartinka.PictureData = rez
   Set rs = Nothing

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

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

Вот в принципе и все, если что непонятно пишите в комментариях, может, чем помогу. Удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.