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

• Searching and Displaying Data with multiple subforms, Bound/Unbound Form
  This article shows how to both use BuildCriteria to simplify forming SQL for filters and where clauses. We also build upon an earlier article to show how to allow users to enter search criteria in both a main form and related subforms.

• Using UNPIVOT to normalize mapped data sets in SQL Server
  This article provides an example of using UNPIVOT to normalise data.

• SQL Server Database Auditing
  This article provides samples of how to construct both row level and field level auditing in SQL Server.

• Searching and Displaying Data using a single Bound/Unbound Form
  This technique shows how to dynamically unbind single and continuous forms to allow users to enter search criteria in the controls, and then re-bind the form to display the results, avoiding the need to create separate search forms.

• Getting started with SQL Server Security
  This is an introduction to SQL Server security, providing an overview of windows authentication, SQL Server logins, database roles and schemas. All an Access Developer needs to get started.

• View updateability and Timestamp columns
  This article discusses how to use TIMESTAMP columns in updateable Views, and explains how this works when a view has multiple base tables.

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

• Packing up Text Fields using a Common Table Expression
  This technique shows how to use a CTE expression in SQL Server to pack up the contents of multiple rows of text into a single field.

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

• Referential Integrity, Multi-Tenanted Solutions, Access and SQL Server
  This article looks at basic architectural issues in constructing Multi-Tenanted designs in both Access and SQL Server.