Let's open up the mailbag
! In today's exciting episode, Nemo writes:
Hi, I am doing a project that my boss "requires" to be done in Access,
even though we have MS SQL server 2005! Well, the problem is: I dont
seem to find anything equivalent to a cursor ( I havent reached the stage
where I can handle everything with a set based solution, and I dont
even know how many kinds of joins Access supports). The next worst thing
to a cursor is a while loop. Cant seem to find that either. Am I missing
Great job on Jet SQL to T-SQL conversion webpage btw.
It's funny, the lack of cursors is actually one of the best
features in JET/Access!
You can do anything in Access in set-based manner that you can do in SQL Server, with the exception of table-valued user-defined functions. Access supports INNER JOINS, LEFT OUTER JOINS, UNION / UNION ALL, and CROSS JOIN (implicitly -- don't state the join) which is all you need to do things set-based. It also supports correlated sub-queries and derived tables
. You may need to break your SQL down into steps and store each step as a separate "query", but it works well. For example, I would often create a database with a sequence of queries like this:
And so on ... you can automate the calling of these steps via VBA or with a Macro. For complicated SELECT queries that required derived tables, you simply break it down into separate queries that build upon one another, again using a good naming convention to keep things organized.
If you really need to use a cursor, your only option is writing some VBA. Using VBA, you can open a Recordset
and loop through the rows and do whatever you need. But overall, I strongly, strongly recommend against this - do it set-based if at all possible.
Thinking about Access brings back great memories ...
(Warning: here comes a "Lost-style" flashback sequence! But stay with me, there is eventually a point, I promise! )
My first "real" job was primarily as a report writer, using Access and Crystal reports. I also started developing some simple Access database applications as well during this time. I had absolutely no relational database experience at all. Everything I learned about data types, joins, relations, keys, constraints, etc was learned by "playing" with MS Access. It was mind-blowing to work with a database and that whole "set-based thinking" concept really was a struggle for me. However, as far as I knew, set based was the only way to do anything with a database!
There was simply no other way to get at the data -- it was a SQL query or nothing.
By using Access and writing set-based SQL queries (SELECT, UPDATE,INSERT, DELETE) and nothing else, I quickly learned:
- Why normalization "works". I didn't read about it in theory and try to apply it to be correct, I simply learned through experience that having Jan,Feb,Mar,etc columns in table, or stuffing values into a CSV string, or having Contact1 and Contact2 columns just didn't work! I stumbled upon normalization over time and learned that storing related data as rows in related tables make things easier, quicker and cleaner. And that's the best way to learn, isn't it? It was only after a few years that I even heard of normalization and I proudly realized "hey, that's kind of what I am doing!" Don't get me wrong, my designs were still a mess and I still had a lot to learn about data modeling, but the point is: I understood the benefits normalization right away from experience so I was motivated to learn more and to do it.
- How to think in sets. I had no idea what a cursor was. I had no idea you could even process rows one at a time -- I thought it was impossible! And it really irked me -- I wanted to step through the rows one by one, but since I couldn't figure it out, I had no choice but to sit there and think to myself "how can I do this with a JOIN? Or in a single UPDATE? or with an intermediate temp table?" Again, if cursors were easily available, who knows if I ever would have really understood or embraced relational database programming with SQL -- I probably never would have since I never would have been motivated.
- How to break things down into steps. Using the query designer in Access, you had no way to write a derived table. Your only option was to break things down into smaller, simpler queries and to build them one upon another. Again, at the time this may have seemed like a drawback, but it taught me the valuable lesson of breaking down SQL into small pieces, testing them on their own, and then putting it all together at the end. Once again, would I ever have learned this if I just started typing away in Query Analyzer and building messy, convoluted queries in T-SQL? It's hard to guess, but probably not! Like the lack of cursors, requiring users to write queries piece by piece and to slowly build them is actually a great benefit of MS Access, in my opinion.
So, I used Access and learned quite a bit over time, but in general lots of the limitations of Access that I encountered actually made me a better programmer. Could you imagine where I'd be today if Access supported FULL OUTER JOIN
s? My goodness .... that's probably all I would ever use!
Anyway, I heard of these fancy things called "cursors" from a friend who was taking a database course at my school and they seemed amazing!
I explained how I was stuck using Access at work and it didn't have that advanced feature, and my friend simply could not imagine database programming without them! I found out they were a primary focus of the course he took and many lessons centered around using them. (I don't recall what product they were using; it was all UNIX at my school back then.)
Eventually, I uncovered the VBA Recordset technique to finally get cursors in Access. I struck gold! Finally, I could do some real programming! I quickly sought to replace those silly queries with VBA code that was much more complicated and complex than any of those simplified queries could ever be, and I felt like a "real" programmer again at last!! Of course, the result was ... a mess. A slow, convoluted, ugly, buggy mess. Before too long, I realized that cursors are simply not necessary for 99% of the things you need to do with a relational database, and in fact, they were not only much longer and harder to write, they were much, much, much slower! Not twice as slow -- but hundreds of times slower! And, before too long, the VBA code was mostly gone, and from that point on I was thinking set-based all the way.
When I finally began using SQL Server, with native T-SQL support for cursors of course, I never even considered learning the syntax. (I still don't know it!) They were never an option. And the same with always normalizing schemas, using correct data types, foreign key relations, intelligent primary keys -- from my Access experience, I had developed a great initial understanding of the importance of these concepts. That, in turn, made me a much better SQL programmer overall.
The moral of this story? Well, there really isn't one, other than maybe this: Access has it's place, and it is a great
tool if you use it properly and for what it was designed. But more importantly, you can learn a lot
about relational databases and best practices and good techniques by using Access, believe it or not.