USE [EditorTest]
GO
/****** Object:  Table [dbo].[CASE_MILESTONE_TEMP]    Script Date: 12/04/2008 10:35:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CASE_MILESTONE_TEMP](
	[CASE_IDENTIFIER] [int] NULL,
	[MILESTONE_DATE] [datetime] NULL,
	[TEXT_FIELD] [varchar](50) NULL,
	[DROP_THIS_FIELD] [varchar](50) NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

USE [EditorTest]
GO
/****** Object:  Table [dbo].[CLEAN_MILE_DUPES]    Script Date: 12/04/2008 10:35:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CLEAN_MILE_DUPES](
	[CASE_IDENTIFIER] [int] NULL,
	[MILESTONE_DATE] [datetime] NULL,
	[TEXT_FIELD] [varchar](50) NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

USE [EditorTest]
GO
CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[CLEAN_MILE_DUPES]
(
 [CASE_IDENTIFIER] ASC
)
WITH (IGNORE_DUP_KEY = ON)

Option Explicit
 
Private oConn As ADODB.Connection
Private oCmd As ADODB.Command
 
Private Sub Command1_Click()
'
Dim strSQL As String
Dim ConStr As String
 
On Error GoTo ErrorHandler
 
ConStr = "PROVIDER=SQLOLEDB;Data Source=JASMIN\SQLEXPRESS; INITIAL CATALOG=EditorTest; User ID=XXXXXXX; Password=XXXXXXX;"
Set oConn = New ADODB.Connection
oConn.Open (ConStr)
 
strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
           & "       CASE_IDENTIFIER" & vbCrLf _
           & "       ,MILESTONE_DATE" & vbCrLf _
           & "      ,TEXT_FIELD) " & vbCrLf _
           & "(SELECT CASE_IDENTIFIER" & vbCrLf _
           & "       ,MILESTONE_DATE" & vbCrLf _
           & "       ,TEXT_FIELD " & vbCrLf _
           & "   FROM CASE_MILESTONE_TEMP)"
 
Set oCmd = New ADODB.Command
oCmd.CommandText = strSQL
oCmd.CommandType = adCmdText
Set oCmd.ActiveConnection = oConn
oCmd.Execute
 
Sub_Exit:
  oConn.Close
  Exit Sub
ErrorHandler:
  ' Breakpoint here
  Resume Sub_Exit
 
End Sub

? oConn.Errors(0).Description
Duplicate key was ignored.