📑 Table of Contents▼

Upsizing is the term coined by Microsoft to describe the process of upgrading Microsoft Access Database to a Microsoft SQL Server. This allows to continue using Microsoft Access as a database front-end whereas the actual back-end is served by a separate local or remote SQL Server allowing much higher productivity and data volumes. Microsoft Access from the version 2000 on has a special Upsizing Wizard which facilitates the data migration to the proprietary Microsoft SQL Server. No other RDBMS are currently supported for upsizing.

Upsizing strategies

edit

There are two strategies how database can be migrated from Access to a SQL Server.

  1. Using ODBC from Microsoft to get access to a remote database via ADO.
  2. Using Access Data Projects (available in Microsoft Access 2000 and higher) which allow more "native" integration with Microsoft SQL Server.

The first strategy is often seen as the first step towards complete migration on a SQL server on the stage 2,[1] and can be seen as a part of the strategy 2. For peculiarities of every strategy see the table.

Parameter Strategy 1 Strategy 2
Database access Via ODBC using ADO, somewhat slower than strategy 2. Native integration with Microsoft SQL Server using Access Data Project (.adp), data access using ADO
Integration Can be integrated with (almost) any RDBMS Native support only for Microsoft SQL Server
The role of Access Serves as database front-end with full features of Microsoft Access Jet Engine and VBA. (compare to thick client) Serves as database front-end with limited functionality. Local tables are not allowed.[2] Queries are generally transformed into database views, VBA code manipulating the data is transformed into procedures stored on server. (compare to thin client).

Performing the upsizing

edit

As any data migration procedure Microsoft Access database upsizing requires fundamental refactoring of the database structure and source code. Even though some aspects of this procedure seem to be automatized by the Upsizing Wizard, there are still many points requiring human intervention. Following changes have to be done on upsizing

  1. Complete change of data access interface from DAO to ADO.
  2. Substantial change of Microsoft Jet SQL to Transact-SQL.
  3. Substantial adaptation of object (e.g. tables, columns etc.) names.
  4. Reconception of the source code to optimally employ the new functionality of ADO and Microsoft SQL Server of the "upsized" solution.

Notes and references

edit
  1. ^ Thomas Groß, Claudia Clemens "Upsizing von Access zu SQL Server", dotnetpro 6/2004
  2. ^ There are though the ways to store some data locally using the save methods of recordset object

📚 Artikel Terkait di Wikipedia

Access Database Engine

2000 to Access 2010 included an "Upsizing Wizard" which could "upsize" (upgrade) a Jet database to "an equivalent database on SQL Server with the same table

Microsoft Access

feature called the Upsizing Wizard that allowed users to upgrade their databases to Microsoft SQL Server, an ODBC client–server database. This feature was

Pipe bursting

with larger ones, or "upsizing." Extensive proving work by the gas and water industries has demonstrated the feasibility of upsizing gas mains, water mains

Ratchet & Clank: Size Matters

18, 2021. McGarvey, Sterling (January 25, 2008). "High Impact Games on Upsizing Size Matters". GameSpy. p. 2. Archived from the original on August 29,

Suzuki Vitara

Vitara received a facelift. Along with the cosmetic changes, the V6 was upsized to 2.5 litres while a 2.0-litre four-cylinder was slotted into the range

Foresite Capital

Retrieved 20 May 2017. Ben Fidler (6 November 2013). "Karyopharm Prices Upsized IPO at $16 Per Share, Begins Trading Today". Xconomy. Retrieved 20 May

Buick LeSabre

LeSabre sedans and coupes by a smaller 300-cubic-inch V8 designed for the upsized Special/Skylark intermediates that replaced the aluminum V8 in those vehicles

Porsche 911 (992)

engineered 3.6 L (3,591 cc) turbocharged flat-six engine, which has been upsized in comparison to the last model's 3.0 L six-cylinder, makes 541 PS (534 hp;