Link to home
Start Free TrialLog in
Avatar of HyperBPP
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?
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Using VBA, you can  add or edit records in a recordset in Access - or even reference SQL Server directly and undertake various data related T-SQL tasks. You shoud view VBA as the T_SQL of Access.


Kelvin
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.
Avatar of HyperBPP
HyperBPP

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
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial