Microsoft Knowledge Base Email Alertz
All KB/RSS Links  |  Hot Kb!  |  New Kb!  |  Scott Cate  |  FAQ / KB  |  Web Hosting  |  Unsubscribe  

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

Search KbAlertz

Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.
Click here to create a
FREE account
Already have an account?
[Click here to Login]











Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks
Article ID: 2279654 - Last Review: February 21, 2011 - Revision: 4.1

Upsize Access Tables to SQL Server

View products that this article applies to.

On This Page

Microsoft Professional Advisory Services is a support option that provides short-term, proactive, consultative support beyond break-fix product maintenance needs. This includes working with the same technician for assistance with issues like product migration, code review, or new program development and is a remote, phone-based support option. This service is typically used for shorter engagements, and is designed for developers and IT professionals who do not require the traditional onsite consulting or sustained account management services that are available from other Microsoft support options.

For additional information on Microsoft Advisory Services, including on how to engage, refer to this Microsoft web page:

http://support.microsoft.com/gp/AdvisoryService (http://support.microsoft.com/gp/AdvisoryService)
Back to the top

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

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.

Back to the top

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.
Back to the top

Scenarios

Collapse this imageExpand this image
SCENARIOS
Back to the top

Microsoft Advisory Services Engagement

Statement of Work for Upsizing Access Data to SQL Server

Scoping questions

  • 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

  • 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

  • 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
Back to the top

Self-Help resources for this scenario

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.
Back to the top

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://kbalertz.com/Feedback.aspx?kbNumber=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)
Back to the top

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://kbalertz.com/Feedback.aspx?kbNumber=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://kbalertz.com/Feedback.aspx?kbNumber=294407/ ) Upsizing Wizard "Access 2002 Upsizing Tools" white paper is available in Download Center

330468  (http://kbalertz.com/Feedback.aspx?kbNumber=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://kbalertz.com/Feedback.aspx?kbNumber=130166/ ) Data Type Mapping in the Upsizing Wizard

325017  (http://kbalertz.com/Feedback.aspx?kbNumber=325017/ ) HOW TO: Use the Microsoft Access Upsizing Wizard

330468  (http://kbalertz.com/Feedback.aspx?kbNumber=330468/ ) HOW TO: Use the Microsoft Access 2002 Upsizing Wizard

328319  (http://kbalertz.com/Feedback.aspx?kbNumber=328319/ ) Issues with the Access 2002 Upsizing Wizard
Back to the top

Optimization

Optimizing Microsoft Office Access Applications Linked to SQL Server (http://msdn.microsoft.com/en-us/library/bb188204.aspx)

892490  (http://kbalertz.com/Feedback.aspx?kbNumber=892490/ ) How to create a DSN-less connection to SQL Server for linked tables in Access
Back to the top

Troubleshooting

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

294398  (http://kbalertz.com/Feedback.aspx?kbNumber=294398/ ) Tables Not Upsized When Query or Table Name Contains Apostrophe

295231  (http://kbalertz.com/Feedback.aspx?kbNumber=295231/ ) Upsizing Wizard fails to upsize data in large tables

838594  (http://kbalertz.com/Feedback.aspx?kbNumber=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://kbalertz.com/Feedback.aspx?kbNumber=288300/ ) "Microsoft Access Can't Find the Wizard..." Error [...]

282380  (http://kbalertz.com/Feedback.aspx?kbNumber=282380/ ) Bad File Name or Number [...]

281950  (http://kbalertz.com/Feedback.aspx?kbNumber=281950/ ) Database Unexpectedly Upsized [...]

272384  (http://kbalertz.com/Feedback.aspx?kbNumber=272384/ ) "Overflow" Error Message When You Try [...]

269824  (http://kbalertz.com/Feedback.aspx?kbNumber=269824/ ) Incompatibility Issues Between Access 2000 Projects [...]

165827  (http://kbalertz.com/Feedback.aspx?kbNumber=165827/ ) "Overflow" or "Division by Zero" Error Upsizing Table

153034  (http://kbalertz.com/Feedback.aspx?kbNumber=153034/ ) Table Is Not Exported Using the Upsizing Tools

244309  (http://kbalertz.com/Feedback.aspx?kbNumber=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)
Back to the top

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)

Back to the top
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.
Back to the top
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Access 2010
  • Microsoft Office Professional 2007
  • Microsoft Office Enterprise 2007
  • Microsoft Office Ultimate 2007
  • Microsoft Office Professional 2010
  • Microsoft Office Professional Plus 2010
Back to the top
Keywords: 
kbproadvisory kbinfo kbhowto kbsurveynew kbexpertiseadvanced kbgraphxlink kbgraphxlinkcritical kbprooffice KB2279654
Back to the top
        

Community Feedback System

Very often, it takes hours to solve a problem. Very often, you've looked high and low, and have tried a lot of solutions. When you finally found it, chances are, it was because someone else helped you. Here's your chance to give back. Use our community feedback tool to let others know what worked for you and what didn't.

Please also understand that the community feedback system is not warranted to be correct, it's simply a system that we've built to let people try and help each other. If something in a feedback response doesn't make sense to you, or you're not comfortable making changes that the feedback talks about (like registry edits), please consult a professional.

Thank you for using kbAlertz.com Feedback System.

-- Scott Cate

Copyright © 2002-2007 KBALERTZ.COM.  All Rights Reserved. Terms / Privacy. Discount ASP.NET Hosting