Rules to Better Access Databases - 20 Rules

Having spaces in table names necessitates the use of square brackets in all your code. e.g. [Order Details].[Order ID] instead of OrderDetail.OrderID. Spaces will also cause problems when you upsize to SQL Server later on... there is just no benefit.

They just add characters. Surely OrderDetails.OrderID is no better then OrderDetail.OrderID.

1. Access2000: Hidden tables are not shown in the list of tables available for upsizing regardless Tools->Options->View
2. All versions of Access: Tables created by ADODB ("CREATE TABLE ...") are not shown at all

If Access linked table does not have an index to uniquely identify rows, the whole table will be read-only

When you upsize a table, the Upsizing Wizard tries to "map" Visual Basic for Applications functions in your DefaultValue and ValidationRule properties to an equivalent TSQL function. If this attempt is not successful, the validation rule or default will be skipped by the Upsizing Wizard. Consider the following:

• If the Upsizing Wizard fails to map a function in a field's ValidationRule property, only the validation rule is skipped, and the rest of the table is upsized.
• If the Upsizing Wizard fails to map a function in a field's DefaultValue property, the entire table is skipped.
• Access 2000: Validation rules are not upsized

Upsizing PRO will check this rule

ValidationText is upsized only if its accompanying ValidationRule is successfully migrated. Avoid the use of apostrophes ( ' ) in your validation text because they will be displayed as quotation marks ( " ) when upsized.

SQL Server and MSDE have no equivalent to the Format or InputMask property in Microsoft Access 2000. As a result, neither property will be upsized when it is encountered by the Upsizing Wizard, nor will any errors be reported in the Upsizing Report. All formatting displayed as a result of using the Format property will be lost when the data is migrated to SQL Server or MSDE.

The Caption property is ignored by the Upsizing Tools. The true column name of a field will always be upsized, regardless of what the caption for that field may read.

• The value that you select for the AllowZeroLength property determines whether zero length strings ("") may be inserted into a field. Currently, the Upsizing Wizard does not create a constraint or trigger against an upsized table to enforce this rule. Instead, you must manually create a Check Constraint on the columns once the upsizing process is complete.
• Still an issue in Access 2000 -2003

The Required property of a field determines whether the field will allow null values after upsizing. If Required is set to Yes in Microsoft Access, the upsized field will not allow null values. If Required is set to No, null values are acceptable.

In SQL Server a field with a unique index cannot contain Null values.

Don't use sys as a prefix for Access tables. Some developers use this for system tables etc. SQL Server uses tables with this prefix and it becomes confusing. We recommend system tables start with zs - E.g. zsUsers

See our Rules to Better SQL Server Databases - Do you add zs prefix to system tables?

We always use two tables for tracking versioning information:

• _zsDataVersion tracks the schema changes, and which update script we are up to. This helps tremendously in determining which version of the scripts are still required between development, test, and production databases.
• _zsVersionLatest tracks which version the front-end client should be. This allows us to give a warning to (or even deny) users who are connecting to the database while not using the right version of the front-end client.

Please see "Is a Back-end structural change going to be a hassle?" on our Rules to Successful Projects.

The Upsizing Tools do not try to upsize every type of Microsoft Access query that you may have in your Access (Jet) database. The following varieties of queries will not upsize:

• Crosstab queries
• Action queries (append, delete, make-table, update) that take parameters
• Action queries that contain nested queries
• SQL pass-through queries
• SQL Data Definition Language (DDL) queries
• Union queries
• Queries that reference values on a form

You must manually re-create queries that the Upsizing Tools do not migrate.

The Upsizing Tools do not try to upsize Microsoft Access query that includes VBA function names that don't have their equivalent Transact-SQL functions. The upsizing result will depend on Microsoft Access version (2000/2002/2003) and SQL Server Version (2000/2005). The following varieties of queries will not upsize:

• Queries referencing value in control, for example Forms![FormName]![ControlName] (Access 2000)
• Select queries that take parameters (Access 2000)
• Select queries where parameter used more than once (All versions of Access)
• Select queries referencing Format function (All versions of Access)

You have to manually edit SQL definition in Microsoft Access (remove or replace keyword) and modify view/stored procedure/function T-SQL in SQL Server after upsizing.

SELECT Orders.OrderID,
    "Order Subtotals".Subtotal,
     FORMAT (ShippedDate,'yyyy') AS Year
FROM Orders
INNER JOIN "Order Subtotals"
    ON (Orders.OrderID="Order Subtotals".OrderID);

SELECT Orders.OrderID,
    "Order Subtotals".Subtotal,
     YEAR (ShippedDate) AS [Year]
FROM Orders
INNER JOIN "Order Subtotals"
    ON (Orders.OrderID="Order Subtotals".OrderID)

The MS Upsizing Wizard cannot upsize Microsoft Access queries containing:

• EXISTS <> FALSE/TRUE or
• EXISTS = FALSE/TRUE

For example, the following query will not be upsized:

PARAMETERS [@Employee Last Name] Text ( 20 );
SELECT Orders.OrderID
, Orders.CustomerID
, Orders.EmployeeID
FROM Orders
WHERE EXISTS (SELECT EmployeeID
 FROM Employees
 WHERE LastName= [@Employee Last Name]
 AND Employees.EmployeeID=Orders.EmployeeID) <> FALSE

PARAMETERS [@Employee Last Name] Text ( 20 );
SELECT Orders.OrderID
, Orders.CustomerID
, Orders.EmployeeID

FROM Orders

WHERE EXISTS (SELECT EmployeeID
 FROM Employees

 WHERE LastName= [@Employee Last Name]
 AND Employees.EmployeeID=Orders.EmployeeID)

In order to get the good example syntax you must switch from Design View window to SQL View in query designer window and save query definition.

One of the more common complaints about Microsoft Access databases is that some screens are slow to respond. This is often misinterpreted as poor performance on the part of the database engine but the real culprit is usually the screen design.

Here are a few techniques that developers can use to improve screen responsiveness.

1) Fetch fewer records

It is far too easy to create a screen that fetches all the records in a table. Such a time consuming exercise is seldom necessary and serves only to make screens appear sluggish. Users are usually interested in a few or perhaps only one specific record. They may simply wish to add a new record. In either case, it is a waste of time to fetch enormous quantities of data. If you are on a local area network, you will not only slow down your own application but probably annoy most of your cohorts by greatly increasing network traffic.

The solution is to open screens, or forms as they are called in Microsoft Access, with queries that contain 'where clauses' that use specific criteria to limit the number of records retrieved.

Tip: Open forms with criteria that return no records at all. Allow the user to enter criteria in the form header. It is quicker to refresh an existing form after the user enters criteria than it is to load a new form while fetching data.

Example:

You have a table named "Client" with 10,000 records. The poor way to retreive records from is:

Form.RecordSource = "SELECT * FROM Client"

Form.RecordSource = "SELECT * FROM Client"
Called by

Docmd.OpenForm "frmClient","ClientID='SSW'"

The problem with this method, is that all records are returned to the client, and then the client filters them to 1. A filter does not stop the records from returning. Fetching 10,000 records to view 1 is a large waste of resources, and leads to performance problems.

A better way to New Scenario he wants Big Forms to change to:

Form.RecordSource = "SELECT * FROM Client WHERE 1<>1"

The solution Called via a wrapper function for OpenForm:

Function aOpenForm("frmClient","ClientID='SSW')

...So they open instantly with no records - Need a function to parse the where clause and then replace with passed in where clause - reruns query to get 1 record - much faster Note Some little form you don't want this behaviour So keep a table 'zsOneRecordForm' with the forms you want as a one record form So when you call aOpenForm it can look up this table 'zsOneRecordForm' and determine if it should change the RecordSource OR apply a filter.

2) Fill those drop-down lists when you need them

Entering criteria almost always requires drop-down lists to avoid mistakes and tedious guessing. Unfortunately, drop-down lists are notoriously slow. A few drop-down lists can cause the opening of a form to become intolerably glacial. The reason is that drop-down lists always fetch as much data as they possibly can even if you limit the number of records they display.

Every drop-down list takes its time to fetch data and delays the loading of the form even if the user has no need to use the list. The obvious solution is to populate the drop-down lists when the user activates them.

Tip: Use an event procedure or a button to set the row source for the drop-down list. For example:

Me!myDrop List.Row Source = Q

...where Q is, once again, either the name of a query or an SQL string.

Tip: Drop-down lists themselves will be more responsive if they return fewer records. Try cascading criteria so that successive lists are limited by the selection in a previous list. The row source query for a list could depend on the item selected in a previous list as in this example:

Q = " SELECT Field1, Field2 FROM Table1 WHERE Field3 = " & Me!DropList1

When adding a new record should we use ADO rst.AddNew or a SQL INSERT statement?

The INSERT statement will give you better performance because you don't have to create and pass around a recordset object. However the Recordset object gives you protection from data conflicts by raising an error if the user tries to update a record that has been modified by another user. Also on an error the Recordset object will crash out on the offending line, so you can find the problem easier. And when dealing with a memo field, which may contain single quotes or double quotes the AddNew method is easier to construct. But in general if you can do without the data conflict protection and want the performance then use the ADO connection object to execute a SQL statement.