How to read a table record by record and process and update another table

[Karen Middleton]

Jeff

I have a scaled down application running on SQL Server. I SQL we are
using cursors in stored procedures in many places to things like the
following:


DECLARE myCursor CURSOR FOR
SELECT au_id FROM authors FOR UPDATE
DECLARE @au_id VARCHAR(10)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @au_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DELETE FROM otherTable WHERE au_id = @au_id
DELETE FROM authors WHERE CURRENT OF myCursor

FETCH NEXT FROM myCursor INTO @au_id
END
CLOSE myCursor
DEALLOCATE myCursor



Please note this is jst one example there are many such scenarios
where I use a cursor for various processing I want to do a similar
thing in Access with VBA open a query for select and step through each
recordset and do various processing on other tables. Also, like in SQL
Server cursor can permit a select for update I would like to do the
same in Access.

Please note I cannot use MSDE for this application which is running
currently over users want this small application in Access and hence I
am converting from SQL to Access but I am plagued by several problems
including lack of stored procedures in Access but our users want
Access I am helpless.

Please let me know how I can do these things as above in Access
Macros/VBA in DAO or ADO


Thanks
Karen

 

[Albert D. Kallal]

When you use sql server, your stored procedures are the t-sql language. We
all know that T-sql is not a very good language, and is missing all kind of
things like debuggers, and for intensive purposes is a hard to language. Of
course, when using sql server, t-sql is all you got!! The new versions of
sql server will actually (finally) allow developers to write code in their
language of choice (vb.net).

Of course, using ms-access, we have been spoiled, and for 10+ years have
always enjoyed the incredible advantages that our database code is in fact
VB code. Thus, all of the cool things like a good VB6 IDE, a debugger, and
even using class objects is standard fair as part of the development system
in ms-access!

So, to write code that process data, you will have to use the built in VBA
in ms-access. You can use ADO, or DAO (you don't mention which version of
ms-access you are using).

However, here is a example of a "loop" that process a table..and it should
get/give you some ideas and get you started.
Dim rstAuthors As DAO.Recordset

Dim strSql As String

Set rstAuthors = CurrentDb.OpenRecordset("select * from authors")

' process untill end of file
Do While rstAuthors.EOF = False

CurrentDb.Execute "delete from otherTable where au_id = " &
rstAuthors!au_id
CurrentDb.Execute "delete from authors where au_id = " &
rstAuthors!au_id

rstAuthors.MoveNext

Loop

rstAuthors.Close
Set rstAuthors = Nothing

However, in looking at the above example, you can see that I deleted each
author after I am done..and in fact, the above would be much faster if we
first processed the look..and then deleted all authors.

So, we likely use:

Set rstAuthors = CurrentDb.OpenRecordset("select * from authors")

' process untill end of file
Do While rstAuthors.EOF = False

CurrentDb.Execute "delete from otherTable where au_id = " &
rstAuthors!au_id

rstAuthors.MoveNext

Loop

rstAuthors.Close
Set rstAuthors = Nothing

currentDb.Execute "delete from authors"

Of course the above deletes all records fro authors..and you might not want
that..but again the above is just example code. And, if we actually look at
the above, we are deleting all records in otherTable that in fact have a
author ID..and thus we really don't need a loop..but in fact could simply
use a whole sql statement to do the above.

 

[Jeff Boyce]

Karen

Albert offers some technical approaches to what you are trying to do. My
question still stands... if you step away from the nuts and bolts of "how",
I asked "why"? What business purpose are you trying to satisfy by moving
data from one table to another, identically-structured table? There may be
other ways to handle/solve the business need, but you'd need to let the
'group know what that was...