MyTetra Share
Делитесь знаниями!
Как в Access на VBA импортировать данные в базу Microsoft SQL Server?
Время создания: 06.06.2020 14:05
Раздел: !Закладки - SQL Server - VBA_Sql_Server

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

20.09.2013Рубрика: Microsoft OfficeАвтор: Админ


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

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

Так же в этом разделе:
 
MyTetra Share v.0.53
Яндекс индекс цитирования