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:

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.

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.

Save the Access file to an archive location somewhere.

Delete the local Access table.

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:

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

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.

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.

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 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.

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.

... 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.

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.

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…