Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET MS-Access
Hi everyone,
 
I'm working in a application with SQL Server 2008 Database in VB.NET 2010. I converted my SQL database now to MS-Access. Now i got an error in my sql query, i don't know how the structure of query in Ms Access to Delete Duplicate records.
 
Here's my working code in Sql Database to delete duplicate:
 
Connect()
        SQLQuery = "WITH DuplicateRec AS (SELECT *,row_number() OVER(PARTITION BY M_ID,name,contact ORDER BY M_ID) AS RowNum FROM tblContact) DELETE FROM DuplicateRec WHERE RowNum > 1"
        cmd = New SqlCommand(SQLQuery, conn)
        cmd.ExecuteNonQuery()
        conn.Close()
 
'When i move to Ms Access i got an error:
 
Connect()
        SQLQuery = "WITH DuplicateRec AS (SELECT *,row_number() OVER(PARTITION BY M_ID,name,contact ORDER BY M_ID) AS RowNum FROM tblContact) DELETE FROM DuplicateRec WHERE RowNum >1"
        cmd = New OleDbCommand(SQLQuery, conn)
        cmd.ExecuteNonQuery()
        conn.Close()
 
Error:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
 
Pls help in my code.Thanks
Posted 2-Oct-12 8:11am
Edited 2-Oct-12 8:19am
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

MS Access database engine (JET) does not recognize WITH and OVER commands.
 
To find duplicates in your table, use something like this:
SELECT [FieldWithDuplicates] AS [Value], COUNT([FieldWithDuplicates]) AS [CountOfDuplicates]
FROM YourTable
GROUP BY [FieldWithDuplicates]
HAVING COUNT([FieldWithDuplicates])>=1
 
To find non-duplicates, you can use LAST[^] or FIRST[^] or DISTINCT[^] functions too, for example:
SELECT DISTINT [FieldWithDuplicates]
FROM YourTable
 
To delete duplicates and leave the newest values, use:
DELETE *
FROM YourTable
WHERE [ID] NOT IN (SELECT LAST([ID]) FROM YourTable)
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.141022.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100