Updated 4/24/2011 with SQL Server Migration Assistant (SSMA) for Access v4.2 migration log posts of 4/20/2011 and the following:
Download NwindAzure.mdb from my Windows Live Skydrive site to connect to a publicly accessible SQL Azure Northwind database running in Microsoft’s San Antonio, Texas data center from here.
Download Northwind.mdb for upsizing to SQL Server and SQL Azure from my Windows Live Skydrive instance from here.
The following Web-accessible articles address issues with and the history of topics covered in my Linking Access tables to on-premise SQL Server 2008 R2 Express or SQL Azure in the cloud Webcast for Que Publishing on 4/26/2011. The articles are listed in reverse chronological order and include an excerpt from their introduction or summary.
See Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011 of 3/30/2011 for more information on my two upcoming Microsoft Access 2010 Webcasts.
Output Logs for Successful Migration of Northwind.mdb to a SQL Server Northwind Database with SSMA for Access (to SQL Server or SQL Azure) blog post by Roger Jennings (4/20/1011):
Successfully migrating the tables of an Access 2003 Northwind.mdb sample database with the SQL Server Migration Assistant (SSMA) for Access v4.2 generates 325 log entries for 9 tables. You can download Northwind.mdb from my Windows Live Skydrive instance here.
A complete copy of the log file follows.
You also can download NwindAzure.mdb, which is a Access front-end for tables linked by a publically accessible Northwind SQL Azure database, here. Running NwindAzure.mdb will open a connection to the Northwind database running in Microsoft’s South Centeral US (San Antonio, TX) data center.
Output Logs for Canceled Migration of Northwind.accdb to a SQL Server Northwind Database with SSMA for Access blog post by Roger Jennings (4/20/2011):
Attempting to migrate the tables of an Access 2010 Northwind.accdb database created from the built-in Northwind template with the SQL Server Migration Assistant for Access v4.2 generates 670 log entries for 20 tables. The entries consist of 6 error, 245 warning, 233 information and 116 other messages.
Migration failed because of five instances of Access tables with unsupported Attachment field data types and one instance of an unsupported Lookup field data type. Many warnings are the result of the Access team’s inexplicable inclusion of spaces in field names.
A complete copy of the log file follows.
Access to SQL Server Migration: Access Solutions using SQL Server, Part 1 blog post by Mary Chipman (3/19/2011):
In Part 1 and Part 2 I’ll discuss your options for continuing to use Access as a front-end client once you have migrated the data to SQL Server. Some people come to SQL Server migration as Access developers who are new to SQL Server, whereas others may be IT professionals who know little about Access, so I will attempt to bridge any gaps with useful information for everyone. The SSMA Migration Assistant for Access (SSMA) makes it easy to link converted SQL Server tables and queries to the Access database you started from. For applications of any complexity, migrating the data is by far the easiest step. If your existing Access application suffered from intractable problems when the tables were in native Jet/ACE format, and you have done nothing to fix or at least understand those problems, then you are most likely going to have even bigger problems with SQL Server.
The Access client application has often been dismissed as a "toy" because of its ease of use, but when understood and used appropriately with SQL Server on the back end, those very features can help you quickly deliver cost-effective results with a low TCO. Access is not a suitable client for Web or n-tier applications that require a separate data access layer, although it can be used in conjunction with other clients, such as ASP.NET. There are always tradeoffs involved between ease of development, efficiency and security when using Access in a 2-tier, client-server scenario. You will need to determine which of the following techniques are best suited to your needs
Access to SQL Server Migration: Access Solutions using SQL Server, Part 2 blog post by Mary Chipman (3/19/2011):
In Part 2 I'll continue the discussion about how you can "tread lightly" on your SQL Server by making your Access front end an efficient and economical data consumer. I'll focus on techniques you can use to keep your application running efficiently, freeing up network and server resources in the process.
Upsize Microsoft Access Databases to SQL Azure with SSMA 4.2 blog post by Bill Ramos reposted to this blog (4/14/2011):
Bill Ramos explained Migrating Access Jet Databases to SQL Azure in a 4/14/2011 post:
In this blog, I’ll describe how to use SSMA for Access to convert your Jet database for your Microsoft Access solution to SQL Azure. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template. The blog also assumes that you have a SQL Azure account setup and that you have configured firewall access for your system as described in the blog post Migrating from MySQL to SQL Azure Using SSMA.
Access to SQL Server Migration: Understanding Data Type Conversions blog post by Mary Chipman (3/6/2011):
In this blog post I’ll discuss how you can use the SSMA Migration Assistant for Access (SSMA) to refine data type mappings between Access and SQL Server, but first a little history lesson. You can think of Access as two separate products rolled into one: the relational database engine, Jet, consisting of tables and queries, and Access application objects, consisting of forms, reports, macros and modules. Access 2007 introduced the Access Connectivity Engine (ACE), replacing Jet. It is fully backwards compatible with earlier versions of Jet, but incorporates new data types and functionality for integrating with SharePoint. Access 2010 completes the SharePoint "vision", making Access a first-class client application for SharePoint. What this means is that little work was done on Access-SQL Server integration, so much of the information available online for data type mappings between Access 2007 and SQL Server 2005 is still valid. Many of the new types introduced in SQL Server 2005 and 2008 are not supported in any version of Access, and the new SharePoint-compatible types introduced in Access 2007 and 2010 are not supported in SQL Server. Hopefully by the end of this article you’ll be able to find the information you need to manage data type conversions between your Access/Jet/ACE application and SQL Server.
Access to SQL Server Migration: Handling Schema Migration Issues blog post by Mary Chipman (3/6/2011):
In this blog post I’ll discuss some of the data migration issues you may encounter when migrating your Access schema to SQL Server. These consist of issues that you should address prior to migrating your data, and others are behavioral differences that you should be aware of. In my experience, it’s better to take a proactive approach and solve any known issues sooner rather than later. Fixes and workarounds after the fact are always more expensive and time-consuming than doing things the right way to begin with. The simple fact is, Access shields you from much of the complexity involved creating in a database application, and once you leave its protective umbrella for SQL Server, you face an infinitely more challenging database engine with a much higher learning curve. You can use the assessment reports in the SSMA Migration Assistant (SSMA) to identify potential issues before you migrate your data, which will not only smooth data migration, but also simplify application development later on.
Access to SQL Server Migration: How to Use SSMA blog post by Mary Chipman (1/28/2011):
In this blog post I’ll walk you through the process of migrating the Access 2007 Northwind database to SQL Server using the SQL Server Migration Assistant for Access (SSMA). You can use SSMA to do all of the heavy lifting, migrating your tables and queries to SQL Server 2008 or SQL Server Azure. SSMA can also link your Access solution to the new SQL Server tables after you have migrated your data.
Migration for Microsoft Access from the Microsoft SQL Server 2005 Web site (current):
Microsoft Access is a database intended for small projects with a few users. When a database grows large, and more users need to work with it, you are ready to move up to the more robust and secure database solution provided by Microsoft SQL Server. This page provides the tools and resources to help you simplify the process of migrating Access to SQL Server. For more information, read Microsoft Access or SQL Server 2005: What's Right in Your Organization? [Emphasis added.]
Remember that it’s the SQL Server team talking when you read the first sentence above.
Optimizing Microsoft Office Access Applications Linked to SQL Server by Andy Baron (11/2006):
Microsoft Office Access supports three primary options for connecting to data stored in Microsoft SQL Server databases:
- Use the Office Access database engine—originally called the Jet database engine—to communicate with SQL Server over ODBC connections.
- Create Office Access Project applications that use an OLE DB connection to communicate with SQL Server.
- Write Microsoft Visual Basic for Applications (VBA) code that uses DAO, ActiveX Data Objects (ADO), or middle-tier objects to connect and manipulate SQL Server data.
This paper focuses on the challenges encountered by Office Access developers who rely on the Office Access (Jet) database engine to connect to SQL Server over ODBC. The most common way this is done is by creating linked tables in Office Access that use the SQL Server ODBC driver to connect to tables in SQL Server databases.
The SQL Server Migration Assistant (SSMA) for Office Access enables you to convert an Office Access database to this type of application by moving your Office Access data to new SQL Server tables and linking to these tables. Any forms, reports, queries, or code that previously worked with the original Office Access tables are automatically connected to the new SQL Server tables.
In an application that uses linked SQL Server tables, two different database engines are at work: the Office Access/Jet database engine that runs on the Office Access client and the SQL Server database engine. The interaction of these two engines can sometimes yield results that are inferior to those obtained by using only the Jet database engine with native Office Access tables. This white paper discusses several of these issues and presents strategies for resolving them. Most of these issues relate to performance or updatability.
Microsoft Access or SQL Server 2005: What's Right in Your Organization? by Luke Chung, FMS, Inc. for Microsoft Corp. (12/2004 revised 7/2006):
Organizations have a wide range of database needs and resources. From simple, short-term needs to long-term mission critical requirements, organizations create and support a variety of databases. Choosing the right technology is an ongoing challenge. Many organizations struggle with deciding whether or when they should be using Microsoft® Access and Microsoft SQL Server™. Both are powerful and established database alternatives with their unique strengths and weaknesses.
Microsoft Access is composed of two parts: the application layer and the data layer (Jet database engine). SQL Server only provides the data layer. The Access application layer can interact with SQL Server, either through linked tables in an .mdb database or directly through an Access Database Project (ADP). Other products like Visual Studio® .NET, Visual Basic®, and C++ can also interact with SQL Server.
Both databases have an important role in most organizations, because each is optimized for different segments of database solutions. The strength of Access is its ease of use, rapid application development environment, and simplistic distribution (assuming the recipient also has the correct version of Access installed). It can even support multiple user shared deployments. The strength of SQL Server is its more robust data integrity, scalability, security, and manageability.
Depending on the situation, the strength of Access may outweigh its deficiencies. Overall, if the database problem is targeted for Microsoft Windows®-based operating systems only and can be solved with Access, the need for SQL Server is minimized. This is particularly true if information workers who are not programmers can use Access to solve their own problems. Not only are immediate costs lower, Access users may create a solution that would be difficult for someone else to understand and create for them as quickly.
For more challenging situations, an information technology (IT) professional creating a SQL Server solution is more appropriate. Whether the front end is in Access or not, a SQL Server database offers many features that a file server Access database cannot. Whether you need triggers, stored procedures, transaction logs, Web application support, or security, SQL Server is a solid choice for critical needs that Access (using the Jet database engine) cannot match.
In most cases, it's easy to determine whether a new application should be using Access (with the Jet database engine) or SQL Server. The challenge for most organizations is how to anticipate and manage the small fraction of Access applications that need to migrate to SQL Server each year.
Few (we estimate less than 2 percent) Access databases need to migrate every year, yet some organizations want to ban Access completely. This is often because IT professionals are included when an Access application is breaking down. They fail to recognize the ability of Access to solve 98 percent of database situations that never require their involvement. When an Access application is created initially, the features needed in the future cannot be anticipated, nor can the budget be justified. It's a case of database evolution, and now it's time to evolve from Access and the Jet database engine. You have many ways to migrate an Access application without losing the existing investment, and an organization that manages this well attains a significant competitive advantage.
Client-Server Application Development Using Microsoft Access 2.0 blog post by Roger Jennings (4/25/2006) describes what I believe was the first Access application linked to a mainframe database (IBM DB2):
The oldest article that I've been able to find that's related to my early Access client/server development projects is the "Client-Server Application Development Using Microsoft Access 2.0" presentation from Tech•Ed 95 by Microsoft's Christopher Payne. (Chris Payne left Microsoft in March 2007.)
The article—last modified on February 26, 1995—had been available as a Web page from http://www.microsoft.com/Accessdev/ARTICLES/AC302.HTM. Details are preserved here because the Web version is no longer available from the Microsoft site, and it appears to have disappeared from the Google cache. Figures were missing from the cached source copy. Microsoft Knowledge Base article Q129613 has a link to a Te9514.exe self-extracting ZIP file that contains a downloadable Microsoft Word version of the article.
My Access 2.0 applications that inspired this article were NSC_ASK.mdb and MSA_OLTP.mdb. NSC_ASK.mdb was a client/server decision-support application for National Semiconductor Corp., which is described in my "An (Almost) Lifetime of BASIC" archive post. NSC_ASK.mdb began as an Access 1.1 front-end for a Hitachi mainframe running an IBM DB2 database. Microsoft didn’t offer an Open Database Connectivity (ODBC) data source for DB2 when I wrote NSC_ASK, so I used an early beta version of Information Builders’ EDA ODBC middleware to link Access front ends on salespersons’ laptops to the data source back end via TCP/IP and a phone modem. I wrote MSA_OLTP.mdb as a contract project for Greg Nelson, an early Access marketer.
National Semiconductor's Margaret Lam and Greg Nelson demonstrated the NSC_ASK and MSA_OLTP applications at Tech•Ed 1995's AC302 session—about a year and a half after Microsoft released Access 1.0 in November 1993. Special Edition Using Access 95 and subsequent editions have used MSA_OLTP.mdb to test backward compatibility and compare client/server data retrieval and update performance with unbound controls.
Remarkably, much of the content of this article still applies to client/server projects with current Access versions. MSA_OLTP.mdb was designed for heads-down telephone order entry by customer service representatives. All order lookup, new order entry, and new customer addition operations use keyboard shortcuts; a mouse isn't required.
My blog post continues with screen shots of the MSA_OLTP.mdb project and an excerpt from Chris Payne’s white paper.
Note that Visual Basic will celebrate it’s 25th birthday on May 20, 2011.
Evolving Microsoft Access Applications to SQL Server by Dan Haught, FMS, Inc. (undated, about 2000):
Learn When and How to Upsize to SQL Server
This whitepaper explores the issues related to upsizing Microsoft Access
applications to take advantage of the performance, security, and reliability of Microsoft SQL Server. Topics discussed include:
- The Value of Access in Your Organization – a brief discussion of how
Access provides power and agility to an organization’s users - Making the Decision: When to Upsize – an evaluation of the criteria to
decide if an application has outgrown the capabilities of access - Microsoft Access Data Architectures – a discussion of the type of data
architectures that Access supports - Types of Upsizing Projects – There are several approaches to upsizing.
This section shows how to determine which one is best for you. - Planning an Upsizing Project – careful planning results in a successful
project. This section outlines what to plan for.
FMS Expertise – FMS is a world-leader in both Access and SQL Server.
We can deliver your upsizing project on time and on budget.
The oldest article that I've been able to find that's related to my early Access client/server development projects is the "Client-Server Application Development Using Microsoft Access 2.0" presentation from Tech•Ed 95 by Microsoft's Christopher Payne. (