Check if data already exists in Access from Excel

[byeung]

Hi,

I am trying to check if a particular record already exists in an Access
database through Excel vba code. Through code obtained at another
forum, I got the following:
************************************************** *********************

Sub TheButton()

Dim db As Database, rs As DAO.Recordset, r As Long
Dim PolicyNum As Variant
Dim bFound As Boolean

Set db = OpenDatabase("E:Test.mdb")
Set rs = db.OpenRecordset("Level Data", dbOpenTable)

bFound = False
rs.MoveFirst
Do
If ThisWorkbook.Worksheets("Loss Model").Cells(17, 3).Value =
rs.Fields("Policy/Quote Number") Then

bFound = True
MsgBox "Record Already Exist."
End If
rs.MoveNext
Loop Until rs.EOF Or bFound = True
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

************************************************** *******************

The data is entered at cell (Q,3). But if I type in

If ThisWorkbook.Worksheets("Loss Model").Cells(Q, 3).Value =
rs.Fields("Policy/Quote Number")
it will give me a 1004 runtime error: application defined or object
defined.

If I use the code above

If ThisWorkbook.Worksheets("Loss Model").Cells(17, 3).Value =
rs.Fields("Policy/Quote Number")

The program cannot catch whether the record already exist and it does
not show me a MsgBox.

Please help.

[byeung]

Got it, Should have used:
If ThisWorkbook.Worksheets("Loss Model").Range("Q" & "3").Value =
rs.Fields("Policy/Quote Number")

[Gord]

by****@bh-hc.com wrote:

Hi,

I am trying to check if a particular record already exists in an Access
database through Excel vba code. Through code obtained at another
forum, I got the following:
************************************************** *********************

Sub TheButton()

Dim db As Database, rs As DAO.Recordset, r As Long
Dim PolicyNum As Variant
Dim bFound As Boolean

Set db = OpenDatabase("E:Test.mdb")
Set rs = db.OpenRecordset("Level Data", dbOpenTable)

bFound = False
rs.MoveFirst
Do
If ThisWorkbook.Worksheets("Loss Model").Cells(17, 3).Value =
rs.Fields("Policy/Quote Number") Then

bFound = True
MsgBox "Record Already Exist."
End If
rs.MoveNext
Loop Until rs.EOF Or bFound = True
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

************************************************** *******************

The data is entered at cell (Q,3). But if I type in

If ThisWorkbook.Worksheets("Loss Model").Cells(Q, 3).Value =
rs.Fields("Policy/Quote Number")
it will give me a 1004 runtime error: application defined or object
defined.

If I use the code above

If ThisWorkbook.Worksheets("Loss Model").Cells(17, 3).Value =
rs.Fields("Policy/Quote Number")

The program cannot catch whether the record already exist and it does
not show me a MsgBox.

Your parameters for the Cells() property are reversed. They are
specified as...

Cells(RowIndex, ColIndex)

....so you should be looking at Cells(3, 17), not Cells(17, 3).

Also, if your [Level Data] table is of any significant size you might
not want to scan it each time. Instead, you could do something like
this:
Private Sub CommandButton1_Click()
Dim db As DAO.Database, rs As DAO.Recordset
Dim PolicyNum As Variant
Dim bFound As Boolean

PolicyNum = ThisWorkbook.Worksheets("Loss Model").Cells(3, 17).Value

Set db = OpenDatabase("R:\Test.mdb")

Set rs = db.OpenRecordset( _
"SELECT [Policy/Quote Number] " & _
"FROM [Level Data] " & _
"WHERE [Policy/Quote Number]=""" & _
Replace(PolicyNum, """", """""", , , vbTextCompare) & _
"""", dbOpenDynaset)

bFound = Not rs.EOF

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

If bFound Then
MsgBox "Record exists."
Else
MsgBox "Record not found."
End If

End Sub

[byeung]

Gord,

Thanks for your help.