Converting MSAccess .MDB to SQL Server database

I need to switch from MS Access to SQL Server for my database. To setup a development environment I downloaded the free Microsoft SQL ServerExpress (February CTP version). I installed the required .NET Frameworkv2, and then SQLExpress. The install was done using all the defaults,and was done successfully. I also downloaded and installed the SQLExpress Manager Tool.
The SQL Server was installed on the same machine as my VS.NETdevelopment environment. The SQL Server process is now running, and Ican connect to the server using the SQL Express Manager Tool. Thisallows me to view and query the sample databases, but not much else.
To convert my Access .MDB database to SQL Server, I am trying to usethe MS Access Upsizing Wizard. The version of Access I am using isAccess 2002 on a Windows XP-Professional system. The problem is thatAccess cannot get a connection to the SQL Server. I tried using thedefault server name "(local)" and "Use Trusted Connection", but Ireceive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver[]Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver[]Shared Memory]SQL Server does not exist or access denied.
Curiously, I get this exact same error message even if the SQL serviceis stopped. So I'm pretty sure the problem is that it is not findingthe SQL server, and not a security issue.
In order to connect to the SQL server using the SQL Server ManagerTool, you have to provide the actual instance name for the server"COMPNAME\SQLExpress". So I tried using this server name in the AccessUpsizing Wizard, but this returns the same error message as aboveexcept the first SQL Server Error is 53. I also tried using a Login IDand password (using the Windows administrator ID and password, and alsothe "sa" ID and password) to no avail.
I am at wits end, and can't figure out why Access can't find the SQL Server. Any ideas would be appreciated.
Thanks.

I believe I found the answer to my problem through trial and error. Itappears that that Access wants to communicate with the SQL Server viaTCP/IP, however the default for SQL Server Express when installed onthe local machine is shared memory. If you enable shared memory in theSQL Computer Manager, it all seems to work. And it also appears thatthe server name must be specified as "COMPNAME\SQLExpress", not"(local)".|||

Thank you Paul.

After several hours of searching, your solution worked right out of the box.

You would only hope that some of the folks at MSDN would mention your solution on their websites.

Converting MS ACCESS db to SQLExpress

Is it possible to convert an ms access mdb file to an sqlexpress mdf file?
Any help appreciated,
Henk Feijt

You cannot convert MDB to MDF because MDF(microsoft data file) is only half of a SQL Server database because you also have the LDF(log data file). Create the database in Express and do a INSERT INTO or download the eval version of the full SQL Server 2005 and install the OLTP(online transaction processing) management studio which is a separate install under management tools and use integration services which is the new DTS to import the database from Access. BTW Access was not relational untill version 2000. Hope this helps.
http://www.microsoft.com/sql/downloads/trial-software.mspx|||Thanks for your info. But I find out that you can convert mdb to mdf. Access 2000 has an upsize wizard that convert the mdb to adf or mdf.
|||MDB to MDF
You will not belive that, but just trycopy(content of table from SHOW TABLE DATA)and paste(to prepared target table with same structure as the source)!
First you have to connect MDB ACCESS as datasource to the database explorer.
I Was spend almost two days on hard trying to conect and do SELECT INTO....
but it's so trivial to import data into the SQLExpress without sophisticated tools!