Prevent duplicate records

[JonoB]

Hi,

I have written the following procedure which prevents duplicate records from being inserted into a table. This works 100%, but was wondering if there were any other (more efficient?) ways of doing this. I am aware that this could be done with table definitions, but not quite sure of the user-friendlyness of this approach.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQLrs As String
Dim strSQL As String

strSQLrs = "SELECT SiteID,ContactDetailsID FROM tblDramatic " _
& "WHERE (((tblDramatic.SiteID)=" & gblSiteID & ") AND " _
& "((tblDramatic.ContactDetailsID)=" & Me.txtContactDetailsID & ")); "

'Create connection.
Set cnn = CurrentProject.Connection

'Create recordset
Set rs = cnn.Execute(strSQLrs)

'Check to see if there are any records
If rs.EOF And rs.BOF Then
strSQL = _
"INSERT INTO tblDramatic (ContactDetailsID, SiteID)" & _
" SELECT '" & Me.txtContactDetailsID & "' AS ContactDetailsID," & _
"'" & gblSiteID & "' AS SiteID;"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.Requery
Else
MsgBox "This record already exists"
End If

'Tidy up
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing

 

[JonoB]

I am awarw that this could be done with dcount, but have ben steered clear of using domain functions....not sure if this may be an exception

 

[RoyVidar]

One possibility, is the following (using the .execute method of the connection object to do the insert - if it returns an error - it's most likely that the record exists, then just trap for it):

[tt]strSQL = _
"INSERT INTO tblDramatic (ContactDetailsID, SiteID)" & _
" SELECT '" & Me.txtContactDetailsID & "' AS ContactDetailsID," & _
"'" & gblSiteID & "' AS SiteID;"
on error resume next
cnn.Execute strSQLrs
if err.number<>0 then
msgbox "This record already exists"
err.clear
end if[/tt]

Roy-Vidar

 

[RoyVidar]

Ouch - this would require an unique index on the fields (primary key?), so it does perhaps not address the issue properly... (and of course also goofed with the string variable name ;-))

Roy-Vidar

 

[JonoB]

Yeah, I saw the string variable name issue, and also wondered about the unique index issue.

As mentioned, I could sort this our using table definitions, but this would present some user-friendly issues.

So, back to my original code or using dcount....which do you reckon is faster? Intuitively, I just dont like domain functions.....

 

[RoyVidar]

I don't like the domain aggregate's either. On native tables, they might be faster, but on linked tables and in multiuser setups, their performance can get very bad (see for instance LittleSmudges reply here thread705-876059). It's been my experience that even though an app in the requirements is specified to be a stand alone "forever", they often end up being split, getting more users etc, so I never use the domain aggregate functions (btw, in the above link, there's a link to some functions to replace the domain aggregates).

Sorry for the mixup, I just use unique indexes (primary keys) on every table, not just where I want to prevent duplicates, so I didn't think before replying first

Roy-Vidar

 

[JonoB]

Thanks for that Roy.

No problem on the mixup, but I use primary keys on every table as well.

Cheers