<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Migrating your Access Queries to SQL Server Transact-SQL

Published on
44,984 Points
23,284 Views
22 Endorsements
Last Modified:
Approved
Jim Horn
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
An overview of migrating your Microsoft Access queries to SQL Server Transact-SQL (T-SQL) query language, and is intended to be a 101-level introduction.  The target audience is Access developers that are familiar with queries, but not very familiar with SQL Server.

In Scope


  • Basic Info about Access and SQL Server
  • Why You Should Migrate queries
  • Converting Access TABLES To SQL Server
  • Converting Access QUERIES To SQL Server
  • Code Cleanup
  • Naming Conventions
  • Data Type Changes
  • Value Delineation
  • Common Function Changes
 

Out of Scope


  • Converting the user interface of an Access application to any application
  • How to take ugly Access queries and make it easy-to-understand SQL Server queries.
  • SQL Server Stored Procedures and Functions
 

Let us begin with the benediction

Everybody repeateth after thee:  

Converting queries from Access to SQL Server DOES NOT by itself result in better code.
I’ve had numerous potential clients that built their own home-grown Access applications, with an undocumented dogpile of queries, and they had the expectation that a couple of my hours to convert to SQL Server would result in beautifully clean manageable code.   Not gonna happen.  Amen.  
 

Basic Info about Access and SQL Server

Access is both a front-end (there is a user interface that can perform actions) and back-end (stores data only) application. SQL Server is ONLY a back-end application.   So you can migrate tables and queries to SQL Server, but Forms, Reports, Macros, Modules have to be converted to a front-end application such as VB.NET, C#, Java, or can remain in Access.

This also means that SQL server cannot include in queries input prompts like [Enter a start date here] or [Enter your customer number here].  Instead of user interface actions in forms, such as [Forms]![frmDataEntry]![CumstomerID], T-SQL accepts parameters in a similar fashion as Access functions and subs.

Access is a file, and can be run either on a pc that has a full (aka full-blown) license of Microsoft Access to allow users to develop / design apps, or runtime license which allows users to run Access apps, but not design them.   SQL Server is a server, which means that it must be installed on a separate box, and then users can either develop in it with a client application called SQL Server Management Studio (SSMS), or just connect to its data by installing the correct drivers and having access.

Access security (insert drum roll-symbol crash if you prefer) is home grown, and maintained by the developer.  And since Access is a file, someone of malicious intent can copy it and walk away.  SQL Server security is either SQL Server Authentication which is create your own user names and passwords, or Windows Authentication which integrates with Active Directory to read a user’s network login.
 

Why You Should Migrate queries from Access to SQL Server

Wouldn't it be good if...

  • You can write code comments in your own Access queries.
  • You can string along multiple queries in one script without having to save them as individual Access queries.
  • The same IntelliSense that helps you code in Access VBA also existed in queries?
  • You can create temporary table(s) that can be re-used multiple times.
  • You can dim / declare your own single-value variables / parameters, and use them throughout multiple queries.
  • You can perform DAO / ADODB-like recordset loops / cursors within a query?
  • You can ‘tune’ your queries for optimum performance, identifying roadblocks?


Converting Access TABLES to SQL Server

Easy

To date there is no Access to SQL wizard other than the SQL Server Import Wizard.
Until then, you’ll have to manually do something that goes like this:
  1. Go into SQL Server, click on the database, then right-click:Tools, Import, and follow the steps to import all tables into your SQL Server database.
  2. Go into Access, Linked Table Manager, and follow the steps to connect to SQL Server and link to all of your tables.  Access will prefix these new tables with dbo_, or whatever the schema is.
  3. Save the Access file to an archive location somewhere.
  4. Delete the local Access table.
  5. Renamed the linked tables by removing the dbo_ prefix so that the names are exactly the same as the old local tables.


Converting Access QUERIES to SQL Server

There is no migration tool, so you have to do it yourself.  Sorry, no big magic button that says ‘Do my work for me’.  At least not one that’s been created yet.  And if it did, it might be created by me, which would result in making my first million.  So until then, read on..

The syntax of a single SQL statement is exactly the same:The parts of a query


Code Cleanup

Access always ‘qualifies’ column names by writing them in SQL as tablename.columnname, which SQL Server only requires columnname, with the exception that if a given columnname is in two or more tables that participate in the query then tablename.columnname must be used.

Access throws in a lot of unnecessary (( parentheses marks ))  and [][] square brackets ]] that can be deleted.

Access uses a semi-colon ; at the end of Queries, which SQL Server does not require.
 

A Simple Example

Give me all of the holidays in Fiscal Year 2013 from the Calendar table from my article on Build your own SQL calendar table to perform complex date expressions

Access Design View
Access Datasheet ViewAccess SQL View
SQL Server, Query Only
SQL Server, View
SQL Server Management Studio, where the view resides

Naming conventions


  • Just like Access, SQL Server is better used by conforming to a standard naming convention.  Styles will differ such as ALL_CAPS, all_smalls, CamelCase, PNOO_PREFIX_NAME_OF_OBJECT.   PK_THIS_IS_MY_PRIMARY_KEY vs. id, etc.  vwViews Are Prefixed vw, etc.  Many developers openly admit that which naming convention is used is a style preference, as long as it is used throughout the database.
  • Square brackets [] ] are still needed for columns that violate standard naming conventions, specifically including spaces or punctuation marks other than underscore, such as [State Code], [First Name].  Generally accepted naming conventions are to not use these characters, thus not requiring square brackets [] ] around the value, as that’s one more thing to potentially forget and throw an error.
  • Very few things scream ‘I’m an amateur code monkey’ like not following a standard naming convention, and if you’re asking yourself right now ‘Is he talking about me?’, the answer is probably yes.
  • If you are going to use aliases, then use an alias that is reflective of the table, such as policies p, customers c, demographics d, current_address ca, PurchaseOrderHistory poh, SalesOrderDetail sod, etc.  The use of continuous letters such as a, b, c, d, etc. adds nothing to the understanding of the query and should be avoided.


Data Type Changes


Not anywhere near an all-inclusive list
 
Link to SQL Server Data Types  
Access AutoNumber is now identity
Date/Time is now either datetime, date, time,
Yes/No is now Bit, with 1 as True instead of the Access -1 as True
Text(255) is now…

  • char(x fixed characters, including any spaces)
  • varchar(Up to x fixed spaces, optimizes storage by not storing any trailing spaces). This is a big improvement over Access, as a Text-255 column always stores 255 characters of data per row, whereas SQL Server optimizes storage such that it stores the value in a significantally less number of bytes in memory then 255.


Value delineation, aka the character you use to surround a value.


Value Delineation Table

String Concatenation

Access uses the ampersand operator ( & ) , SQL Server uses the plus operator. 

string-concatenation.jpgOne catch, NULL values are handled differently. 
  • Access: NULL & "SomeText" = > "SomeText"
  • T-SQL:  NULL + "SomeText" => NULL, as the entire expression evaluates to a NULL with a single NULL is involved. 
So depending on the nullability of the involved columns, just to be safe you may need to use IsNull([ColumnName], '') for each column.   Hat tip:  SQL Server expert ste5an in this question


Common function changes


Decisions
IFF is now CASEIFF is now CASE
For more information on CASE please see my article on SQL Server CASE Solutions.

NULL handling
Nz (value, value if null) is now ISNULL(value, value if null) or COALESCE(value, value if the first value is null, value if the second value is null, etc.)

Data type conversions
CStr, CDate, CLng, etc. is now CAST and CONVERT.  

Parameters
Access can pass values into your query by Input(), SQL Server uses parameters in a similar manner

The asterisk ( * )
DELETE * is now DELETE, without the asterisk.   SELECT is still SELECT *.  
SQL Server has the TRUNCATE TABLE {table name} statement, which is faster than DELETE as it is only minimally logged.  


Beyond this article, wouldn’t it REALLY be good if..


You could schedule the execution of scripts at set times, without requiring an open form with a Timer event, which hogs a lot of processing?

You had help to optimize the processing of your query by being able to look 'under the hood' of how queries execute, in order to better create queries?

You could log the progress of REALLY LONG stored procedures to a log table, so you don’t have to sit and watch a one-hour-long query execute and wonder ‘Is it really doing anything?’
 

Thank you for reading my article, please leave valuable feedback. If you liked this article would like to see more, please click the 'Good Article' button. 

I look forward to hearing from you. -  Jim - ( LinkedIn ) ( Twitter )
22
Comment
Author:Jim Horn
  • 2
  • 2
5 Comments
LVL 61

Expert Comment

by:mbizup
Good article, Jim.

Voted 'yes'.
0

Expert Comment

by:Evelyn Decker
Hi

I am a student and I am working on my finals of building a database in Microsoft SQL Server 2012. I have complete the building of my tables and now I want to create a switchboard in Microsoft Access to navigate around in the tables on the sql server.  Is it possible to use Access as the front-end (switchboard only) and build all my queries and reports on the sql side.  My assignment is to build a database for a small fabric company to allow them to generate invoices for their customers but I am required to build the database using Microsoft SQL server.  My question is can this be done and I only have two more weeks before my assignment is due.

Thanks in Advance
Evelyn
0
LVL 61

Expert Comment

by:mbizup
Evelyn,

Yes, connecting an Access user interface to a SQL server back end is certainly doable.  If you need specific guidance to get you started, you can post questions in the Access and SQL server topic areas.
0
LVL 67

Author Comment

by:Jim Horn
... and you can build queries in SQL, but it sounds like you're ultimately going to need some kind of front-end UI to call and execute those queries.  Go ahead and ask these questions in the Access and SQL zones, but please be upfront that you're doing this for a class, and make sure the questions are designed towards helping you understand the concepts and seeking advise, and not doing your homework for you.  

As you can imagine, we get a lot of lazy (multiple expletives deleted) around here that attempt to have experts flat-out do their homework for them.

Thanks for reading my article.  If it helped you, please hit the 'Yes' button next to 'Was this article helpful' between the end of the article and the beginning of the member comments.
0

Expert Comment

by:Evelyn Decker
Thanks for the information!  I know what you mean about people trying to get other people to do their homework and in my opinion they are doing themselves a dis-service which they will not learn a thing. Also if I have any questions as I go along I will post and I am sure I will have questions.

Thanks Again
Evelyn
0

Featured Post

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Join & Write a Comment

With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month