Working with Stored Procedures - MS Access

At the beginning of the last section, we introduced Views and described how a View can return data like a SELECT query, and we described how the dataset returned may be updateable. The equivalent to an INSERT, UPDATE or DELETE query in SQL Server is a stored procedure. Stored procedures are in fact more flexible than a simple query that performs one step; for example, updating data in a single table. They are mini programs that can range from a few lines to several pages of code.

In Access, when you need to write a program, you use VBA, in SQL Server, you use T-SQL, which allows for program logic and variables. T-SQL is a specific language for SQL Server; other popular databases have their own variations on SQL to assist with programming; for example, Oracle uses PSQL. Programing in T-SQL is different to writing VBA. The first difference you will spot is that even though the language has a number of features, it is not as rich and varied as VBA. On the plus side, this means that the language features can be learned quickly. Another big difference is that T-SQL mixes program logic with SQL in a very elegant manner; effectively, the SQL becomes embedded in the code, which means that when you need to mix a variable into a piece of SQL, rather than building up a string to be executed, you can often directly mix the reference to the variable into the flow of the SQL. This is a more elegant code structure.

Before you take a look in depth at the T-SQL language features, you will look at how to create some simple stored procedures. Unlike Views, which can be edited in a GUI in addition to the Query window, stored procedures do not have a graphical editor. The stored procedures are saved in the Programmability | Stored Procedures folder, as shown below.

The Stored Procedure folder in Object Explorer.

The Stored Procedure folder in Object Explorer.

The following example (StoredProcedure.sql) illustrates how to create, execute, change, and remove a stored procedure:

One area of possible confusion is in thinking that the preceding example is like a VBA module—it is not. When you execute the T-SQL command to create the stored procedure, the actual code is saved inside the database as a stored procedure object. After executing the lines of code to create the stored procedure and refreshing the folder containing the stored procedures, you will see that the stored procedure has been saved inside the database. To see and modify the stored procedure, you can use the shortcut menu, as shown below.

After the code has been executed and the stored procedures folder has been refreshed, you can get back to the code by right-clicking the stored procedures and either selecting Modify or Script Stored Procedure from the shortcut menu.

After the code has been executed and the stored procedures folder has been refreshed,

You will find that the syntax for writing queries to INSERT, UPDATE, and DELETE data in SQL Server is similar to that used in Access, but it is not exactly the same.

The DELETE Query
In Access, an example of a DELETE query would have the following SQL (notice that Access often adds additional brackets in a WHERE clause):

In SQL Server, you don’t have the list of fields following the keyword DELETE. The SQL Server syntax shown in the following code would also work in Access. In this situation the additional information for specifying the fields was added by the Access Query designer:

INSIDE OUT Truncating tables

When you perform any operation to change data in SQL Server, the changes to the data are normally recorded in the LOG file (there are some exceptions to this with respect to Binary and some other data type columns, which due to their size are not recorded in the LOG file). This means that if you try to delete a large amount of data, there will be a lot of data written to the LOG file to record the change, which will take time to execute.

If for example you have a table containing data imports, you might not be interested in having the operation of emptying the table recorded in the LOG file. As long as this import table does not have any relationships, you can use a special TRUNCATE TABLE <tablename> syntax to provide a very significant boost to performance. This command is not recorded in the LOG file, which means that it cannot be undone inside a transaction, and if a backup using LOG files were restored, or you are using log shipping or a mirror database, then the change would not be reproduced. But in this situation, you probably don’t care about this minor effect.

The UPDATE Query

It is only when you come to design an UPDATE query that depends on more than one table that you will find the syntax for this command changes. In the following Access SQL, you are updating only customers who have an order in the system:

This SQL needs to be rewritten as follows:

After the keyword UPDATE, you need to state only the name of the table that will be updated, and then you need an additional FROM clause that restates the main table name and expresses any joins. This involves a cut and paste operation on the first few lines of the SQL.

The INSERT and INSERT INTO Queries

There are four different forms for INSERT operations. In you want to insert one or more values of data into a table, in Access, you can use the following syntax:

Change the double quotes to single quotes, and the same code will run in SQL Server, as shown here:

But you would not normally write this expression in SQL Server, because SQL Server supports a VALUES syntax, which has the added benefit of being able to insert multiple rows with a single statement, as shown in the following:

This can then be extended for up to 1000 rows:

The second form of an insert statement is equivalent to an Access Make Table query; in Access you have the following:

Apart from changing the double quotes to single quotes, the same SQL executes in SQL Server:

The third form of an INSERT query is where you want to add values to an existing table. In Access, you create the following Append query:

INSERT INTO Customers2 SELECT Customers.* FROM Customers WHERE (((Customers.Country) Like "*USA*"));

In SQL Server, apart from changing the double quotes to single quotes and the wildcard matching character * to a %, the SQL is unchanged:

It is interesting to note that the wildcard syntax in SQL Server is also supported in Access, which you will see if you paste the code in the previous example back into Access.

INSIDE OUT Temporary tables

In Access, you can’t easily create a temporary user-specific table, and yet in solving many complex problems, it is essential to stage data into a holding table for further processing. SQL Server allows user-specific temporary tables to be created; this is an extremely powerful feature, and it can be incorporated into your stored procedures. But if you create temporary tables, you need to ensure that your code allows for the fact that the temporary results table might already exist, as illustrated in the following:

There are two key points to observe when doing this: first, temporary tables are created inside the Temporary Tables folder in tempdb; this means that you can go into this system database and look at the temporary tables. (If you close the Query window that was used to create the temporary table, the temporary table will be removed from tempdb. If you try this, remember to use the shortcut menu and select Refresh to see the folder updated after you have closed the Query window.) Second, these temporary tables exist for as long as the connection exists, which means in Access you could execute a call that creates the temporary table, and then subsequently refer back to the temporary table, because Access will hold the connection open for you.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics