SQL Insert statement syntax

Question

pixelmeow 0 Newbie Poster

15 Years Ago

Hello,

I'm using VB6 and SQL Server 2000.

I've got a problem with an insert statement that seems to be randomly failing. Its job is to copy everything from one table into another, removing duplicates using WITH IGNORE_DUP_KEY ON [PRIMARY]. Then my code copies the records back to the original table, duplicate free.

This is my VB code:

strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
           & "       CASE_IDENTIFIER" & vbCrLf _
           & "      ,DOCUMENT_TYPE" & vbCrLf _
           & "      ,DOCUMENT_NUMBER" & vbCrLf _
           & "      ,MILESTONE" & vbCrLf _
           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
           & "      ,MILESTONE_DATE)" & vbCrLf _
           & "(SELECT CASE_IDENTIFIER" & vbCrLf _
           & "       ,DOCUMENT_TYPE" & vbCrLf _
           & "       ,DOCUMENT_NUMBER" & vbCrLf _
           & "       ,MILESTONE" & vbCrLf _
           & "       ,MILESTONE_DATE_TYPE" & vbCrLf _
           & "       ,MILESTONE_DATE " & vbCrLf _
           & "   FROM CASE_MILESTONE_TEMP)"

The SQL works in TOAD and Query Analyzer, every time. The code works in my program, on the machine I'm using right now, also every time. I've got two other machines I'm using for testing, with their own databases, and this code fails in the program, but works in TOAD or Query Analyzer.

As I'm debugging this today, it is failing. I removed the second pair of parentheses from the code above (I saw an insert statement here today while trying to find the answer, and it didn't have the select statement in parentheses) and it worked. I am now ripping my hair out.

Either form works in TOAD or QA. It would seem that either works in VB, *unless it doesn't feel like working*. Does anyone have any idea about this?

Thanks,
teresa

visual-basic-6

2 Contributors
8 Replies
171 Views
2 Days Discussion Span
Latest Post 15 Years Ago Latest Post by pixelmeow

All 8 Replies

Teme64 215 Veteran Poster

15 Years Ago

What is the exact error message that you get? What error number you get from oConn.Errors(0).NativeError (oConn being your connection object)?

A few things come to my mind straight away. Are the tables identical? Is any of the fields char/varchar type and contains '-character in the source table? Null values?

Do you have an error handler in your code where you can set a breakpoint and check, what does the strSQL variable actually hold?

Teme64 215 Veteran Poster

15 Years Ago

I'm trying to re-produce your case.

Here's my simplified tables.
Temp-table:

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

and Clean-table:

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

with CASE_IDENTIFIER as a duplicate key:

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

and I tested with this code:

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

The error I get in the Breakpoint-line is:

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

and that comes from a duplicate CASE_IDENTIFIER-field value from the Temp-table. And now the Case-table has all the rows from the Temp-table, except one dropped duplicate, and the column DROP_THIS_FIELD is missing of course.

Is this anything similar to your code?

(I tested with SQL Server 2005 EE, but that shouldn't make any difference)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

pixelmeow 0 Newbie Poster · Answer 1 · 2008-12-03T22:29:28+00:00

What is the exact error message that you get? What error number you get from oConn.Errors(0).NativeError (oConn being your connection object)?

It's not a connection object error. The first truncate works fine, the first insert doesn't insert anything into the CLEAN table, the second truncate works fine, then there's nothing to be inserted into the CASE table.

A few things come to my mind straight away. Are the tables identical? Is any of the fields char/varchar type and contains '-character in the source table? Null values?

The only differences between the tables are the ID column in the CLEAN table and the REMARKS column in the CASE table. The REMARKS column isn't included in the SELECT statement, so that doesn't affect anything. If I DESC (Oracle term, but it's a function I have in SS), the tables are identical. They should be, the CREATE statement for the CLEAN table was exactly the same as the CASE table with the ID column and without the REMARKS column. Null values are allowed everywhere except primary keys, as long as this is in development.

Do you have an error handler in your code where you can set a breakpoint and check, what does the strSQL variable actually hold?

I've got a lot of breakpoints to see what's going on, and the strSQL variable holds the SQL statements that are passed to the DB. All of them work, except *that* one, and that one seems to fail at random.

I just ran my program on the DB on my machine, and as usual, all SQL worked just fine. I've got to run it on the test machine now, and I'll let you know how it goes.

Thank you for your questions, they help me make sure everything's right! :-)

pixelmeow 0 Newbie Poster · Answer 2 · 2008-12-04T02:40:55+00:00

I'm at a complete loss. The SQL above, and two other similar statements, work fine on my machine. Now, instead of just the one statement above giving me problems, all three are. They act as if they are copying records to the CLEAN table, then truncate the CASE table, then when it's time to copy the records back from the CLEAN table to the CASE table, there are no records to copy back! I don't understand!

I created two new databases for two new datasets, but these SQL statements work FINE in those databases. Does anyone have any idea?

Thanks,
teresa

pixelmeow 0 Newbie Poster · Answer 3 · 2008-12-04T20:24:26+00:00

I'm trying to re-produce your case.

Here's my simplified tables.
Temp-table:

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

Here's my milestone table:

CREATE TABLE [CASE_MILESTONE_TEMP] (
	[CASE_IDENTIFIER] [varchar] (10)  NULL,
	[DOCUMENT_TYPE] [varchar] (3) NULL ,
	[DOCUMENT_NUMBER] [varchar] (3) NULL,
	[MILESTONE] [varchar] (10) NULL,
	[MILESTONE_DATE_TYPE] [varchar] (1) NULL,
	[MILESTONE_DATE] [varchar] (14) NULL,
	[MILESTONE_REMARKS] [varchar] (500) NULL 
) ON [PRIMARY]
GO

I forgot there's no primary key... sorry...

and Clean-table:

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

Here's my clean table:

CREATE TABLE [CLEAN_MILE_DUPES] (
	[ID] [int] NULL ,
	[CASE_IDENTIFIER] [varchar] (10) NULL,
	[DOCUMENT_TYPE] [varchar] (3) NULL,
	[DOCUMENT_NUMBER] [varchar] (3) NULL,
	[MILESTONE] [varchar] (10) NULL,
	[MILESTONE_DATE_TYPE] [varchar] (1) NULL,
	[MILESTONE_DATE] [varchar] (10) NULL
) ON [PRIMARY]
GO

with CASE_IDENTIFIER as a duplicate key:

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

Here's my index:

CREATE  UNIQUE  INDEX [RM_MILE_DUPES] ON [dbo].[CLEAN_MILE_DUPES]
(
    [CASE_IDENTIFIER]
   ,[DOCUMENT_TYPE]
   ,[DOCUMENT_NUMBER]
   ,[ID]
   ,[MILESTONE]
   ,[MILESTONE_DATE_TYPE]
   ,[MILESTONE_DATE]
) 
WITH  IGNORE_DUP_KEY  ON [PRIMARY]
GO

and I tested with this code:

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

The error I get in the Breakpoint-line is:

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

and that comes from a duplicate CASE_IDENTIFIER-field value from the Temp-table. And now the Case-table has all the rows from the Temp-table, except one dropped duplicate, and the column DROP_THIS_FIELD is missing of course.

Is this anything similar to your code?

(I tested with SQL Server 2005 EE, but that shouldn't make any difference)

Your code is very similar, just differences in how we do the database code:

Option Explicit
Public cn As New ADODB.Connection
Public cmd As New ADODB.Command
Public rs As New ADODB.Recordset

Private Sub removeMileDupes()
    Dim strSQL As String
    If cn.State = 0 Then
        'fileset says which set of files are being loaded at the moment
        Call doDBaction("openDB", fileSet)
    End If
    'clean_dupes tables have ignore duplicates flag on
    'vb catches the error and stops
    'so resume next.
    On Error Resume Next
    strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute
    
    strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
           & "       CASE_IDENTIFIER" & vbCrLf _
           & "      ,DOCUMENT_TYPE" & vbCrLf _
           & "      ,DOCUMENT_NUMBER" & vbCrLf _
           & "      ,MILESTONE" & vbCrLf _
           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
           & "      ,MILESTONE_DATE)" & vbCrLf _
           & "SELECT CASE_IDENTIFIER" & vbCrLf _
           & "      ,DOCUMENT_TYPE" & vbCrLf _
           & "      ,DOCUMENT_NUMBER" & vbCrLf _
           & "      ,MILESTONE" & vbCrLf _
           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
           & "      ,MILESTONE_DATE " & vbCrLf _
           & "  FROM CASE_MILESTONE_TEMP"
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute
'THIS ^^^ IS THE COMMAND THAT IS FAILING. IT HANGS FOR 5 OR 
'MORE SECONDS. DOING COUNT(*) ON THAT TABLE GIVES 0 
'RECORDS. THEREFORE THE FOLLOWING CODE JUST REMOVES 
'EVERYTHING FROM THE TEMP TABLE.

    strSQL = "TRUNCATE TABLE CASE_MILESTONE_TEMP"
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute

    strSQL = "INSERT CASE_MILESTONE_TEMP (" & vbCrLf _
           & "       CASE_IDENTIFIER" & vbCrLf _
           & "      ,DOCUMENT_TYPE" & vbCrLf _
           & "      ,DOCUMENT_NUMBER" & vbCrLf _
           & "      ,MILESTONE" & vbCrLf _
           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
           & "      ,MILESTONE_DATE)" & vbCrLf _
           & "      SELECT CASE_IDENTIFIER" & vbCrLf _
           & "             ,DOCUMENT_TYPE" & vbCrLf _
           & "             ,DOCUMENT_NUMBER" & vbCrLf _
           & "             ,MILESTONE" & vbCrLf _
           & "             ,MILESTONE_DATE_TYPE" & vbCrLf _
           & "             ,MILESTONE_DATE " & vbCrLf _
           & "         FROM CLEAN_MILE_DUPES"
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute

    strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute
End Sub

I want to remove all records that are duplicates on all fields. This code works perfectly on my machine, but deletes everything on the other two machines. It is failing randomly, it seems, and I'm stumped. I'm an Oracle person, and have been working with SQL Server only 4 months. Even so, I don't know that doing this with Oracle would make a difference.

Thank you so much for your help!

Teme64 215 Veteran Poster · Answer 4 · 2008-12-04T21:38:54+00:00

Here's a slightly modified code:

Option Explicit

Public cn As New ADODB.Connection
Public cmd As New ADODB.Command
Public rs As New ADODB.Recordset

Private Sub removeMileDupes()
    Dim strSQL As String
    '
    Dim ConStr As String
    Dim oCmd As ADODB.Command ' Use a local object. You may use global, but make sure you know what it does :)
    
    ' Add error handler
    On Error GoTo ErrorHandler
 
' I had to set and open connection object in here. You may do it before calling this sub
ConStr = "PROVIDER=SQLOLEDB;Data Source=JASMIN\SQLEXPRESS; INITIAL CATALOG=EditorTest; User ID=XXXXXX; Password=XXXXXX;"
Set oConn = New ADODB.Connection
oConn.Open ConStr

Set oCmd = New ADODB.Command
Set oCmd.ActiveConnection = oConn ' Set a connection for the command
' I think you might have missed this OR the connection's state wasn't Open
    
'  I commented this out. If this opens the connection, you may have to move Set oCmd.ActiveConnection = oConn after this block
    If cn.State = 0 Then
        'fileset says which set of files are being loaded at the moment
        Call doDBaction("openDB", fileSet)
    End If
    'clean_dupes tables have ignore duplicates flag on
    'vb catches the error and stops
    'so resume next.
    
    ' No need to Resume Next in here
    'On Error Resume Next
        
    strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
    oCmd.CommandText = strSQL
    oCmd.CommandType = adCmdText
    Set rs = oCmd.Execute
    
'    strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
'           & "       CASE_IDENTIFIER" & vbCrLf _
'           & "      ,DOCUMENT_TYPE" & vbCrLf _
'           & "      ,DOCUMENT_NUMBER" & vbCrLf _
'           & "      ,MILESTONE" & vbCrLf _
'           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
'           & "      ,MILESTONE_DATE)" & vbCrLf _
'           & "SELECT CASE_IDENTIFIER" & vbCrLf _
'           & "      ,DOCUMENT_TYPE" & vbCrLf _
'           & "      ,DOCUMENT_NUMBER" & vbCrLf _
'           & "      ,MILESTONE" & vbCrLf _
'           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
'           & "      ,MILESTONE_DATE " & vbCrLf _
'           & "  FROM CASE_MILESTONE_TEMP"

' My "simplified" table 
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)"


    oCmd.CommandText = strSQL
    oCmd.CommandType = adCmdText
    ' Now, here is the only place where Resume Next is needed
    On Error Resume Next
    Set rs = oCmd.Execute
    On Error GoTo ErrorHandler
'THIS ^^^ IS THE COMMAND THAT IS FAILING. IT HANGS FOR 5 OR
'MORE SECONDS. DOING COUNT(*) ON THAT TABLE GIVES 0
'RECORDS. THEREFORE THE FOLLOWING CODE JUST REMOVES
'EVERYTHING FROM THE TEMP TABLE.

    strSQL = "TRUNCATE TABLE CASE_MILESTONE_TEMP"
    oCmd.CommandText = strSQL
    oCmd.CommandType = adCmdText
    Set rs = oCmd.Execute

    ' My "simplified" table again. I dropped your strSQL somewhere...
    strSQL = "INSERT CASE_MILESTONE_TEMP (" & vbCrLf _
           & "       CASE_IDENTIFIER" & vbCrLf _
           & "      ,MILESTONE_DATE" & vbCrLf _
           & "      ,TEXT_FIELD) " & vbCrLf _
           & "      SELECT CASE_IDENTIFIER" & vbCrLf _
           & "       ,MILESTONE_DATE" & vbCrLf _
           & "       ,TEXT_FIELD " & vbCrLf _
           & "         FROM CLEAN_MILE_DUPES"
    oCmd.CommandText = strSQL
    oCmd.CommandType = adCmdText
    Set rs = oCmd.Execute

    strSQL = "TRUNCATE TABLE CLEAN_MILE_DUPES"
    oCmd.CommandText = strSQL
    oCmd.CommandType = adCmdText
    Set rs = oCmd.Execute
    
Sub_Exit:
  oConn.Close
  Exit Sub
ErrorHandler:
  ' Handle errors here (this is just to trap errors)
  Resume Sub_Exit
    
End Sub

I tried your code and it hang up. Then I removed On Error Resume Next and started to trap errors. First thing that failed in the code was missing Set oCmd.ActiveConnection = oConn i.e. command object did not have an associated connection object.

Second, I used a local command object. You may use a global one, but you have to make sure that it's valid in your subroutine. Besides, you can always pass command and/or connection objects as ByRef parameters rather than use global ones.

I moved On Error Resume Next to the only place where it's actually needed (remove duplicates) and then turned error handling back.

Now the code works just fine for me. I hope this helps you a bit :)

pixelmeow 0 Newbie Poster · Answer 5 · 2008-12-04T21:47:58+00:00

I'm debugging right now, and of course it's working fine on my machine.

One of the other databases didn't have 0 records in the milestone table, it just had about 200,000 less records than it should have.

I'm going to try your advice above, and let you know what happens. Thank you so much!

pixelmeow 0 Newbie Poster · Answer 6 · 2008-12-04T22:13:15+00:00

Here's a slightly modified code:

'    strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _
'           & "       CASE_IDENTIFIER" & vbCrLf _
'           & "      ,DOCUMENT_TYPE" & vbCrLf _
'           & "      ,DOCUMENT_NUMBER" & vbCrLf _
'           & "      ,MILESTONE" & vbCrLf _
'           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
'           & "      ,MILESTONE_DATE)" & vbCrLf _
'           & "SELECT CASE_IDENTIFIER" & vbCrLf _
'           & "      ,DOCUMENT_TYPE" & vbCrLf _
'           & "      ,DOCUMENT_NUMBER" & vbCrLf _
'           & "      ,MILESTONE" & vbCrLf _
'           & "      ,MILESTONE_DATE_TYPE" & vbCrLf _
'           & "      ,MILESTONE_DATE " & vbCrLf _
'           & "  FROM CASE_MILESTONE_TEMP"

' My "simplified" table 
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)"


    oCmd.CommandText = strSQL
    oCmd.CommandType = adCmdText

    ' Now, here is the only place where Resume Next is needed
    On Error Resume Next
    Set rs = oCmd.Execute
    On Error GoTo ErrorHandler

Just got this far. SELECT COUNT(*) FROM CLEAN_MILE_DUPES = 0. No error, it just went to the next line. This is on *my* machine! <going to go tear out my hair>

I tried your code and it hang up. Then I removed On Error Resume Next and started to trap errors. First thing that failed in the code was missing Set oCmd.ActiveConnection = oConn i.e. command object did not have an associated connection object.
Second, I used a local command object. You may use a global one, but you have to make sure that it's valid in your subroutine. Besides, you can always pass command and/or connection objects as ByRef parameters rather than use global ones.
I moved On Error Resume Next to the only place where it's actually needed (remove duplicates) and then turned error handling back.
Now the code works just fine for me. I hope this helps you a bit :)

I want your code to work for me! :)

SQL Insert statement syntax

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers