Microsoft Access - how to create a storedprocedurein microsoft access that uses a cursor

Asked By Sujit P on 28-Jan-12 07:58 AM

0 down vote favorite

I have a storedProcedure in SQL-Server that I am using to delete duplicates from one of the tables. This storedprocedure makes use of a cursor.

I tried to create the same storedprocedure in microsoft access by just replacing the 'CREATE PROCEDURE' with 'CREATE PROC' but it didn't seem to work.

Can anyone provide some workaround?

Here is the SQL- storedprocedure:-

ALTER PROCEDURE [dbo].[csp_loginfo_duplicates] AS BEGIN

SET NOCOUNT ON;

declare @minrowid bigint declare @empid nvarchar(15) declare @dtpunched datetime declare @count tinyint

declare curDuplicate cursor for select empid,dtpunched,count(*),min(row_id) from loginfo group by empid,dtpunched having count(*)>1

open curDuplicate

fetch next from curduplicate into @empid,@dtpunched,@count,@minrowid

while (@@fetch_status=0) begin delete from loginfo where empid=@empid and dtpunched=@dtpunched and row_id<>@minrowid fetch next from curduplicate into @empid,@dtpunched,@count,@minrowid

end

close curDuplicate deallocate curDuplicate END

D Company replied to Sujit P on 28-Jan-12 08:23 AM

hello friend ,

do this in following manner,

Create a query by using the Find Duplicates Wizard. By default, the query returns matching records only when the values in each field match character for character. If you need to find partial matches, you can use an expression in your query, or you can alter the Structured Query Language (SQL) code.
Optionally, edit the field values or delete records when viewing the query results in Datasheet view.
Optionally, alter the Structured Query Language (SQL) code in the query to look for values that partially match. If you don't alter the SQL code, the query returns only those records where the values in the specified fields match character for character.

for more detail visit the official site of microsoft

http://office.microsoft.com/en-us/access-help/find-hide-or-eliminate-duplicate-data-HA010096301.aspx#BM3

Regards
D

Venkat K replied to Sujit P on 28-Jan-12 10:58 AM

The SQL Cursor doesn't work in MS Access, you need to loop through reordset in vba to get it make work as CURSOR:
Ex:

sql = "SELECT * FROM Customer"
' Create the Recordset
Set rcd = CurrentDb.OpenRecorset(sql)
Do While Not rcd.EOF
    If rcd("Age") > 30 Then     ' Prepare for update
    rcd.Edit

    ' Now we can update
    rcd("Salary") = rcd("Salary") * 2

    ' The row is ready to be saved
    rcd.Update
    End If
    ' Go to the next record
    rcd.MoveNext
Loop