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?
LVL 6
HyperBPPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
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
0
PatHartmanCommented:
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.
0
HyperBPPAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kelvin SparksCommented:
If you've written your loop in T-SQL, put it into a stored proc, then call it using VBA.

Kelvin
0
pcelbaCommented:
No, this is not possible in MS Access but it could be possible to write some loops as one complex SQL command.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
If you are talking about using a cursor in Access SQL, the answer is no.  But if you are trying to move more to SQL Server then I agree with Kelvin that you should consider calling a SQL stored procedure from Access.

You could, create a pass-through query, which uses SQL Server syntax, and pass that query through to the SQL Server, but if you want that functionality, just build the stored procedure in SQL Server and simply call the SP with whatever parameters are appropriate.

Dale
0
PatHartmanCommented:
You have to decide where it makes the most sense to have the code.  Access' VBA is a more friendly development environment than stored procedures in SQL server.  As long as you are not working with ANY local data or need to reference an open form, a sp will be fine.  If the process is more interactive, then you should probably create it in VBA and run it locally.  As long as you are not bringing down hundreds of thousands of rows, it probably won't take an excessive amount of time in Access.

Typically, I separate real batch processing from form driven batch processing.  If something can run unattended, over night, it becomes a stored procedure and runs on the server.  If it is something triggered by a form such as copying a set of data and appending it with a different foreign key, I do it locally using VBA.  So, one of my active clients needs to send an update file to the state every day so they can record changes we have made to my client's clients.  That is a true batch process and runs off hours.  It creates a .txt file and FTP's it to the state.  There is no reason to run it during the day and on the rare cases where we have had to, it slows down the interactive processes while it runs.  But I have lots of other cases where I have to run code loops on much smaller sets of data that run based on form actions.  Making them stored procedures would be over kill and probably not save enough time to justify the effort.
0
Nick67Commented:
If your backend is Access, then no.
You cannot create loops purely with Access SQL.
If your backend is SQL Server (Express Edition, free. or full-blown) then you have the full power of T-SQL at your command.

In either case, loops remain a much slower, last resort to set logic when that option exists.
Don't use loops when you aren't forced to!

However, I'd look to move as much processing to SQL as I can for later conversion reasons.
Do your conversion first, then!
Having the skillset I do now, I wouldn't start a greenfield project with anything other than a SQL Server backend.  Saves all the conversion hassle later!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.