Change an Access Database to SQL Server

I designed a database that works well in Access and I want to get it to run in SQL Server. How do I learn how and what to do to change over to SQL Server?

Run the Upsizing Wizard by opening the database to be upsized in Access. Click Tools, click Database Utilities, and then click Upsizing Wizard.
There's a whitepaper which describes this process in detail. Have a look here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

This is for SQL 2000, but it should be similar for SQL 2005.|||

Hi Chris

Thanks for your help.

I tried this but got errors. I am running SQLServer on the same computer as Access 2003 with the database. The computer is running WindowsXP Pro operating system. Will this work or do I need Windows 2000 server or Windows NT?

Thanks again,

Mark

|||I also use SQL Server 2005 on Windows XP pro, and haven't experienced any limitations (when compared to using Windows 2003). What errors are you getting? Also have a look at the event log, and ensure you resolve any permissions issues first.|||

Here is the error message I got:

Connection failed:

SQLState: '01000'

SQL Server Error: 2

[Microsoft][ODBC SQL Server][Shared Memory]ConnectionOpen (Connect()).

Connection failed:

SQLState: '08001'

SQLServer Error: 17

[Microsoft][ODBC SQL Server][Shared Memory]SQL Server does not exist or access denied.

|||This is most likely to be either a security issue (invalid security settings used when trying to connect to SQL Server), or a client/server connectivity issue (related to network connectivity, but probaby not in your case, as you are using Shared Memory - ie. both server/client are on the same box, or a client/server protocol mismatch/misconfiguration).

There are detailed step-by-step instructions to follow on the following post for dealing with SQL Server connectivity/connection issues. Have a look at the following thread for details (please read through all details in the thread first before re-posting):
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=302440&SiteID=1|||For some reason, nothing was posted - try again.|||

Chris

I have been writing database application in MS Access for about 10 years and thought I was pretty good. So I figured it was time to try to move up to SQLServer. Boy do I feel stupid!

I tried to read through the thread that you referred me to and I could not make heads or tails out of it. I can't even figure out which program to open (Configuration tools, SQL Server Management Studio Express CTP, etc.) Once I open one of these programs, I can't figure out where to begin. Where is a client? Where is the server?

Access was just - start the program, make some tables, make some queries if needed, make some forms or reports, and your done -EASY.

Do you have any suggestions on where I can go to get basic information on how to get started with SQLServer. The basic information on the Microsoft web site is even too advanced for someone who can't figure out the basics of getting started (step by step - click on this first to do __, click on this next to do the next thing which is __, etc.).

Any suggestions would be greatly appreciated.

Thanks

|||There's no reason to feel stupid. Anything looks super complicated when there are heaps of technical product/domain-specific keywords/jargon, and you've never seen it before. To me, the fact that depsite working on one thing for a long time, the fact that you show the willingness to upgrade/broaden your skills shows that you are willing to learn, and that's commendable.

After a quick scout around, I found the following link, which guides you through, step-by-step, the process of setting up either SQL Server 2005 Pro or the Express edition:
http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx

It's got instructions about how to access different programs (that come with SQL 2005), and by following the instructions step by step, you'll become familiar with which tools are used for which purpose.

Most of what I've learned, I've learned through tutorials, and I find them a great place to start. Once you've got the basics, you can search for specifics in the area you're interested in, or need to work with (ie. once you've got the fundamentals of how to do basic DB management using the tools, then you can concentrate on your DB upgrade).

In most cases, all you need to know will be on msdn, so when you do a google search, if you find you're not getting what you want, then use the site:msdn.microsoft.com keyword to restrict your search to the MSDN site.|||Note also the link to further SQL Server tutorials included on the linked page under the Concepts section, for further tutorials:
SQL Server Tools Tutorials|||

Thanks for your help.

I'll give that a try.