Recently I have had another of those interesting learning curves. I have spent the past 18 to 20 years attempting to learn to develop database applications exclusively using MS Access. I started with Access 2.0 and up to now I have developed in every version of Access since 2.0.
I have even had experience using “views” provided by some database administrator as a data source in my project, but I had never attempted to really get in to the use of SQL Server.
As I said, my latest development project actually required that I not only use SQL Server that that I also learn to create Stored Procedures. While I do not consider myself to be even close to a guru with SQL Server, I can say that after spending the past several months developing a new “cloud” based application I am certainly much more fluent in it that I was before.
After reading many post on some of my favorite forums, it seems that I started out much the same as many of you have indicated that you started. I simply created linked tables to my SQL Server database which, by the way, is very reasonably hosted by a local hosting provider on his web server.
Using the “linked table” method I started with the development of my application. (Perhaps I should make it clear here that I am actually developing this application for my own use. It will be a commercial database application that will replace a desktop database application that I wrote and deployed almost 15 years ago.) I was very pleasantly surprised withj the speed at which my queries and forms were being populated. As with any project things stared out rather simple as far as queries and/or forms were concerned.
However, the farther I went developing more and more complicated queries and forms, things began to slow down in certain place. Needless to say I was starting to think, “Big mistake moving to SQL Server. This going to be way too slow.” Then someone suggested that I tap into the power of Stored Procedures. I can say that I knew just enough about them to be very dangerous. So I started to read and study but I was not getting where I needed or even wanted to go fast enough. Then I remembered that I had a really good friend that was a real guru with SQL Server. In fact that is what he does everyday. He is currently creating SSIS packages at his new job. So, instead of struggling along and wasting my time on reading, I decided to call Jimmy. I want to give credit where credit is due. My friend, Jimmy, has provided the most tremendous help that anyone could have ever asked for.
Come back in a day or so for more about this SQL learning curve. I will be sharing my experiences with leaning to get some real speed into my application using stored procedures. I will also be sharing my experience with pass-through queries.
For those of you who have had problems deploying you Access 2010 applications on Windows XP machines where you get the error message “ODBC call – fail …”, I have some news about a possible fix.
More to come in a day or so ….