Click here to Skip to main content
14,640,713 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Updated 2-Oct-12 8:19am
v3

1 solution

Rate this:
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)
   
v2

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




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