Вопрос по объектной модели ADO и методу .RecordCount

AlexZZZ · 07 окт 2009, 00:04

Коллеги, доброго всем времени суток!
Вопрос по записи в базу данных Access из Excel через ADO. Столкнулся с проблемой: перед записью хотел проверить, есть ли уже данная запись в базе. Для этого сделал запрос и воспользовался методом Recordset.RecordCount, только он почему-то всегда возвращает "-1", даже если запись есть. Но что сделано неправильно? Как определить наличие уже существующей записи в базе данных?

Код: Выделить всё

   Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer

'   Database information
    DBFullName = ThisWorkbook.Path & "\База данных2.mdb"
    
'   Open the connection
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Cnct
    
'   Create RecordSet
    Set Recordset = New ADODB.Recordset
    
    With Recordset
'       Filter
        Src = "SELECT * FROM Контакты"
        Src = Src & " WHERE Фамилия = 'Иванов'"
        Src = Src & " and Имя = 'Иван'"
        Src = Src & " and Отчество = 'Иванович'"
        .Open Source:=Src, ActiveConnection:=Connection
    End With
        
    MsgBox Recordset.RecordCount
    
    If Recordset.RecordCount < 0 Then ' проверка, если уже эта запись
            Src = "INSERT INTO Контакты (Фамилия, Имя, Отчество)"
            Src = Src & " VALUES ('Иванов', 'Иван', 'Иванович')"
            'запись в базу данных
            Connection.Execute Src, adCmdText + adExecuteNoRecords
    Else
        MsgBox "Уже есть в списке!"
    End If
    
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing

Teslenko_EA · 07 окт 2009, 17:17

Здравствуйте AlexZZZ.
замените строку :
.Open Source:=Src, ActiveConnection:=Connection
на подобную:
rs.Open sSql, cn, adOpenStatic, adLockReadOnly
она явно устанавливает нужный тип курсора и блокировку и свойство RecordCount будет доступно.
Евгений.
P.S. настоятельно рекомендую отказаться от использования в качестве имен переменных Recordset и Connection.
--------------------------------------------------------------------------------
дополнение:
совершенно не обязательно менять тип курсора, если открыть рекордсет с таким текстом запроса:
Src = "SELECT count(*) FROM Контакты WHERE Фамилия = 'Иванов'" + _
" and Имя = 'Иван' and Отчество = 'Иванович'"
и далее:
If rs(0) = 0 Then ...
Евгений.

AlexZZZ · 07 окт 2009, 22:47

замените строку :
.Open Source:=Src, ActiveConnection:=Connection
на подобную:
rs.Open sSql, cn, adOpenStatic, adLockReadOnly
она явно устанавливает нужный тип курсора и блокировку и свойство RecordCount будет доступно.

Спасибо, Евгений! Так заработало.

If rs(0) = 0 Then ...

А вот на это выдаёт ошибку:

Teslenko_EA · 08 окт 2009, 06:58

Здравствуйте AlexZZZ.
конструкция запроса :
SELECT count(*) FROM Контакты WHERE Фамилия = 'Иванов'...,
если она составлена верно и он работоспособен, всегда отображает как минимум одну строку, т.е. при открытии рекордсета его свойство EOF (BOF) не может быть True в принципе. Создайте такой запрос в БД и проверьте его конструкцию, в построителе ошибку обнаружить проще.
Евгений.

AlexZZZ · 08 окт 2009, 12:42

Сообщение поправил. После исправления ошибок всё заработало, буду разбираться в коде. Евгений, ещё раз спасибо!

Dim DBFullName As String
Dim Cnct As String, Src As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Col As Integer

' Database information
DBFullName = ThisWorkbook.Path & "\База данных2.mdb"

' Open the cn
Set cn = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
cn.Open ConnectionString:=Cnct

Set rs = New ADODB.Recordset

Src = "SELECT * FROM Контакты"
Src = Src & " WHERE Фамилия = 'Иванов'"
Src = Src & " and Имя = 'Иван'"
Src = Src & " and Отчество = 'Иванович'"
rs.Open Src, cn, adOpenStatic, adLockReadOnly

MsgBox rs(0)

If rs.RecordCount > 0 Then ' проверка, если уже эта запись
MsgBox "Уже есть в списке!"
Else
Src = "INSERT INTO Контакты (Фамилия, Имя, Отчество)"
Src = Src & " VALUES ('Иванов', 'Иван', 'Иванович')"
cn.Execute Src, adCmdText + adExecuteNoRecords
End If

Cells.Clear
cn.Close
Set cn = Nothing
Set rs = Nothing
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ConnectionString:=Cnct
Src = "SELECT * FROM Контакты"
Src = Src & " WHERE Фамилия = 'Иванов'"
Src = Src & " and Имя = 'Иван'"
Src = Src & " and Отчество = 'Иванович'"
rs.Open Source:=Src, ActiveConnection:=cn

Cells.Clear
For Col = 0 To rs.Fields.Count - 1
Range("A1").Offset(0, Col).Value = rs.Fields(Col).Name
Next
Range("A1").Offset(1, 0).CopyFromRecordset rs

Set rs = Nothing
cn.Close
Set cn = Nothing

P/S: Я так понимаю, что если искомая запись в базе есть, то rs(0) возвращает значение ключевого поля строки, а если ещё нет, то возвращает пустую строку. Правильно?

Teslenko_EA · 08 окт 2009, 20:53

Здравствуйте AlexZZZ.
Вашу конструкцию можно несколько "усовершенствовать"

Код: Выделить всё

Const sCon$ = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Const sBase$ = "\База данных2.mdb"
Const sQl$ = "SELECT * FROM Контакты WHERE Фамилия = '"
Const sQ2$ = "' and Имя = '", sQ3$ = "' and Отчество = '", sQ4$ = "'"
Const sIl = "INSERT INTO Контакты (Фамилия, Имя, Отчество) VALUES ('"
Const sI2 = "', '", sI3 = "')"
Sub Proc()

Dim DBFullName$, Src$, sV(2) As String
Dim cn As ADODB.Connection, rs As ADODB.Recordset

sV(0) = "Иванов": sV(1) = "Иван": sV(2) = "Иванович"
DBFullName = ThisWorkbook.Path + sBase
Set cn = New ADODB.Connection
cn.Open ConnectionString:=sCon + DBFullName + ";"

Set rs = New ADODB.Recordset
    Src = sQl + sV(0) + sQ2 + sV(1) + sQ3 + sV(2) + sQ4
rs.Open Src, cn

If Not rs.EOF Then   ' проверка, если уже эта запись
    MsgBox "Уже есть в списке!"
Else
    cn.Execute sIl + sV(0) + sI2 + sV(1) + sI2 + sV(2) + sI3
End If

'cn.Close - соединение открыто зачем его закрывать
'изменился только рекордсет(набор записей) его можно обновить не закрывая
rs.Requery
'For Col = 0 To rs.Fields.Count - 1 ' если данные выкладываются на заготовку (шаблон) этот код лишний
    'Range("A1").Offset(0, Col).Value = rs.Fields(Col).Name
'Next
Range("A2:AA65000").Clear 'сохранение имен полей
Range("A2").CopyFromRecordset rs
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

или изменить так:

Код: Выделить всё

Const sCon$ = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Const sQl$ = "SELECT * FROM Контакты WHERE Фамилия = '"
Const sQ2$ = "' and Имя = '", sQ3$ = "' and Отчество = '", sQ4$ = "'"
Sub Proc()

Dim DBFullName$, Src$, sV(2) As String
Dim cn As ADODB.Connection, rs As ADODB.Recordset

sV(0) = "Иванов": sV(1) = "Иван": sV(2) = "Иванович"
DBFullName = ThisWorkbook.Path + "\База данных2.mdb"
Set cn = New ADODB.Connection
cn.Open ConnectionString:=sCon + DBFullName + ";"
Set rs = New ADODB.Recordset
    Src = sQl + sV(0) + sQ2 + sV(1) + sQ3 + sV(2) + sQ4
rs.Open Src, cn, adOpenDynamic, adLockOptimistic

If rs.EOF Then
    rs.AddNew: rs("Фамилия") = sV(0): rs("Имя") = sV(1): rs("Отчество") = sV(2): rs.Update
End If
rs.Requery
Range("A2:AA65000").Clear
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

rs(0) - первое (не обязательно ключевое) поле рекордсета т.к. нумерация его полей начинается с нуля. К полям рекордсета можно обращаться как по имени - rs("Имя") так и по индексу - rs(1). Поле текущей записи может не содержать значения (Null) или содержать значение (строка нулевой длины - "пустая" тоже значение), но если записи в рекордсете отсутствуют, его свойство EOF принимает значение True и обращение к полям невозможно.
Евгений.