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 SQ..." 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:
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?
Related ArticlesAre there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?