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

20.09.2013

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

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

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

'Объявляем переменные

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

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