By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Check if data already exists in Access from Excel

P: n/a
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
Share this Question
Share on Google+
3 Replies


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

Nov 20 '06 #2

P: n/a

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

P: n/a

Gord,

Thanks for your help.

Nov 20 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.