By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

SQL to Access VBA

P: 2
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
Aug 15 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
Why would you even consider using a cursor to do that?
Or is this homework ??
Aug 15 '08 #2

P: 2
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?
Aug 18 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Aug 18 '08 #4

Delerna
Expert 100+
P: 1,134
From what I can see in the code posted. The code equates to this query
Expand|Select|Wrap|Line Numbers
  1. update Test
  2. set [Cue Number]=6
  3.  
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.
Aug 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.