Home > SSW Standards > Rules > SSW Rules to Better Access Databases
I've been putting together Development Guidelines for my employer and in the process have reviewed many published standards (in the .Net arena) from around the world. In each category, the suggestions at SSW are always among the best.
Leon Bambrick -
What is it that makes a good software development consultancy? What sets one company
completely above another? What makes a project completely successful?
Do you agree with them all? Are we missing some? Email us your tips, thoughts or
arguments.
Let us know what you think.
|
*** Update *** Instead of manually implementing these rules, use
SSW Upsizing PRO to automatically implement many of them for you.
Download it and give it a go.
|
Backend General Rules
-
Do use spaces in table names?
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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/UseSpacesInTableNames.aspx
-
Do you use underscores (Preference only)?
They just add characters. Surely Order_Details.Order_ID is no better then OrderDetail.OrderID
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/Underscores.aspx
-
Do you have hidden tables or queries (Upsizing problem)?
- Access2000: Hidden tables are not shown in the list of tables available for upsizing
regardless Tools->Options->View
- All versions of Access: Tables created by ADODB ("CREATE TABLE ...") are not shown
at all
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/HiddenTables.aspx
-
Do you always have a unique index on a table?
If Access linked table does not have an index to uniquely identify rows, the whole
table will be read-only
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/UniqueIndex.aspx
Backend Data Rules
-
Do you have invalid DefaultValue and ValidationRule properties
(Upsizing problem)?
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
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/DefaultValue.aspx
-
Do you have valid ValidationText property(Upsizing
problem)?
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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/ValidationText.aspx
-
Do you use the Format and InputMask properties on
a field?
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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/InputMask.aspx
-
Do you use the Caption property on a field (Upsizing
Problem)?
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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/CaptionProperty.aspx
-
Do you use the AllowZeroLength property on
a field (Upsizing Problem)?
- 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
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/AllowZeroLength.aspx
-
Do you use the Required property on a field?
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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/RequiredProperty.aspx
-
Do you use an unique index and the Required
property on a field?
In SQL Server a field with a unique index cannot contain Null values.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/UniqueIndexAndRequiredProperty.aspx
Backend Table and Column Rules
-
Do you use prefix sys in table name (Best Practice)?
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 eg. zsUsers
See our Rules to Better SQL Server Databases - Do you add zs prefix to table name?
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/PrefixSys.aspx
-
Do you always have version tracking 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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/VersionTrackingTables.aspx
Backend Relationship Rules
-
Do you have multiple
key foreign key relationships on tables that map to fields of a different name (Upsizing
Problem)*?
|
|
Figure: Multiple Foreign Key Relationships Different Names
|
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/MultipleKeyForeignKeyRelationships.aspx
-
Do you have fields with multiple key indexes
(Upsizing Problem)?
|
|
Figure: A table with fields with multiple key indexes where the total size of the
fields adds to more than 450 characters
|
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/FieldsWithMultipleKeyIndexes.aspx
Frontend Rules
-
Do you have complex queries (Upsizing Problem)?
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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/ComplexQueries.aspx
-
Do you avoid parameter queries with EXISTS keyword
and comparison operators (<> or =)(Upsizing Problem)?
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
Figure: Bad example of Access query with EXISTS keyword and comparison operator
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)
Figure: Good example of Access query with EXISTS keyword and without comparison operator
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.
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/AvoidParameterQueries.aspx
-
Do you remove VBA function names in queries
before upsizing queries (Upsizing problem)?
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);
Figure: Bad example of Access query with FORMAT keyword
SELECT Orders.OrderID,
"Order Subtotals".Subtotal,
YEAR(ShippedDate) AS [Year]
FROM Orders
INNER JOIN "Order Subtotals"
ON (Orders.OrderID="Order Subtotals".OrderID)
Figure: Good example of SQL Server view with YEAR keyword
*Note: Moved to http://sharepoint.ssw.com.au/Standards/ReportingSolutions/RulesToBetterAccessDatabases/Pages/VBAFunctionNamesInQueries.aspx
Acknowledgements
Adam Cogan
Sergei Tchernykh