Migrating Queries from Microsoft Access to an Access Project

By Shannon Eric Peevey, UNT Central Web Support

This month, I want to talk about an important part of the migration from Microsoft Access to an Access Project. An Access Project is an upgraded Access database, that allows the end-user to use Access as a front-end for what is essentially a SQL Server database. This is nice for end-users, because they do not have to learn a new interface and can use the same tools that they are accustomed to using with Access databases. After testing migration from Access to an Access Project, I have found that the migration is relatively painless, except the fact that the upsizing tool does not know how to deal with queries very well. This can make the relatively "painless" migration into a "painful" migration in a hurry. (If you don't believe me, take a look at the SQL statements used in your Access databases? Scary, huh ?!) The reason that the upsizing tool doesn't know how to deal with a majority of the queries, is because SQL Server has two distinct families of queries, as opposed to the all encompassing Query in Access. These two families are Views and Stored Procedures. The differences between these two types of queries, are that Views are simple queries that take no variable, and which are not executed by any other query, and Stored Procedures are queries that can be executed, take user input and place that input into a variable. A Stored Procedure can also run tests on a query, as well as, use control structures. In other words, Stored Procedures are very powerful, and can be used with much of the power of a programming language. On the negative side, the nature of the power of the stored procedure also means that they are a little more difficult to write, and in the migration process, a little more work to modify.

This article is an attempt to get you familiar with the process of modifying queries, and will help to allay some of the fears that you might have about taking a new step in your database creation and management skills. And even though this process is not completely painless, I believe, after this article, that you will feel a little more easy about the process in general. To do this, we are going to, first, set up our work environment to best suit the migration process, second, discuss the movement of view-type queries to your new Access Project, third, discuss the make-up of a stored procedure, and then, finally, look at an example of a migration of an Access query to a SQL Server stored procedure.

Setting up our environment

The first thing that I would like to do in this article, is set up our environment so that we can make the modifications with the least amount of work and confusion. (This is assuming that you have already migrated the Access database to an Access Project.) This is done by:

1. Opening the original version of the Access database. (This will be the database with the .mdb extension.)

2. Opening another instance of Access, and then opening the new, or migrated, version of the Access database. (This version of the database will have the .adp file extension.)

While you have the Access Project at the top of the desktop, take a look at the Views and Stored Procedures, which can be viewed by clicking on the appropriate buttons on the left-hand side of the navigation dialog box. You will notice that some of the queries were migrated in the migration, but that you have a lot of junk Views and/or Stored Procedures. You can delete them. Also, be aware that some queries might actually be in the wrong place, (Views in the Stored Procedure area, and/or vice versa). You can easily change this by opening the queries in the original Access database and checking the SQL statements to see what type of queries they are. These queries will fall in one of these two families:

1. All queries that do not take variables and are not executed by another query are Views.

2. All queries that are executed by another query, accept variables or manipulate results are Stored Procedures.

Creating New Views

After you have discovered which queries fall under the categories of Views and Stored Procedures, I would advise you to recreate the Views first. They are the easiest to deal with, and they will help you to deal with some of the initial fear that you will be feeling with this migration, and in a very real sense, this new environment. To move the queries from Access to Views in the Access Project, you must:

1. Create a new view in the Project by clicking on the View button on the right-hand of the navigation dialog box, and then clicking on New, which is found on the top bar of the navigation dialog box. You will now be in the View creation environment, and for the purpose of migrating your queries, you will need to see the SQL view, which can be activated using the SQL button found on the top control panel of your access project. (It is located next to the SQL-checkmark button). If you would prefer a larger work environment, you can click on the two buttons directly on the left of the SQL button, and this will close the other two query creation tools, and leave you with a full screen SQL creation tool.

2. Next, you will open the original query (from the original Access database) in design view . When you are in the query design view, you will want to see the queries SQL statement, which is accomplished by clicking on the design view button located directly underneath the file menu button. (This will bring up a short menu of three items, from which you will choose the SQL view).

3. Highlight the query with your mouse, and copy and paste the query over the generic code in the Access Project.

4. Save the new view as the same name of the query in the original database. (Do not be alarmed if there are any problems with the syntax, the program will prompt you if there are.) If an error does occur, check to see if the query does indeed need variable declaration, etc. If it does, you will have to create the query as a Stored Procedure. If it does not, check for syntax errors, make the changes necessary, and try saving again.

I wouldn't be too worried. I have moved quite a few queries to Views, and have had few problems. Most of the time, the problems occur when you are trying to migrate a query to a View, when it should be migrated as a Stored Procedure instead.

Speaking of Stored Procedures

If you find that the query that you want to migrate is most appropriately migrated to a Stored Procedure, you will need to modify it to recreate the query to acquire the same results as the original. Writing stored procedures for Microsoft SQL Server is a task that sounds more difficult than it actually is, and is made even easier by the fact that you already have the SQL query in place in the original database. To get started:

1. Open both of the databases, as written in steps 1 and 2 in the section "Setting up our environment".

2. Open a new Stored Procedure by clicking on the Stored Procedure button found on the left side of the navigation dialog box and then choosing the New button found on the top bar of the navigation dialog box.

In this new screen, you will something like this:

Create Procedure "StoredProcedure1"

/*

(

@parameter1 datatype = default value,

@parameter2 datatype OUTPUT

)

*/

As

/* set nocount on */

return

This is an outline of a generic Stored Procedure, and is a good place for us to start understanding the syntax of the Stored Procedure. We will look at this line-by-line, so that you will see how this generic recipe can be integrated into your query, and help you to migrate these queries with ease.

The first line of the Stored Procedure, states, "Create Procedure "StoredProcedure1"" This line creates a Stored Procedure with the name "StoredProcedure1". When we are creating the new Stored Procedure, we will want to change this to read, "Alter Procedure "StoredProcedure_Name_that_you_would_like". By changing this line from Create Procedure to Alter Procedure, we are making it easier for us to make future changes to the Stored Procedure. If we left it as Create Procedure and we tried to execute the Stored Procedure a second time, we would get an error stating that the Stored Procedure already exists, and that SQL Server cannot execute the command. Using Alter Procedure allows us to execute the Stored Procedure as many times as necessary, and it simply modifies the existing Stored Procedure.

The second line contains the comment character /* . This character will treat the text/code that follows as a comment, and will not execute the code. It needs to have a */ at the end of the block that you would like to comment, to end the comment. (For example:

This is not a comment

/*This is a comment

and this is too, but */ This is not.

Therefore the database engine sees this "quasi-code" as:

This is not a comment

This is not.

and completely ignores the section of text bracketed by the symbols /* and */ .

The next section of code is:

(

@parameter1 datatype = default value,

@parameter2 datatype OUTPUT

)

This block, when uncommented, (by removing the /* and */ ) , represents the declaration of variables to be used in the Stored Procedure. If you have ever coded in a programming language, you will notice the similarities between the declaration of variables in a Stored Procedure, and the declaration of variables in a programming language. You declare a variable by first:

1. Giving the variable a name that is significant in the context of your stored procedure, such as, fileName, to signify a variable that will receive the name of a file as input from the user. The second important part of the variable name, is the appearance of the "@", preceding the variable name. In SQL Server, this "@" character represents a local variable. This means that the variable name must have an "@" preceding the variable name for the variable to be recognized as a variable in the Stored Procedure. (Global variables are available in SQL Server, but are beyond the scope of this article.)

2. Give the @variableName a datatype. This is the type of information that the variable will hold. Below is a table with all of the data types that are available in a SQL Server database.

The exciting part about using SQL Server is your ability to create data types that fit your needs better than the data types that are listed above. (I will cover that in a future article.)

1. You may want to initialize, or set the @variableName, to a value. This can be a number in the case of an integer data type, or Int. (ie. @variableName Int = 0 ) Or, in the case of varchar:

@variableName varchar(255) = 'inactive'

Notice that we placed a number after varchar. This number, enclosed by parenthesis, is the length of the text that you will allow to be held in that variable. In this case, the text can be 255 characters in length, (hence, varchar(255)). I would also like you to notice that the variable is initialized by placing a string of text characters inside a pair of single quotes. You need these single quotes, otherwise, it will not know how to deal with strings.

The final block of code looks like this:

As

/* set nocount on */

return

This block of code is where you place the SQL statements. The "As" and the "return", act as brackets, or the beginning and ending, for the code that you place in this block. (As you see, there is a commented section that says "/* set nocount on */". That is rubbish that can be removed and replaced with a SQL query, such as, "SELECT * FROM table_name".)

Here's an Example

I always feel that it is easier to understand a new topic by looking at an example. This example will take a query that I have migrated, and show you the thought process that I followed to get you from Ex. 1, which is a query that was created in Access:

EX. 1

SELECT

table1.Coordinator, table1.Status, table1.[SSN],

table1.FirstName, table1.LastName, table1.Initial, table1.Address,

table1.City, table1.State, table1.PostalCode, table1.PhoneNumber,

table1.Class, table1.[Major Code], table1.[Graduation Date],

table1.[Citizen], table1.[Hours], table1.[GPA],

table1.[Hours in Major], table1.[GPA Major], table1.[Interview Date],

table1.Comments, table1.Employer, table1.[Start Date],

table1.[JobNumber] AS Expr1, table1.[StudentNumber]

FROM Students

WHERE (((table1.Coordinator)=[Type in your full name]) AND ((table1.Status)<>"inactive"))

ORDER BY table1.Status DESC;

to Ex. 2, which is the query modified to work as a Stored Procedure:

EX. 2

Alter Procedure [sp_students] @Type_in_your_full_name varchar (255),

@students_job_# int

As

DECLARE @status varchar(10)

SELECT @status = 'inactive'

SELECT table1.Coordinator, table1.Status,

table1.[SSN], table1.FirstName,

table1.LastName, table1.Initial, table1.Address,

table1.City, table1.State, table1.PostalCode,

table1.PhoneNumber, table1.Class, table1.[Major Code],

table1.[Graduation Date], table1.[Citizen],

table1.[Hours], table1.[GPA], table1.[Hours in Major],

table1.[GPA Major], table1.[Interview Date],

table1.Comments, table1.Employer, table1.[Start Date],

table1.[JobNumber] AS Expr1, table1.[StudentNumber]

FROM Students

WHERE (((table1.Coordinator)= @Type_in_your_full_name)

AND ((table1.Status)<> @status ))

ORDER BY table1.Status DESC;

Return

As you can tell, there was not much to change from the original query. If you look at them side by side, you will see that the query is virtually the same.

The only differences, are found in the highlighted text. This highlighted text represents the input data, "Type in your full name", and the condition, "inactive". The input data takes standard input from the user, and applies it to a variable, which we have given the name, @Type_in_your_full_name, in the transformed query. Access, because of it's desire to make the job of database creation easier for the end-user, allows you to set this up with relative ease, through the gui SQL interface that comes with the program. It is intelligent enough to realize that the user wanted to return the information from the table based on the full name of the coordinator. This works well in the Access world, but it lacks the power of a real enterprise-size database server, and must be modified to react with the precision needed for the SQL Server. We modify this code, by first declaring a variable after our procedure name. Which looks like this:

Alter Procedure [sp_students] @Type_in_your_full_name varchar (255)

You will find that any variable that needs input from the end-user, must be declared here at the heading. (By needing input from the end-user, I mean that we cause a dialog box to open when the stored procedure is called, and ask the user for input, applying that input to a variable.) (One note, the text on the dialog box prompt will be the text that is your variable name, For example, "Type_in_your_full_name". If the aesthetic of the program is important to you, you should think of another prompting scheme than the one that I use in this example.)

When I have declared the variable at the start of the stored procedure, then I will have to change the Access created variable name, with the variable name that you just declared:

WHERE (((table1.Coordinator)=[Type in your full name])

changes to:

WHERE (((table1.Coordinator)= @Type_in_your_full_name)

That is all there is to it.

Next, we need to look at the second part of the criteria that we are using in our WHERE statement.

WHERE (((table1.Coordinator)=[Type in your full name])

AND ((table1.Status)<>"inactive"))

We want to show the results of our SQL query without the entries that are set to "status = inactive". In Access, the Jet Database engine knows that this string (inactive), enclosed with quotes, is setting a criteria with which to run it's query, but SQL Server does not. It thinks that we are asking for a column named ("inactive"). We will obviously have to make an adjustment to the SQL statement before this query will work as a stored procedure. To do this:

1. We must declare a variable? but not at the top of the stored procedure, as we do not need user input for this variable. We will therefore declare this variable in the body of the AS/RETURN block. We do it like this:

AS

DECLARE @status varchar(10)

As you see, it is exactly like the declaration of @Type_in_your_full_name except, we preface the declaration with the word DECLARE.

2. After we have declared our variable, we must initialize it, or give it the value that we need to fulfill the criteria that we have set for the original SQL statement. We do it like this:

SELECT @status = 'inactive'

The declaration statement is just a SELECT statement without the FROM. The exciting part about the syntax for this statement is that you can simply expand on the form to initialize the variable to include information from another query, such as:

SELECT @status = status FROM table1 WHERE name = 'John Doe'

Or just initialize it as we did in the first declaration example:

SELECT @status = 'inactive'

3. After we have initialized the value of the variable @status, we need to set it into the WHERE portion of the query, like this:

table1.Status)<> @status

We have now successfully modified the original query for use as a stored procedure, and will be able to use this new form in the Access Project that we have migrated our database to. Here is the final example:

Alter Procedure [sp_students] @Type_in_your_full_name varchar (255),

@students_job_# int

As

DECLARE @status varchar(10)

SELECT @status = 'inactive'

SELECT table1.Coordinator, table1.Status,

table1.[SSN], table1.FirstName,

table1.LastName, table1.Initial, table1.Address,

table1.City, table1.State, table1.PostalCode,

table1.PhoneNumber, table1.Class, table1.[Major Code],

table1.[Graduation Date], table1.[Citizen],

table1.[Hours], table1.[GPA], table1.[Hours in Major],

table1.[GPA Major], table1.[Interview Date],

table1.Comments, table1.Employer, table1.[Start Date],

table1.[JobNumber] AS Expr1, table1.[StudentNumber]

FROM Students

WHERE (((table1.Coordinator)= @Type_in_your_full_name)

AND ((table1.Status)<> @status ))

ORDER BY table1.Status DESC;

Return

In Conclusion?

In this article, we have taken a look at the migration of queries from an Access database to both Views and Stored Procedures in an Access Project. As we have seen, this can be somewhat complicated, but I think that this article will get you a little more acquainted with the process, and give you a point from which you can leap from and try to come up with your own solutions to the complicated queries that you have written in Access. I have noticed that the most important task of migrating queries is that I completely understand what the query is doing. If I don't, I will sit and spin my wheels for a long time just throwing steps into a Stored Procedure. If I have taken the time to understand what the query is doing, then I only need to find the simple modifications that are necessary to make the query work as a Stored Procedure. Next, I like to set up my environment in a way that allows me to work the most efficiently, and with the least chance for human error. With my environment set up to my liking, I begin to work with the simplest forms of queries, Views, which in most cases, consist of simply copying the SQL statements from the original database and pasting them into our new View in the Access Project, naming the View with the name of the original query. Finally, I tackle the most difficult queries, which involve variables, execution, or result manipulation, and modify the query according to the rules of Stored Procedure syntax. When I follow these steps, I find that the migration process for moving Access databases to Access Projects, is fairly easy. This shows that once again, Microsoft has allowed us to take the next step beyond their Access database, and give our ColdFusion applications the room to grow that we need.

We'll see you on the other side!