By: Tim Ford | Updated: 2010-04-28 | Comments (4) | Related: > Upgrades and Migrations
Problem
Access. Yes, I said Access. Our topic of this tip is going to be transitioning from being an Access Power User into a role of SQL Server Professional. You'll notice that I'm taking great pains to avoid the terms "Developer", "Programmer", or "Database Administrator". I'm going to do my best in this series of tips on Access to be role-agnostic. You may wonder why I'm talking about Access. After all, I'm one of those guys out on Twitter and in my blog that make fun of those who call Access a database platform. I do so all in good fun however. All tools have their purpose in the I.T. Toolbox - Access included. Many SQL Professionals gained their start by using Microsoft Access. I should know; I'm one of them.
Many of you who know me, know that my background was not one originally geared towards technology. My formal education was in the concentrations of Accounting and Finance. I earned by Bachelor's of Business Administration in those disciplines in 1990. I graduated with that degree not because I wanted to, but rather I ran out of money and had to. I fell into a desktop publishing job and later converted to working as the corporate estimator for 10 years. I hated the job, hated the ownerhip and management and was looking for a way out. While an estimator, I wrote my own program using Excel and Visual Basic for Applications (VBA). I really enjoyed that aspect of my job more than all others. I ended up teaching myself Visual Basic and picked up one of those "Learn SQL in 24 Hours" books as well, because the language of T/SQL seemed extremely easy to learn. I was planning my escape and at the same time the fear factor of Y2K was looming large. I ended up getting my first I.T. job as an Access Developer in the waning days of 1999. The company that hired me (and that I still work for to this day) was frantically attempting to convert 4,000+ Access 7.0 files to Access 97; thus making them Y2K-compliant. There were two other individuals performing this task and they needed to hire one more. I was their plus-one. We successfully converted the files and I went on, along with one of the other Programmer/Analysts (per our title) to support custom development of Access application in our company for the next year. Eventually I transitioned into a role as a SQL Developer and finally the Primary SQL Server DBA and Subject Matter Expert (SME) for our company. My other two co-workers still work in different capacities in our organization: one is an Oracle DBA, the other continues to perform custom Access development; though with SQL Server as the database for his applications. He just so happens to be the only other Microsoft MVP in our company, but in the area of Access to my SQL.
Why did I just tell you all that? In order to tell you this:
Three people - three different paths taken from Access. This little piggy went to Oracle, this little piggy does SQL Server. One little piggy develops Access applications against SQL Server. This will frustrate the Editors of MSSQLTips.com who asked me to write a series of articles about how to transition from Access to SQL Server, but the real world is seldom a step-1, step-2, step-3 environment. I'll do my best here to explain different techniques, resources, and methods for making the jump between these platforms, but they are only suggestions; they will not exhaust all possibilities as you never can do so when dealing with qualitative subjects like the one I'll be undertaking here.
Solution
The first part of this article on transitioning between Access and SQL Server will focus on the initial suggested process for transitioning that I took - albeit with the 2007 version of Access to my more-familiar Access 2000. The subsequent tips in this series will focus on comparing the respective management tools for each platform, the common mistakes made when transitioning to SQL Server from Access and myths associated with SQL Server that many beginning users seem to take as fact. I'll also provide my suggestions for where to go for help with SQL Server. So what won't we cover here? This is not going to be an introductory series of tips for beginning SQL Server - SQL Server 101 you may call it. We have years worth of tips here that cover SQL Server from all angles and levels. I'll be tasking the editors to include links to tips they feel compliment the subject matter of this tip. Likewise I've started an initiative with my blog, with the upcoming Professional Association for SQL Server (PASS) Summit, and with SQLSaturday to include more entry-level sessions, articles, and topics for SQL Server. Please keep an ear to the ground if interested. Additionally, Jorge Segarra has enlisted some of the more-well-known Bloggers and SQL Professionals presenting and writing on the subject of SQL Server to work with him in his SQL University project which is aimed at those just starting out in their SQL Server career.
We all learn differently. Some pick up books and read cover to cover. Others only attack the books when they get stuck, while others jump around from chapter-to-chapter as they develop an interest in a specific task or function. The important thing I will try to drive home here is that books alone (and you can also substitute "websites" or "blogs" for "books") will not cut it for a transition process. You're going to need to get your hands on a keyboard and eyes planted firmly in front of a monitor that is displaying Microsoft SQL Server Management Studio (aka SSMS) - the key management tool that ships with Microsoft SQL Server. What I strongly suggest you do first is identify which discipline within Access you find yourself either working with most or enjoy working with first-and-foremost. Then work within SQL Server to replicate that functionality or process to see how it's done in SQL Server. If you enjoy building queries in code then work with the query editor in SSMS to create queries that perform similar functionality in a SQL-equivalent database to one you commonly work with in Access. Before you do any of that tough you need to have a database to work with. Therefore what I am going to cover here is how to take a file you're familiar with in Access and transform it to a comparable SQL Server database for the purpose of learning Microsoft SQL Server. You may expect that what I am about to suggest involves the Upsizing Wizard that ships with Microsoft Access. It does not.
You see, the current Upsizing Wizard - the one that ships as a module within Microsoft Office Access 2007 - does not see the current version or SQL Server (Microsoft SQL Server 2008) as a version of SQL that is compatible with the Upsizing wizard. While the Upsizing Wizard is compatible (on paper) with each version of SQL Server greater than Microsoft SQL Server 6.5, it thinks that MS SQL Server 2008 (which is version 10) is in fact less than SQL Server version 6.5.
Thankfully there is a better option than the Upsizing Wizard. There is the SQL Server Migration Assistant or the SSMA for short. It is available in various flavors from Microsoft at this link. Since we're discussing Microsoft Access we'll be using the SSMA for Access for Microsoft SQL Server 2008 (there is also a 2005 version on the site.)
Download and install the SSMA for SQL Server 2008 for Microsoft Access:
Select Save from the download popup window. Once downloaded, unzipped, and installed you'll want to launch SSMA from the Start Menu. Upon initial launch you will be required to license (free) the SSMA tool.
Click the license registration page hyperlink provided on the License Management form. You'll need to sign into your Windows Live account; once done you'll need to save the license file to the License directory you've browsed to in the dialog box presented above. Click the Refresh License button after doing so and you'll finally be provided with the Migration Wizard within the SSMA:
At this point you've now reached Click Next Land...
You'll first be asked to create a project for the migration. Simply select a name and a file location. The next form you're presented with in the SSMA is simply a File Open dialog box. Select the Access file you wish to migrate into SQL Server and click Next >.
You'll now be presented with a form that is finally a little more detailed than anything you've had to deal with to date in this process:
You'll see that I selected an Access file called DVD. I've also decided to only import the tables into my new database. If you did choose to import queries from the Access file they would import into SQL as a View. We will discuss tables, queries, and views later in this series. Click Next>
At this point you've hit the big time. You're now about to create your first SQL Server database and it only requires a few bits of information! Enter the name of the SQL Server instance you're going to use for your training database, decide on a database name and (so long as you've been granted rights that allow you to create a database you're able to determine your means of security (either Windows Authentication or SQL Server Authentication) and by clicking Next> you'll create your first SQL database.
You will have the option on the subsequent screen to link tables from SQL to Access. You would perform this step if you chose to move your data from Access to SQL, but yet continue to use Access as the "Front End" to the database. Your queries would run against SQL, if you opened your tables in a grid view manner you would see the data from the SQL database. We will not link our tables in this iteration of the SSMA, because we're going to eventually use this as a method to compare and contrast querying from within Access against Access to querying the SQL database from within the SQL Server Management Studio - the management tool that ships with SQL Server. Later in this series we will look at what happens inside the SQL Server database when you do query from within Access against the SQL Server tables and what you can do to mitigate the performance nightmare that ensues. For now just make your form look like the one here and move on... NEXT!
The final screen asks you convert your individual tables from Access to SQL Server. Once done, you're presented with a screen that will look something like this:
You'll note that the Access file objects are listed first, followed by the corresponding objects in the newly-created SQL Server database. Don't worry, the Access file remains unchanged so long as you don't link tables. Using the SSMA is only the first step in your education and familiarization with Microsoft SQL Server. In our next tip in the series I'll show you how to perform similar function in each platform to assist you with learning SQL by using processes you're already accustomed to in Access.
Next Steps
- More tips from the author are available via this link.
- Learn about SQL Server Security (Windows versus SQL Server) at MSSQLTips.com
- Learn more about SQL Server Management Studio at MSSQLTips.com
- Additional introductory tips on SQL Server from MSSQLTips.com are available here
- The author blogs about SQL Server topics at his personal blog: thesqlagentman.com.
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-04-28
