When you have a sequence of operations, that needs to produce different result sets for different users, but is too complex to complete in a single or set of layered queries, you need to create a physical table of results.

This is very difficult in Access without giving each user a copy of their own font-end application, or naming the result tables to reflect a users name (and suffering from bloating or fragmentation).

In SQL Server this is very easy, and it is also very easy to link Office applications like Excel or Access to these result sets.

What you need to know about SQL Server for this article

• You need to know that in SQL Server, temporary tables can be created, by creating a table with a # as the first character in the name. But when executed in a stored procedure the temporary table will be removed after the procedure has completed and returned the results.
• You need to know that the suser_name() function returns the user name for a user when using windows authentication with each user having a separate login to the SQL Server.
• You need to know that a stored procedure is a mini-program, which can also return data, so that it can be used in a pass-through query from Access.

Top of Page

Stored procedures and #temp tables

The following example shows how to create a stored procedure in Northwind, which returns a temporary result set. This can be used by any number of users at the same time.

CREATE PROC usp_ReturnsTemporayData
AS
BEGIN
-- consider a complex sequence of operations
-- ending with temporary data in #ReturnsTemporaryData
SELECT * INTO #ReturnsTemporaryData
FROM Customers
-- return the data
SELECT * FROM #ReturnsTemporaryData
END

To link to this from Access we create a pass-through query.

This is an excellent solution, as long as you do not need to get at the results using other Access queries. For example trying the following after running the stored procedure will not work.

Top of Page

User specific views

As an alternative to the above, where you need to persist results over a period of time, possibly whilst a series of stored procedures are executed, and other activities undertaken, you can consider creating a physical table, and adding a UserName field to the table.

CREATE TABLE [dbo].[user_TemporaryData](
[UniqueId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[CustomerID] [varchar](5) NOT NULL,
[CompanyName] [varchar](40) NOT NULL,
[UserName] [varchar](100) NULL
CONSTRAINT [DF_user_TemporaryData_UserName] DEFAULT (suser_name()), [TSCustomers] [timestamp] NULL
)

Then you will also need a view which filters the data for the specific user.

CREATE VIEW vw_TemporaryData
AS
SELECT * FROM user_TemporaryData
WHERE UserName = suser_name()

Our stored procedure must now empty the data for the user and re-populate the data.

CREATE PROC usp_ReturnsTemporayData_UsingAView
AS
BEGIN
-- consider a complex sequence of operations
-- ending with temporary data placed in the user specific table
DELETE FROM [user_TemporaryData] WHERE Username = suser_name()
-- or better to use the following
DELETE FROM vw_TemporaryData

INSERT INTO [user_TemporaryData] (CustomerId,CompanyName)
SELECT CustomerId, CompanyName
FROM Customers

-- return the data
SELECT * FROM vw_TemporaryData
END

The data set now includes the user’s name.

This method offers a great deal of flexibility, as we can now link to the view if required and update data in the view; maybe the temporary data was a result of a user selecting records for further processing.

Top of Page

Pumping temporary data into SQL Server

You can also use this technique when you have legacy or other processes which you need to keep in Access (because of complexity to re-write), but require temporary data. In this case we can link Access to the view, and use Access queries to pump data into the view.

Or you may have a complex sequence of operations which finishes by opening an Excel spreadsheet linked to the view.

The use of the above methods means that may activities which are very difficult to create in Access become far simpler when we have the data in SQL Server.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz