469,572 Members | 1,205 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

Check if data already exists in Access from Excel

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.

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

Nov 20 '06 #2

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

Nov 20 '06 #3

Gord,

Thanks for your help.

Nov 20 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Innuendo | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.