IBM Computer, Laptops and Servers

How do I upsize from Access to SQL Server?

Access is not truly meant for concurrent use. Microsoft made it easy to use Access over the web so that small companies who couldn't afford (or were intimidated by) SQL Server would have some database platform to use for their LANs (great way to sell more copies of the higher-end Office package, as well). Unfortunately for Internet developers, Access does not handle more than a handful of simultaneous users very well at all. So if you've got a family site that only your friends and such are looking at, Access is probably fine. For anything more than that, you'll probably want to use SQL Server.

Here are two KB articles and a white paper that will help you upsize your Access database to SQL Server (6.5 or greater):

KB #237980 How to Convert an Access Database to SQL Server

KB #325017 HOW TO: Use the Microsoft Access Upsizing Wizard

KB #330468 HOW TO: Use the Microsoft Access 2002 Upsizing Wizard

KB #294407 "Access 2002 Upsizing Tools" White Paper

Also see this page:

SQL Server and Microsoft Access

If you know you are converting from Access 2000 to SQL Server 2000, you may want to pick up the SQL Server 2000 Resource Kit. It has an updated version of the upsizing wizard that fixes some problems, and makes Access 2000 projects (.adp) much more fluent in the changes to SQL Server 2000.

Also, the book "Microsoft Access Developer's Guide to SQL Server" by Andy Baron and Mary Chipman covers the upsizing process fairly well.

Russell Sinclair's "From Access to SQL Server" will certainly help with the migration process, and understanding the differences between the two platforms. For a brief summary of the differences between Access and SQL Server, see Article #2214.

There are also some 3rd party products out there that may help in your migration:

Access-to-MSSQL

Converter Technology

DataJunction djCosmos

Database Converter

dbPal

SSW Upsizing Pro

Finally, here are some Knowledge Base articles you may want to glance over before proceeding:

KB #328319 Issues with the Access 2002 Upsizing Wizard

KB #325019 Issues with the Access 2000 Upsizing Wizard

KB #288300 "Microsoft Access Can't Find the Wizard..." Error [...]

KB #282380 Bad File Name or Number [...]

KB #281950 Database Unexpectedly Upsized [...]

KB #272384 "Overflow" Error Message When You Try [...]

KB #269824 Incompatibility Issues Between Access 2000 Projects [...]

KB #165827 "Overflow" or "Division by Zero" Error Upsizing Table

KB #153034 Table Is Not Exported Using the Upsizing Tools

Now that you see how much work is involved, and that there are plenty of potential issues, wouldn't it make sense to just prototype your application using SQL Server in the first place?

[ Comment, Edit or Article Submission ]