Access 2007: Microsoft Advisory Services Engagement Scenario - Upsize Access Tables to SQL Server

SUPPORT SOLUTION:
This Advisory Services scenario is designed to assist customers with upsizing their Access database tables from an .accdb or .mdb database file to SQL Server and then assist them with linking their frontend Access database file to these backend SQL tables via an ODBC connection. This will allow customers to continue to use their Access database applications while also allowing them to host their data on a SQL Server to optimize database and application performance, scalability, availability, security, reliability, and recoverability..Key deliverables

Install SQL Server Express if it is applicable.
Database will be reviewed for known upsizing compatibility issues.
Access tables will be upsized to SQL Server tables.
Access database will be updated to contain linked tables to the SQL Server database.
Will ensure that upsized tables are assessable and updatable from the Access client.
.Scenarios

Collapse this imageExpand this image.Statement of Work for Upsizing Access Data to SQL Server

Scoping questions
3,
Do you have Microsoft SQL Server available to use with your Access database?
If the answer is “yes,” is it already installed? Do you have someone in your organization that is familiar with SQL Server Management Studio to maintain the tables when they are upsized? Is there someone in your organization that can administer the tables when they are upsized to SQL? How large is your Access database – what size is the .mdb file?
How many tables exist within your Access database?
How many reports exist in your database?
How many forms exist in your database?
How many queries exist in your database?
How many macros exist in your database?
How many lines of code exist in your Visual Basic project file?Included with scope
3,
Database will be reviewed for known upsizing compatibility issues.
Install SQL Server Express if it is needed.Access tables will be upsized to SQL Server tables.
Access database will be updated to contain linked tables to the SQL Server database.
Will ensure that upsized tables will be assessable and updatable from the Access client.
Basic functionality testing is performed to ensure basic connectivity and operation against the SQL Server.
Out of scope
3,
In depth data analysis is not included.
Only very basic functionality testing is performed to ensure basic connectivity and operation against the SQL Server. Further rigorous testing should be completed by the developer before putting the solution into production.
SQL Server Training will not be provided. The developer will need to have or obtain basic SQL Server knowledge on their own.
SQL Server Security will not be provided. We will create a basic domain admin account on the SQL Server to use, if there are no other accounts already setup to use. We will not be creating multiple SQL accounts and going over SQL Server security.
We will not be providing ongoing application support. This will need to be done via the developer, a consultant, or by opening break / fix incidents.Below is a list of self-help resources or this scenario. These resources may also be used by Microsoft Support Engineers during an Advisory Services engagement..Referenced articles

When to upsize a Microsoft Access database to Microsoft SQL Server
(http://msdn2.microsoft.com/en-us/library/aa199588.aspx)
Before you upsize a Microsoft Access database
(http://msdn2.microsoft.com/en-us/library/aa156266.aspx)
Use the Upsizing Wizard
(http://office.microsoft.com/en-us/access/HP052730091033.aspx)
294407 

(http://support.microsoft.com/kb/294407/
)
“Access 2002 Upsizing Tools” white paper is available in Download Center Access migration using SSMA (Whitepaper)

(http://download.microsoft.com/download/e/c/8/ec8d5025-7ef7-4dcc-a9f3-9c297cf5350e/SSMAAccess.docx)
.Other general articles

When to Migrate from Microsoft Access to Microsoft SQL Server

(http://download.microsoft.com/download/5/d/0/5d026b60-e4be-42fc-a250-2d75c49172bc/when_to_migrate_from_access.doc)
When to upsize a Microsoft Access database to Microsoft SQL Server
(http://msdn2.microsoft.com/en-us/library/aa199588.aspx)
Use Access with a database server
(http://office.microsoft.com/en-us/access/HA102791591033.aspx?pid=CH100948231033#5)
Before you upsize a Microsoft Access database
(
http://msdn2.microsoft.com/en-us/library/aa156266.aspx)
Choose how to upsize your application
(
http://msdn2.microsoft.com/en-us/library/aa190349.aspx)
Building a SQL Server System Architecture to Support Multiple Migrated Microsoft Access Databases
(http://technet.microsoft.com/en-us/library/cc917612.aspx)
How to Migrate from Access to SQL Server 2000
(http://technet.microsoft.com/en-us/library/cc917602.aspx)
237980 

(http://support.microsoft.com/kb/237980/
)
INF: How to Convert an Access Database to SQL Server

SQL Server Migration Assistant

SQL Server Migration Assistant for Access (SSMA Access)

(http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx)
Access migration using SSMA (Whitepaper)
(http://download.microsoft.com/download/e/c/8/ec8d5025-7ef7-4dcc-a9f3-9c297cf5350e/SSMAAccess.docx)
Learn About SSMA for Access
(http://www.microsoft.com/sqlserver/2005/en/us/migration-access-learning.aspx)
SQL Server Migration Assistant (SSMA) for Microsoft Access (Download)
(http://www.microsoft.com/downloads/details.aspx?familyid=4747730C-6E3D-449E-8F12-BB19328E7128)
294407 

(http://support.microsoft.com/kb/294407/
)
Upsizing Wizard

“Access 2002 Upsizing Tools” white paper is available in Download Center 330468 

(http://support.microsoft.com/kb/330468/
)
HOW TO: Use the Microsoft Access Upsizing Wizard

Converting an Access Database with the Upsizing Wizard
(http://msdn2.microsoft.com/en-us/library/aa164896.aspx)
Use the Upsizing Wizard
(http://office.microsoft.com/en-us/access/HP052730091033.aspx)
Move Access data to a SQL Server database by using the Upsizing Wizard
(http://office.microsoft.com/en-us/access/HA102755371033.aspx)
130166 

(http://support.microsoft.com/kb/130166/
)
Data Type Mapping in the Upsizing Wizard

325017 

(http://support.microsoft.com/kb/325017/
)
HOW TO: Use the Microsoft Access Upsizing Wizard
330468 

(http://support.microsoft.com/kb/330468/
)
HOW TO: Use the Microsoft Access 2002 Upsizing Wizard
328319 

(http://support.microsoft.com/kb/328319/
)
Issues with the Access 2002 Upsizing Wizard
.Optimization

Optimizing Microsoft Office Access Applications Linked to SQL Server

(http://msdn.microsoft.com/en-us/library/bb188204.aspx)
892490 

(http://support.microsoft.com/kb/892490/
)
How to create a DSN-less connection to SQL Server for linked tables in Access.Troubleshooting

Troubleshooting Performance Problems in SQL Server 2008
(http://msdn.microsoft.com/en-us/library/dd672789.aspx)
294398 

(http://support.microsoft.com/kb/294398/
)
Tables Not Upsized When Query or Table Name Contains Apostrophe

295231 

(http://support.microsoft.com/kb/295231/
)
Upsizing Wizard fails to upsize data in large tables

838594 

(http://support.microsoft.com/kb/838594/
)
Error message when you try to upsize your Access database by using the Upsizing Wizard: “The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source.”

288300 

(http://support.microsoft.com/kb/288300/
)
“Microsoft Access Can’t Find the Wizard…” Error [...]
282380 

(http://support.microsoft.com/kb/282380/
)
Bad File Name or Number [...]
281950 

(http://support.microsoft.com/kb/281950/
)
Database Unexpectedly Upsized [...]
272384 

(http://support.microsoft.com/kb/272384/
)
“Overflow” Error Message When You Try [...]
269824 

(http://support.microsoft.com/kb/269824/
)
Incompatibility Issues Between Access 2000 Projects [...]
165827 

(http://support.microsoft.com/kb/165827/
)
“Overflow” or “Division by Zero” Error Upsizing Table
153034 

(http://support.microsoft.com/kb/153034/
)
Table Is Not Exported Using the Upsizing Tools

244309 

(http://support.microsoft.com/kb/244309/
)
MOD2000: Permissions Required to Upsize a Microsoft Access Database to SQL Server

Upsizing Design Issues
(http://msdn.microsoft.com/en-us/library/ms952656.aspx)
.
Additional upsizing resources

http://office.microsoft.com/en-us/access/results.aspx?qu=upsize&sc=9&av=ZAC110
(http://office.microsoft.com/en-us/access/results.aspx?qu=upsize&sc=9&av=ZAC110)
http://office.microsoft.com/en-us/access/results.aspx?qu=upsize&sc=9&av=ZAC120
(http://office.microsoft.com/en-us/access/results.aspx?qu=upsize&sc=9&av=ZAC120)
http://msdn.microsoft.com/en-us/library/bb545450(v=MSDN.10).aspx
(http://msdn.microsoft.com/en-us/library/bb545450(v=MSDN.10).aspx)
http://technet.microsoft.com/en-us/library/bb418440(SQL.10).aspx
(http://technet.microsoft.com/en-us/library/bb418440(SQL.10).aspx)
http://blogs.msdn.com/sqlexpress/articles/616581.aspx
(http://blogs.msdn.com/sqlexpress/articles/616581.aspx)
http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx
(http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx)
http://msdn.microsoft.com/en-us/sqlserver/default.aspx
(http://msdn.microsoft.com/en-us/sqlserver/default.aspx)
http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp
(http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp)
.Note This is a “FAST PUBLISH” article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use
(http://go.microsoft.com/fwlink/?LinkId=151500)
for other considerations..

For recovery of Microsoft Access 2007 databases, visit Microsoft Access File Repair