Problems with data and database structure

• Relationships which will not upsize, ..cycles and multiple cascade paths..
  Certain relationships with cascade updates and deletes are not allowed in SQL Server, this article explains the origins of these problems.

• Understanding problems when converting time data to SQL Server
  This article discusses points to consider when converting applications that use time data to SQL server.

• Multi-part foreign keys, subtle differences between Access and SQL Server
  This article discusses a subtle difference between how Access and SQL Server deal with multi-part keys.

• Building applications with schemas and database roles (SQL Server 2005/2008)
  This article discusses how to take advantage of schemas to simplify your database design, and combine this with database roles to simplify security.

• Resolving issues when upsizing one-one relationships
  This article discusses potential problems arising from one-one relationships and provides examples of how to resolve these problems.

General issues in application development

• Linked tables and triggers which insert data into other tables
  This article looks at a rather subtle problem which can happen when you use linked table and have triggers on the table which perform inserts into other tables.

• Choosing between System and File DSN’s
  This articles discusses the differences between System and File DSN's, and explain choices of driver and security that need to be considered.

• Relinking tables and views between Access and SQL Server
  This article shows how to use a general purpose routine to switch the linked tables and views from one SQL Server database to another. An essential technique when switching between test and live systems.

• SQL Express backup & maintenance
  SQL Express does not have any provision for performing regular maintenance on a SQL Server database or backing up the database, this article explains how to get started creating your own routines to do this.

• Generating default values for fields in a form
  In Access applications you will be used to having the default values for fields displayed when a new record is entered, but when connected to SQL Server no defaults will be displayed, these are only assigned after saving the record. This article discusses systematic code to locate and display default values from the SQL Server table when the new record is first displayed to a user.

• Handling tables and views of temporary data
  SQL Server has feature which mean that generating temporary tables of data for individual users in a multi-user environment is much simpler than in Access.

Converting and translating SQL

• Solving complex query calculations using nested queries
  In Access queries, calculated fields can refer to other calculated fields. However, in SQL Server the calculation needs to be restated. This technique shows how to avaoid the problem using nested queries which allow a calculated field to refer to another calculated field by name without restating the calculation.

• Using a UNION in nested queries with SUM calculations
  UNION queries which elliminate duplicate values are of greate use, but when you use a nested query with an aggregate over the union this needs more detailed consideration.

• Converting queries to views, general points to consider
  This article discusses general aspects of converting Access queries to SQL Server views.

• Converting queries to views, handling IIF using CASE and COALESCE statements
  This article discusses the conversion of IIF statements using CASE and COALESCE statements.

• Converting SQL which refers to screen controls using a parameter table
  Access queries often contain self-prompting parameter boxes and references to screen controls, this article discusses how to make appropriate changes make these queries run when converted to SQL Server views.

• Hands-On SQL server - Translating SQL data between Microsoft® Access™ and SQL Server - Parts I and II
  Published in the December 2007/February 2008 in the Visual Studio Journal (www.vsj.co.uk), these two articles discusses in depth translating SQL. The first article includes a discussion of form and screen parameters, date calculations and arithmetic. The second article discusses Insert, Update and Delete, date comparisions, boolean comparisons, aggregates, domain functions and subqueries and summary queries.