Отправка данных из Excel в базу данных Access

Отправка данных из Excel в базу данных Access, Ошибка при отправке данных в БД

Tidus1988

Пользователь

Сообщений: 46 Регистрация: 12.01.2017

20.08.2020 19:08:30

Добрый день,

Имеется процедура на листе Excel, которая с помощью переменной, перебора строк в определенном диапазоне отправляет данные таблицы в базу данных Access. При этом возникают ошибки:

Скрытый текст

Первый вариант

Код

Sub Variant1()

'Подключение
Dim Con As ADODB.Connection
Set Con = New ADODB.Connection
Dim i As Integer
Dim IDCheck As Integer
With Con
   .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=C:\REConclusion.accdb;Persist Security Info=True;Jet OLEDB:Database Password=123"
       .Open
End With
'Команда
Dim Cmd As ADODB.Command
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Con
'Строка
IDCheck = Worksheets("Пример").Range("B1").Value + 2
For i = 3 To IDCheck
        Cmd.CommandText = "insert into [TEST] " _
        & "(DateBD, NumberBD, TextMoreThan510, Quantity, CheckEmptyData)" _
        & "values(@DateBD, @NumberBD, @TextMoreThan510, @Quantity, @CheckEmptyData)"
        
    'Поле менее 510 знаков в ячейке (Дата)
    Cmd.Parameters.Append Cmd.CreateParameter("@DateBD", adDate, adParamInput)
    Cmd.Parameters("@DateBD").Value = Sheets("Пример").Cells(i, 3).Value
    
    'Поле менее 510 знаков в ячейке (Число)
    Cmd.Parameters.Append Cmd.CreateParameter("@NumberBD", adInteger, adParamInput)
    Cmd.Parameters("@NumberBD").Value = Sheets("Пример").Cells(i, 4).Value
    
    'Поле более 510 знаков в ячейке (Текст)
    Cmd.Parameters.Append Cmd.CreateParameter("@TextMoreThan510", adLongVarWChar, adParamInput, 2& ^ 31 - 1)
    Cmd.Parameters("@TextMoreThan510").Value = Sheets("Пример").Cells(i, 5).Value
    
    'Количество знаков ячеек в столбце E
    Cmd.Parameters.Append Cmd.CreateParameter("@Quantity", adInteger, adParamInput)
    Cmd.Parameters("@Quantity").Value = Sheets("Пример").Cells(i, 6).Value
    
    'Поле более 510 знаков в ячейке (Текст)
    Cmd.Parameters.Append Cmd.CreateParameter("@CheckEmptyData", adWChar, adParamInput, 255)
    Cmd.Parameters("@CheckEmptyData").Value = Sheets("Пример").Cells(i, 7).Value
    
    Cmd.Execute
    
Next i

End Sub

При котором данные свыше 509 знаков в ячейке отправляются, только в данном случае игнорируется переменная строк r и в базу данных отправляется 10 дублей первой строки, так же в случае если ячейка пустая процедура не передает пустоту а останавливается.

Скрытый текст

При котором данные свыше 509 знаков в ячейке не отправляются появляется ошибка несоответсвий данных, в данном случае переменная строк r не игнорируется и в базу данных отправляется все 10 вариантов (в случае если ограничить наполнение ячейки 509 знаками), так же в случае если ячейка пустая процедура не передает пустоту а останавливается.

Примеры во вложении, буду признателен если укажите на причину:
1) ограничения отправки более 509 знаков во втором варианте;
2) игнорирования переменной строк в первом варианте;
3) ошибки при наличии пустых ячеек, так как отсутсвует значение по умолчанию.

Прикрепленные файлы

Пример.xlsm (25.02 КБ)
REConclusion.rar (17.15 КБ)

Изменено: Tidus1988 - 20.08.2020 19:13:02

Андрей VG

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

20.08.2020 22:51:32

Доброе время суток.

Цитата
Tidus1988 написал: Второй вариант

Третий вариант

Код

Public Sub InsertFromExcel()
    Const strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\REConclusion.accdb;Persist Security Info=True;Jet OLEDB:Database Password=123"
    Dim sSQL As String
    Dim pConn As New ADODB.Connection, pSheet As Worksheet, lRow As Long
    Set pSheet = ThisWorkbook.Worksheets("Пример")
    lRow = pSheet.Cells(pSheet.Rows.Count, 2).End(xlUp).Row
    
    sSQL = "Insert Into Test (DateBD, NumberBD, TextMoreThan510, Quantity, CheckEmptyData) Select [Поле менее 510 знаков в ячейке (Дата)]"
    sSQL = sSQL & ",[Поле менее 510 знаков в ячейке (Число)],[Поле более 510 знаков в ячейке (Текст)]"
    sSQL = sSQL & ",[Количество знаков ячеек в столбце E],[Проверка если данных в ячееке нет] From "
    sSQL = sSQL & "[Excel 12.0;Database=" & ThisWorkbook.FullName & ";HDR=Yes].[Пример$B2:G" & lRow & "] Where [Проверка если данных в ячееке нет] Is Not Null;"
    pConn.Open strConn
    pConn.Execute sSQL
    pConn.Close
End Sub

Tidus1988

Пользователь

Сообщений: 46 Регистрация: 12.01.2017

21.08.2020 06:25:48

Цитата
Андрей VG написал: Where [Проверка если данных в ячееке нет] Is Not Null

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

Цитата
Tidus1988 написал: так же в случае если ячейка пустая процедура не передает пустоту а останавливается

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

Разобрался, исключил условие

Код
Where [Проверка если данных в ячееке нет] Is Not Null

Андрей VG еще раз спасибо.

Изменено: Tidus1988 - 21.08.2020 06:32:11