SQL to Access VBA

[hcaburks]

How can I convert the following SQL to work in Access?

declare @cursor_row_id int,
@cue_counter int

-- create cursor and load all the records...
DECLARE cursorCue CURSOR FOR
select [ID]
from Test

OPEN cursorCue

--grab the first record
FETCH NEXT FROM cursorCue INTO @cursor_row_id

-- begin: loop1 to go thru each record in cursor......
WHILE (@@FETCH_STATUS = 0)
BEGIN

-- reset @cue_counter (counter)
set @cue_counter = 1

-- begin: loop2, will only loop only 6 times.........
WHILE @cue_counter <= 6
BEGIN
update Test
set [Cue Number] = @cue_counter
where [ID] = @cursor_row_id

-- grab the next record
FETCH NEXT FROM cursorCue INTO @cursor_row_id

-- increment @cue_counter by 1
set @cue_counter = @cue_counter + 1

END
-- end: loop2 ------------

END
-- end: loop1 -------------

CLOSE cursorCue
DEALLOCATE cursorCue

[Delerna]

Why would you even consider using a cursor to do that?
Or is this homework ??

[hcaburks]

Why would you even consider using a cursor to do that?
Or is this homework ??

It's not homework. I am building a new database for my company only I only have expierance in SQL code, and not in VBA which is used in Access. Is there a way in VBA to accomplish the same thing?

[Stewart Ross]

Hi. You can accomplish similar row-by-row processing in VBA using DAO or ADO recordsets, and I recommend that you look up the Access help information on the OpenRecordset method as a starter (as there are examples of its use within the help information).

You will also find examples of recordset processing in the responses in Bytes and in our HowTo section if you search for the word Recordset.

Having said that, working back from what you have posted I agree entirely with Delerna - I can see no reason at all not to use standard SQL (without loops) to do basic updating of tables, which is all the loops appear to be doing (at least from what can be inferred without knowing anything about your application, the tables involved, or what the loop is intended to do).

-Stewart

[Delerna]

From what I can see in the code posted. The code equates to this query
[code=sql]
update Test
set [Cue Number]=6
[/code]

which would do the same thing as your cursor example (unless I am missing something)
This is why I am puzzled as to why you are using cursors.