Converting queries to views, general points to consider

support@ascassociates.biz , Sales Enquiries

Converting queries to views, general points to consider

Access has the ability to create Queries, the nearest equivalent in SQL Server is a View. Access Queries are simpler to use than Views, although a lot of progress has been made in extending the power of Views and making them easier to use and SQL Server has a graphical interface in some ways more powerful than Access for designing Views.

One of the main uses for Views is in giving users restricted access to data, it would be possible to only allow a user to have access to a set of views and not the underlying tables; restricting both the rows and columns that a user can see.

Before you dive into converting all your Access queries into Views you should fully understand the implications of this, even when using .NET you may still wish to consider other options.

Points To Consider

Here are a list of things you should consider when deciding to convert a Query to a View.

The bulk of your SQL will not have performance issues, and converting to views means that you lose the power of Access Queries.
Often what easily converts does often not need converting (from a performance viewpoint) and what does not easily convert does. Conversion needs to take account of differences in what is allowed in SQL and involves various syntactical changes to be made.
Some queries like a DELETE * are better replaced with TRUNCATE TABLE (if available) using a Pass-Through Query
Views have some restrictions on updateability, although it is possible to work around this.
The use of some SQL Statements such as ORDER BY are not allowed in Views.
Views reflect the design of the application and not the design of the database (in most cases excepting for reasons of security), so changes to the application such as a re-build in .NET mean that an existing straight conversion will not necessarily be of benefit.
Sometimes pass-through Queries or embedding SQL in code is a better option.
Straight view conversion misses the opportunity to exploit schemas in partitioning a design into more manageable chunks.
A Minor point but re-linking views is more difficult (see Relinking Tables And Views).
When a tables structure is changed, a View which use SELECT * (all fields) will not dynamically pick up the new field (for reasons of security) and needs to be re-built (unlike stored procedures which dynamically reflect structural changes).

On the plus side, views offer good performance and are excellent for giving users an updateable data set which reflects security constraints.

Are Views The Only Answer ?

If you need to re-write a query then you could if using Access consider changing the query type to a Saved Pass-Through Query. Pass-Through Queries return Read-Only sets of data and the SQL must be converted from Access-SQL to SQL Server-SQL (this also applies to Views, Stored Procedures and Functions). You can also construct an Action Query (Insert,Update,Delete) using Pass-Though, these queries are distinguished by the fact that they do NOT return a recordset (queries have a property that gets set to reflect this). Pass-Through Queries can also be used in VBA program code with DAO.

Changing The SQL In A Delete Query

The Access SQL for an unrestricted (no where clause) delete query “DELETE * FROM tablename”, needs converting to “DELETE FROM tablename”; dropping the “*”. A better choice is “TRUNCATE tablename”, this is much faster because the before image of the data does not get written to the SQL Server Transaction Log File. You can only use this command on a table which is NOT participating in any Relationships.

Access SQL

DELETE * FROM Customers

SQL Server SQL

DELETE FROM Customers

Or

TRUNCATE TABLE Customers

Changing The SQL In An Update Query

The general syntax of the query needs changing here, but more importantly there also needs to be a slight change to the structure of the query, when as in this case the update is based upon data in another table.

Access SQL

UPDATE Customers
INNER JOIN CustomersToUpdate
ON Customers.CustomerID = CustomersToUpdate.CustomerID
SET Customers.ContactName = [CustomersToUpdate].[ContactName];

SQL Server SQL

UPDATE Customers
SET Customers.ContactName = [CustomersToUpdate].[ContactName]
FROM Customers
INNER JOIN CustomersToUpdate
ON Customers.CustomerID = CustomersToUpdate.CustomerID

If your Access query had used [CustomersToUpdate]![ContactName], which is quite common, then the ! needs to be changed to a “.”

Changing The SQL In An Insert Query

This example shows a simple example of where the syntax in Access differs from SQL Server. The important point is the need to change the double-quotes around the string to single-quotes. The ; at the end can optionaly be dropped.

Access SQL

INSERT INTO dbo_Customers ( CustomerID, CompanyName )
SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
WHERE (((Customers.CustomerID)="ZZZZZ"));

SQL Server SQL

INSERT INTO Customers ( CustomerID, CompanyName )
SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers
WHERE (((Customers.CustomerID)='ZZZZZ'))

Where Clauses And Yes/No Fields

Access uses -1 and 0 for True/False or Yes/No, where as SQL Server uses 1 and 0, and does not understand True/False or Yes/No. This can be quite confusing, because when you link to a SQL Server table you will see Yes shown as -1, but if you look at the same data on SQL Server it will be 1. So what is happening is that Access performs a clever translation back and forth with SQL Server.

If you change the SQL from Access to SQL Server, you need to change the syntax.

Access SQL

SELECT AccountStatus.CustomerId, AccountStatus.AccountOnHold
FROM AccountStatus
WHERE (((AccountStatus.AccountOnHold)=Yes));

SQL Server SQL

SELECT AccountStatus.CustomerId, AccountStatus.AccountOnHold
FROM AccountStatus
WHERE (((AccountStatus.AccountOnHold)=1))

Now just watch because in a pass through query when displaying a result set if you select the Yes/No field from SQL Server, it will show the values -1, because Pass-Through queries do a quick conversion to the world of Access before showing the results; this is desireable although it can be confusing.

One final word, in Access Yes/No fields can have two values -1 and 0, but in SQL Server the equivalent BIT field can have three values 1, 0 and NULL. This is why when upsizing tables we must add defaults to any Yes/No fields before creating the tables on SQL Server, as compared to Access which automatically defaults Yes/No to False SQL Server defaults to null if no explicit default is specified.

Download Code

The sample code can be downloaded from here Access 2000 Database.

Take the strain out of converting SQL with MUST+SQL. Click below to improve your development productivity.

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

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz