HyperBPP
asked on
Can I execute procedures with loops that go through recordsets in pure SQL in ms access
Does MS Access 2013 SQL allow procedures the way SQL server does? I'd like to build some record set calls that process the record set after querying. That can loop through records and create new records and so on. Or is this only possible use the VBA in MS Access?
I suggest DAO, which is the native Access object for processing recordsets. But ADO works if you are more comfortable with its syntax.
Don't create recordset loops for processes that can be done with a query. An action query will almost always be faster than a code loop. If you end up with recordset loops, make sure you sort the recordsets using an Order By clause in a query if order is important to what you are doing.
Don't create recordset loops for processes that can be done with a query. An action query will almost always be faster than a code loop. If you end up with recordset loops, make sure you sort the recordsets using an Order By clause in a query if order is important to what you are doing.
ASKER
Thanks for the replies. I can do in VBA with ADO just fine. However, I'd look to move as much processing to SQL as I can for later conversion reasons. Can I loop through SQL recordsets within pure SQL (Like T-SQL). I don't want to process the loop from VBA at all with or without DAO. Like T-SQL has the "WHILE" keyword for looping. Does Access SQL have anything like this?
If you've written your loop in T-SQL, put it into a stored proc, then call it using VBA.
Kelvin
Kelvin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Kelvin