Tag Archives: great sql server hosting

IHostAzure.com :: How To Migrating your Access Queries to SQL Server Transact-SQL

Posted on by

This article is 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.

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.