Moving to SQL Server

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Apr-2001 >

Moving to SQL Server

by Russell Sinclair


PETER Vogel asked me to guest edit this special issue of Smart Access because of my experience with SQL Server and Access. With Access 2000 and ADPs, building applications with SQL Server is now as mainstream as creating applications with Jet. In fact, with further development on Jet halted, creating applications with SQL Server will become the mainstream.

When I did my first migration from Access to SQL Server a few years ago, I attempted to do a simple migration. I thought that if I just migrated the tables and data to SQL Server and linked the tables into my Access database, all would be well. After about a week of using the revised application, I realized that something had gone horribly wrong. Queries that I’d relied on to supply the data for many of my forms were suddenly returning read-only data. Other queries that I’d used that contained functions were now taking up to 10 times longer than they’d taken in Access. Performance had gone from bad in Access to terrible in SQL Server. I was beginning to wonder why anyone would make the move if this were the result.

I decided to call on one of the DBAs we had in the office and ask his opinion of what was going on. I took him on a quick tour of the application I had developed, and he asked that I give him a day or two to play with it and then he’d get back to me. After a couple of days, I received an e-mail from him that was a little over two printed pages of all of the things I was doing that, although they’d been good choices for Access, were entirely wrong for working with SQL Server. It started with the fact that I hadn’t used a single view or stored procedure in the database, and it went on from there.

The truth of the situation was that I didn’t understand SQL Server and therefore couldn’t know what to do in order to take full advantage of the features it had. My colleague suggested a few good books on SQL Server and took the time to make a few modifications to the database so that I had some examples of how to apply the knowledge I’d get from the books to my revised application.

Since that time, I’ve read dozens of books on SQL Server. There was a lot to learn along the way, and I’m glad I took the time to do so. I’ve since performed many successful migrations, including at least one project where I used a feature in Microsoft SQL Server 7 called Data Transformation Services to merge 40 similar Access databases into one Sybase Adaptive Server database with one click of a button. SQL Server is a wonderful tool, and now I can’t think of developing an application that doesn’t use it.

In order to help you avoid the same mistakes I made, this issue of Smart Access is devoted to helping you understand some of the features you can use in SQL Server and some of the choices you’ll have to make when you choose to migrate your Access application. Burton Roberts shows you some advanced features in SQL Server, demonstrating how to make the move from VBA to TSQL. Andy Baron and Mary Chipman’s article introduces the concepts involved in SQL Server security and how it affects your development in Access. My article takes you through the differences between views and stored procedures and where to use each object type in your application. And Mike Gunderloy introduces you to forms and how to use them in an ADP.

Making the move to SQL Server is a big step in your development, and ensuring that such a move is successful is absolutely essential. This issue will prepare you for the time when you make that move. Understanding SQL Server is key to understanding half of the functionality Access can now provide.