SSMA connect to SQL Server 2005

[Selinas Books@discussions.microsoft.com Stephen Selinas Books]

I have installed SQL Express 2005 and the services seem to be running.

I am trying to run SSMA to convert an Access 2003 to SQL. In the Migration
Wizard on the 'Connect to SQL Server 2005', it is asking for the Server Name.
What do I put in there?

I've tried just a full-stop, SQLEXPRESS & FINAWARE and it always comes up
with the same error: -

The server was not found or was not accessible. Verify that the instance
name is correct and that SQL server is configured to allow remote
connections. Named Pipes Provider error 40 could not open a connection to SQL
server.

I've gone into Surface manager and changed the settings to allow remote and
named pipes, closed and restarted the services.

This is my first experience of using SQL, so I'm struggling.

Regards
Steve

[Mike]

you should enter for the server: servername\instancename for example
localhost\sqlexpress
as well don't forget to enter an existing database name. if you habe not
created one yet, open the sql server management studio an create an empty
database, so you can migrate your access tables and data into the sql
database.

hope this helps

regards mike

[Stephen Selinas Books]

Thanks Mike. I didn't even have the management studio installed.

Created the dB, back to SSMA and it imported the Access dB although with a
few errors. I'm trying for the two-tiered approach, as I want to retain the
existing the Access Forms etc..

Not sure what I need to next though !

[Mary Chipman [MSFT]]

SQL Server doesn't work on the back end the same way Access/Jet does,
so you'll probably need to tweak your database schema unless your
database is extremely simple. SQL Express has its own Management
Studio, which you can download from
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en.
However, it may make more sense to purchase the Developer edition of
SQL Server, which has all of the features of the Enterprise edition
with licensing restrictions -- single user, can't use it in
production. For $50/US you get everything you need to work with the
database objects. Once your application is complete, you can then use
SQL Express to deploy it. For more information see
http://www.microsoft.com/sqlserver/2008/en/us/developer.aspx.

--Mary

[Armen Stein]

On Fri, 25 Sep 2009 07:43:01 -0700, Stephen Selinas Books
<StephenSe...@discussions.microsoft.com> wrote:

>Created the dB, back to SSMA and it imported the Access dB although with a
>few errors. I'm trying for the two-tiered approach, as I want to retain the
>existing the Access Forms etc..

Hi Stephen,

You can retain most of your Access application as it is, with some
changes for SQL Server compatibility and performance.

I've written a PowerPoint presentation on techniques for using Access
as a client-server front-end to SQL Server databases. It's called
"Best of Both Worlds" at www.JStreetTech.com/Downloads. It includes
some thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Philip Mark Hunt]

Dear Mike

I hope you pick this up a few months down the track from the original posts.
I chose to add my question here as I seem to be stuck in the same area as
Stephen was, but I don't actually have SQL Server 2005 native, so to say. My
'implementation' comes as part of Visual Web Developer 2008 Express Edition.

I have created the mdf file, using a function within VWD, as you remind in
this post. I cannot find a way past the Connect to SQL Server 2005 screen
though. I have tried localhost\sqlserver, to no avail and, as for what I
should put in the Server Port box, I don't have a clue.

I have created the mdf file in the App_data directory of my nacent ASP 3.5
website and that is where I put the SSMA project also, but I cannot work out
how to link the 2, so to say.

Bottom line of what I am really trying to do -

I have a small Access database, which I have invested some time in creating.
To be honest, though, I am merely at the point of having created quite a few
tables and defined the relationships between them, and added quite a lot of
records. I was simply trying to transfer my tables with their data to the
SQL Server 2005 database,and then just do away with my Access database. Am I
using the right tool? Am I missing something? Can I not do what I'm trying
to do when my SQL Server 2005 is part of VWD 2008 Express?

At the moment, this is all just happening on my home PC.

All help welcome, please.
--
Philip Hunt, Kwinana, Perth, Western Australia