Click here to Skip to main content
12,242,000 members (47,155 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB Access VB.NET
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 9:11am
Edited 2-Oct-12 9: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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160426.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2016
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