ADO - Best method to check for duplicates

[RPrinceton]

Hi Everyone,
I am trying to process an Access 2000 DB using VB6 via ADO. I like to keep my error handling "in-line" i.e., check for an error immediately after an instruction. In the code below I am checking for a duplicate key. Is this method suitable or is there a more preferred method?
Please advise. Thx in advance.
Regards,
RPrinceton

Codelit:
Dim insWeek As Long
Dim dbPath As String
Dim conn As ADODB.Connection
Dim ADOrs As ADODB.Recordset
Dim connString As String
Dim SQLString As String

' Establish error handling routine
On Error Resume Next
dbPath = "C:\Path to .mdb"

' Connect to database
Set conn = CreateObject("ADODB.Connection")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & dbPath & ";" _
& "Mode=ReadWrite;" _
& "User ID=Admin;" _
& "Password=;"
conn.Open connString

' Assign values
insWeek = WeekNbr

Set ADOrs = CreateObject("ADODB.Recordset")

SQLString = "Mytb"
ADOrs.Open SQLString, connString, adOpenKeyset, adLockOptimistic, adCmdTable
ADOrs.AddNew
ADOrs!Week = insWeek

' Issue insert
ADOrs.Update
Select Case ADOrs.Status
Case 0
lblMsg.ForeColor = vbBlack
lblMsg.Caption = "Record added"
Case 4096
lblMsg.ForeColor = vbRed
lblMsg.Caption = "Duplicate record"
Case Else
MsgBox ("Unhandled ADOrs.Status=" & ADOrs.Status)
Call notifyRoutine("cmdADOIns_Click")
End Select

' Disconnect
ADOrs.Close
conn.Close
Set ADOrs = Nothing
Set conn = Nothing

 

[abaldwin]

I would probably have to recommend the method that I use for inserts etc. The code becomes somewhat reuesable.

Instead of doing .Addnew, .update, etct use a string variable to create a simple insert statement and then use the ado connection to execute that statement.

Use the Error checking to perform rollbacks on some errors or to notify users if they are generating dupes.

i.e.


Code is very english and just for showing the concept. You will have to code it yourself.

On Error goto Error_handler
stString = "INSERT INTO table1 (field names) VALUES (weeknumb)

conn.execute ststring

blah bla

Error_Handler:
select case err.number
case = 'insert number of error on duplicate error numbers are readily found on the net.
case = 'Insert number for other errors in the code
end select

Benefits:
1. You do away with having to have a recordset item declared.

2. Your error trapping is included with other error trapping in the routine and thus can be modified (found even) quicker and easier.

Just some $.02 If you need help with some of this just holler.




Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[shannanl]

I like to create a function that checks for duplicates. I pass it the item to check for and then it returns true or false if it is a duplicate.

If funIsduplicate(itemtocheck) = true then
msgbox("You have entered a duplicate item.")
exit sub
end if