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.

• Using New SQL Server 2008 Data Types
  This article discusses the advantages and limitations of choosing to map Access data structures to use new SQL Server 2008 data types such as DATE, and provides instructions on the changes you need to make in MUST to perform a mapping to a new data type.

• Upsizing and SQL Server Collation Codes
  Access applications are not case-sensitive. However, if you upsize to a SQL Server which has been configured as case sensitive you may need to take a deeper look at this issue, this article discusses when this can occur and some strategies for dealing with the problem.

• SQL Azure and Microsoft Access
  Please note the date on this article, as features change in Azure this article will be updated. This article looks at how you can link Access Databases on the desktop into SQL Azure.

General issues in application development

• View updateability affects with multiple base tables
  Sometimes you can create a query in Access, but when you convert it to to a view in SQL Server you get errors telling you that the view can not be updated because it affects multiple base tables, in this article we explain this problem and show you a work-around.

• Triggers and Denormalized Tables of Data
  This articles shows how to write triggers to keep denormalised summary or reference tables synchronised in an application.

• Access and SQL Server Views with Boolean Values; when is True -1 or 1
  This articles looks into why adding explicit data typing is important when returning a calculated boolean field from a view, it also illustrates minor inconsistencies in Access when displaying a boolean which does not have a default value.

• Boolean Default Values, when linking Access to SQL Server
  This articles looks into potential problems when working with boolean fields when connecting Access to SQL Server and the use of default values.

• ODBC Drivers and OLEDB Providers for connections from Access to SQL Server
  This articles shows the different options for ODBC Drivers and OLEDB Providers that can be used when connecting Access to SQL Server.

• Access application changes after upsizing to SQL Server: Part I code changes
  This article looks at basic code changes that often have to be applied to an Access application once the data has been upsized to SQL Server, code examples of what to do about dbSeeChanges, and the impact on code of having autonumbers and defaults only available after a record has been saved are discussed.

• Access application changes after upsizing to SQL Server: Part II design changes
  This article looks at how you can boost the performance of your new Access application linked to SQL Server. We look at how design decisions can impact the performance of queries, forms and importing data, and provide a number of design tips.

• 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

• Converting Access Append Queries and IDENTITY_INSERT Problems
  This article describes a problem that will occur when converting an append query to a SQL Server stored procedure, where the query appends directly into a column that has an identity property (autonumber).

• 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.