What I learned about SQL from using Access

Wed Jun 13, 2007 by Jeff Smith in miscellaneous, techniques, ms-access

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
something?

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:

Import01:EmptyData
Import02:CopyToStaging
Import03:CreateTempTable
Import04:UpdateTotals
Import05:CopyToLive

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 JOINs? 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.

Legacy Comments


JohnFx 2007-06-14	re: What I learned about SQL from using Access Really good article. MS Access really gets a bad rap, but mostly from people who haven't bothered to give it a fair shake. There is a ton of mythology about it. Cheif among the unfair complaints is that Access is a low rent version of SQL server for people who don't care about performance or have really small databases. Completely untrue. Access Truths... Access CAN handle large databases and perform well. For every anecodote I hear about an Access DB that had bad performance and was "Upgraded to SQL Server" solving the performance issues, I can point to tons of examples of really large databases performing well in Access. tens of millions of rows in an access table is not an issue in a well designed database. The reason you see so many badly performing access databases is that Access is a bit too approachable. It is not uncommon to see a non-programmer throw together an Access DB that somehow becomes mission critical infrastructure, but the idea of this happening on a SQL Server installation is a bit more unlikely because it is not as approachable and wasn't installed with MS Office (a set of end user tools). In the hands of a qualified DB programmer Access performs beautifully. Secondly what quickly gets forgotten is that "Upgrade" was not just a software upgrade, consider that an Access DB is not a client server DB and runs on the user's workstation. If you are going to pay for a SQL server license, chances are you aren't going to put it on that $499 Gateway computer, you are going to throw some decent hardware at it. I have a good buddy who is doing extensive complicated data analysis on some pretty large data sets using genetic algorythms, he was never able to even touch the performance of his Access version of the tools he built with a SQL Server implementation. I am not trying to imply here that Access is faster than SQL Server or vice versa, just that a client-server and desktop tool have important differences and you need to optimize your DB software selection to the parameters of your specific problem. The important differences between Access and SQL are: Access is a front end UI tool, report generator, that can be used as a database engine, whereas SQL is intended primarily as a back-office application. In fact, I would argue that MS Access is one of the best front end/Reporting tools to use against a SQL database, especially for rapid prototyping and data mining or analysis. Crystal Reports has nothing on the Access Report builder, it is extremely intuitive and well honed over the last 14 years or so. SQL outshines access mostly in features related to back-end data support, not suprisingly since this is what it was designed for. The ability to do backups and avoid data corruption without constatnly taking the system down for maintenance, and running scheduled jobs on the data are good reasons to upgrade a DB back-end from Access to SQL, not because you have 10 users or X rows in a table. The Access MDB format however is great for deployment of client-side apps. Its a snap to include an MDB file with your VB or C++ installer. Further, SQL finally got the ability to use VBA type procedural programming against database objects, Access has had this since around 1993. * End Rant

Jeff 2007-06-14	re: What I learned about SQL from using Access JohnFX -- WOW! that was a GREAT post. I agree with literally every word you wrote, thank you so much for taking the time to write a great post; it is nice to know that some people out there "get" Access and understand what it was designed for and how to use it well. I've said over and over -- there is NO better database prototyping tool than Access, and no better data conversion tool. If I need to convert data from System A to System B, Access is perfect: it can link tables to both systems as well as any others out there that I need via ODBC, I can create local lookup and mapping tables, I can cache data locally as necessary, I can create data entry forms where manual entry is needed, I can add complicated reports with sub-reports and all kinds of advanced features, I can add macros that run things automatically, I can write VBA code that can use any ActiveX DLL to do pretty much anything I need -- all in ONE .mdb file! It's really, really a great tool when used correctly and any good analyst should give it a shot. There is simply no quicker way to get some tasks done than using Access if you actually understand how to use it. Thanks again, John !

Nemo 2007-06-14	re: What I learned about SQL from using Access Hi Jeff, thanks for your advice. Just wanted to mention that multiple joins in Access can be a bit of a pain syntax wise. It shouts at you if you don't enclose the joins in parenthesis. I ran into trouble while I was trying to do a simple inner join and a left outer join together. Chances are that you are already aware of it, but just for reference, the following link describes the problem and solution succinctly. http://cfsilence.com/blog/client/index.cfm/2006/9/5/Multiple-Outer-Joins-in-Access Thanks again Jeff.

Jeff 2007-06-14	re: What I learned about SQL from using Access Nemo -- very true, that is definitely an annoying aspect of JET SQL. I usually just use the visual query designer, to be honest, most of the time in MS Access because of this. it works fine for 99% of the SQL that I need to write, and sometimes it is nice not to type in the SQL statements....

Ned 2007-06-22	re: What I learned about SQL from using Access Thanks for your post. But, errm, OK. Sure, cursors are not the best choice when they are not needed. (Same with full outer joins.) But what about when they are needed? For example, when you have to append the values in a single column into a column of lists: Division Salesman --------- ----------- EAST Jones WEST Smith WEST Brown WEST Thompson NORTH Cole NORTH Simmons SOUTH Stanton and the result you want is: Division Salesman List -------- ---------------- EAST Jones WEST Smith, Brown, Thompson NORTH Cole, Simmons SOUTH Stanton I don't think the fact that Access only supports "good" kinds of queries gives it any sort of advantage over SQL Server. I learned the syntax for cursor operations in SQL before I really had a handle on set-based operations (having come from the dBASE world), but I also took to heart the advice, not fully understanding it yet, that cursors were to be avoided when possible. I use cursors in SQL rarely, and when I do, I look up the syntax in the online books. As you yourself said, once you learned to use VBA recordsets you started to write ugly code, so Access's lack of a cursor didn't prevent you from writing bad code, it just made it more inconvenient (as well as making it more inconvenient to do what you needed to do) -- how is this an advantage?

Jeff 2007-06-24	re: What I learned about SQL from using Access Ned -- you don't need a cursor at all to do concatenation like that: http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx A simple UDF that does this is the way to go, in both Access and in SQL Server. Also, I think you missed the entire point of my post. You wrote this: >>As you yourself said, once you learned to use VBA recordsets you started to write ugly code, so Access's lack of a cursor didn't prevent you from writing bad code, it just made it more inconvenient (as well as making it more inconvenient to do what you needed to do) -- how is this an advantage? The inconvenience factor is what I was writing about; since Access made it inconvenient to write bad code (i.e., using CURSORS), I never learned that bad habit in the first place. My entire point is simply that using Access "trained me" to think in set-based solutions all the time since that was the simplest and most obvious way to do things using that Product. I never tried to claim that Access was "better" than SQL in any way because it didn't support CURSOR syntax!

Piyush 2007-07-25	re: What I learned about SQL from using Access I am a newbie to MS Access and its funny because although I appreciate some of the features ACCESS provides, somethings I still can't figure out. If you guys have answers to this, great!! > Without using the IIF function, how can I do a SELECT CASE statement? MS Access does not even support SELECT CASE WHEN..THEN statements. How do you go about this? I am hitting an Oracle database. Since I have over 30 Select CASE statements, it will make IIF (..IIF(..IIF...))).. almost impossible to write out. I heard you can use some sort of "Native SQL" and make the MS Access understand SELECT case. Where is this option?

Jeff 2007-07-26	re: What I learned about SQL from using Access piyush -- If you have large nested CASE statements all throughout your code, then you have bigger issues than worrying about how they translate into JET SQL. Take some of that logic and see if storing more data -- look up tables, mapping tables, translation tables -- in your database will help simply things. Otherwise, you have no choice. IIF() will work fine. There are also SWITCH() and a CHOOSE() functions supported by Access (not sure if it's JET or VBA, probably VBA) that might be helpful to you. As for the "native SQL", if you are querying an Oracle or any OBDC database, you can create a pass-through query (search for that term) where you direclty pass a SQL statement to the source. Whatever SQL is valid at the server can be passed directly and the server will evalaute and execute the code and return the results to Access. You can also call stored procedures via Access pass through queries as well (though passing parameters can be difficult or clumsy) or through VBA using ADO.

Manie Verster 2008-02-29	re: What I learned about SQL from using Access I am sorry if I sound dumb here and yes, a bit late as well. When I started programming we were developing a system in MSAccess but we did not use the Jet database option but with a .adp connected to a SQL Server database in which you have (those days in Windows and Office 2000) stored procedures in which you can do cursors. Ok, ok, don't bite my ear off now. I know that a person is not gonna create a SQL Server database for every little project you want to do in Access but like someone said they have a SQL Server 2005 database and now he has to do a program in Access. Then you would also not have a problem with SELECT CASE WHEN THEN clauses. I suddenly realised now we we no more use Access. Microsoft, when XP and Office 2003 came out, with all the security patches made it impossible for us to use our system in Access. Wow now I am contradicting myself but for small projects Access together with SQL Server 2005 is perfect. I am currently using it as a report writer and work famously with cursors, SELECT CASE etc. I love ACCESS because I learned on it and will always go back if I am not sure how something works. I hope this makes sense because I dunno if it does to me. Manie Verster South Africa

Charles Wannall 2008-04-09	re: What I learned about SQL from using Access I so rarely find professional developers sensible enough to respect Access for what it was capable of that I always chase up comments about its capabilities. Brings back fond memories of a hard-to-beat RAD environment where, as should be the case always, the tool did all the heavy lifting, just short of writing recordset stuff for you. I still have access-based applications in the field, including one that handles engineering change requests and extensive comment-gathering between Houston and Florida for a NASA contractor) and one that does "cradle-to-grave" tracking of psychiatric trainees across years of post-graduate professional development in great detail. The former is a "distributed app", with a copy of the MDE on a file-server in each state. In addition to NOT having cursors, end encouraging that procedural approach that supports, it DID have a crosstab query that simply could not have been easier. I will confess, I miss the environment. But OK, .NET is pretty cool, and if they keep studying the things that were put together first in Access, they're going to make it very slick on top as well as under the hood, where it is pretty awesome, certainly beyond anything I could understand enough to find fault with. Cheers CRW

Gordon Prince 2008-05-21	re: What I learned about SQL from using Access I enjoyed this Access post. I've been a professional database application developer for 20 years now. I've been using Access since version 1.1. The paradigm that keeps me with Access is that although the overhead can be higher than with some other application environments, I'm writing programs for small groups of users, usually 3-25. The $ saved by the time saved by using Access as the development environment significantly more than offsets the modest additional $ required to deploy to clients. Most of my applications are running against SQL Server. I'm doing business at significantly less cost than my competitors. Which means I don't have much competition, which means I'm making a good living by using Access. With other more upscale tools, I would be shut out of the markets I'm working in. I get dismissed by other programmers -- kind of the Rodney Dangerfield of programming. But I know I'm earning more than twice as much $ as they are since -- in this environment -- Access lets me deliver via RAD in a fraction of the time. I price below the competition, yet I make more profit than my competitors. I LOVE ACCESS!

Andrew Plaisted 2009-02-17	re: What I learned about SQL from using Access I love Access as well. To me it seems easier to use than the SQL Server. Far simpler to create tables and queries, in my opinion. The SQL View is a real bonus as well. I have of late been using VBA at my work and that being similar to VB6 has been a real help in producing a Master Form to access our data. The SQL View made that all possible. Kudos to MS Access.

GMan 2009-10-27	re: What I learned about SQL from using Access Wow, it's funny, I had an exaclty similar career and came to the same conclusions. Now working full time in SQL 2005 I have still not learned how to write a cursor!